Undocumented feature: Server-Side Prepared Statements

[jseldess]

Jesse Seldess 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

[jseldess]

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)

[bdarnell]

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

[knz]

If we organize another doc fixit day, I'll gladly volunteer to look into this.

[knz]

sorry closed by mistake.

[jseldess]

Marking this as part of https://github.com/cockroachdb/docs/issues/2944.

[andy-kimball]

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.

[jseldess]

@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.

[Amruta-Ranade]

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."

[Amruta-Ranade]

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

[Amruta-Ranade]

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.

[jseldess]

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.

[awoods187]

Talked it over with @RaduBerinde and that makes sense to us

[jseldess]

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.

[exalate-issue-sync[bot]]

Richard Loveland (rmloveland) commented: Ryan Kuo assigned this and https://cockroachlabs.atlassian.net/browse/DOC-7025 to you as the SQL Queries person

however let me know if it isn’t Queries and should be Sessions/etc instead? I went with queries b/c this issue (DOC-2091) was already tagged as that product area

[exalate-issue-sync[bot]]

Ryan Kuo (taroface) commented: Dingding Lu Should we prioritize this long-undocumented feature at some point? I’m unclear on how big of a gap it is.