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