Undocumented feature: Server-Side Prepared Statements

[exalate-issue-sync[bot]]

Jesse Seldess (jseldess) commented:

We need to add a page on "Prepared Statements", covering both how these work in application code and how they can work for scripts/testing.

The MySQL docs are a good reference: https://dev.mysql.com/doc/refman/8.0/en/sql-syntax-prepared-statements.html

Jira Issue: DOC-2091

[exalate-issue-sync[bot]]

Jesse Seldess (jseldess) commented: Notes from a chat:

Jesse:

Prepared statements let you store a query server-side to be executed later (as many times as you like).

@benesch:

"prepared statements let you store a query server-side to be executed later (as many times as you like)" is spot on. In CRDB it pretty much only saves you SQL parsing time (small), but will eventually save you query optimization time once we optimize queries more aggressively

@benesch (paraphrase):

Difference between views and prepared statements: The view lasts forever whereas the prepared statement disappears at the close of the session. So typically you use views when a query is important enough that it deserves its own global name. But the prepared statement is typically used just to optimize performance.

@knz:

in a twist to that story, this performance story is only true for other SQL databases. In CockroachDB it is just false. Prepared statements are simply slower in CockroachDB, because we end up parsing and preparing the logical plan two times. (this is no longer true)

however, the good thing is that performance is traditionally not the first motivation for supporting prepared statements. The primary motivation is to separate the construction of the SQL code from the provision of the query arguments in client code. It's an argument about programmer productivity and code security.

Prepared statements are the mechanism provided by all SQL databases to enforce that query arguments are provided with the right type and do not interfere with the structure of the query. It's a strong protection against "SQL injection" (a classical security vulnerability)

[exalate-issue-sync[bot]]

Ben Darnell (bdarnell) commented: The mysql docs make a useful distinction between "Prepared Statements in Application Programs" (which are how prepared statements are normally used in practice) and "Prepared Statements in SQL Scripts" (i.e. the PREPARE/EXECUTE/DEALLOCATE statements) which are less commonly used but are easier to document because they're language-neutral.

https://dev.mysql.com/doc/refman/8.0/en/sql-syntax-prepared-statements.html

[exalate-issue-sync[bot]]

Raphael 'kena' Poss (knz) commented: If we organize another doc fixit day, I'll gladly volunteer to look into this.

[exalate-issue-sync[bot]]

Raphael 'kena' Poss (knz) commented: sorry closed by mistake.

[exalate-issue-sync[bot]]

Jesse Seldess (jseldess) commented: Marking this as part of https://github.com/cockroachdb/docs/issues/2944.

[exalate-issue-sync[bot]]

Andy Kimball (andy-kimball) commented: I'd like to bump up the priority of this, since prepared statements now cache more than just the parsed statement, and we want to be encouraging people to use them. I just tried to search for docs on PREPARE and EXECUTE and couldn't find much.

[exalate-issue-sync[bot]]

Jesse Seldess (jseldess) commented: @rmloveland, @awoods187, over the next weeks, as you look at the sql docs backlog and prioritize the most important work, let's consider keeping this one high on the list.

[exalate-issue-sync[bot]]

Amruta Ranade commented: Discussion with Raphael:

• Why Prepared Stmts: For two reasons: security and performance
  • Security aspect: To prevent SQL injection
    • Solution: Separate SQL syntax from passing parameters/placeholder to a query.
  • Performance aspect: Prepared stmt allows you to prepare the logical plan once for multiple executions and thereby amortize the cost of logical planning.

(Ask Andy K. where's the line in the SQL -> Parse -> Logical plan -> Phy. plan -> Execute sequence where we do things once, and then multiple times.)

When to use it: Whenever you can or must separate the SQL from the query parameters. (In scenarios where user-input parameters are used)

How to use it: Nearly all client drivers provide separate APIs for direct execution and prepared execution. (Give table with examples: ask Rich). Look at your driver docs, and find the prepared APIs.Only applicable to developers who know how to use drivers. Most ORMs already do this automatically.

We don't need things written here: https://dev.mysql.com/doc/refman/8.0/en/sql-syntax-prepared-statements.html We need a note saying "You might find PREPARE and EXECUTE statements in other DBs, but CRDB doesn't recommend using them. Use your client drivers' APIs instead."

[exalate-issue-sync[bot]]

Amruta Ranade commented: Excellent reference: https://www.postgresql.org/docs/9.3/sql-prepare.html

[exalate-issue-sync[bot]]

Amruta Ranade commented: Radu: Document the use-cases/benefits of PREPARED: security and performance. Give examples and explain difference: "SELECT ... 1" and "SELECT ...$1" and how to use it correctly. Give examples for different client libraries (Go would be a good starting point).

Andy W: Call out that "most ORMs handle this, but if you see any problems, contact us"

Andy W and Radu: Agree with Raphael. Don't need to document PREPARE, EXECUTE. Call out that we don't recommend using them.

Note to self: Reviewers: Andy W and Radu.

[exalate-issue-sync[bot]]

Jesse Seldess (jseldess) commented: After chatting about this with @timveil, it doesn't seem to be a priority for technical evaluations, so we should move this out of scope for 19.2. Chime in if you disagree.

[exalate-issue-sync[bot]]

Andy Woods (awoods187) commented: Talked it over with @RaduBerinde and that makes sense to us

[exalate-issue-sync[bot]]

Jesse Seldess (jseldess) commented: Closing this issue for now, as it's quite old and hasn't gotten priority. If it becomes important again, we can reopen or create a fresh issue.

[mari-crl]

This issue is an accidental duplicate created by automation. The correct issue that is synced to the corresponding Jira issue is #1333.