3 ms·

> to actual parametrized queries Except there is no such thing as "actual parametrized queries", either as defined by the SQL standard or as supported by any o

> to actual parametrized queries Except there is no such thing as "actual parametrized queries", either as defined by the SQL standard or as supported by any of the major RDBMS vendors. The closest thing we have are prepared statements, which are basically session-scoped stored procedures used by client-side libraries to pull off the illusion of parametrized queries. I wouldn't be surprised that if you go back far enough, the idea of parametrized queries probably started in client libraries trying solve for SQL string soup with a dash of sprintf-like syntax sugar.

magicalhippo 5y ago
> Except there is no such thing as "actual parametrized queries", either as defined by the SQL standard or as supported by any of the major RDBMS vendors. We're in the process of adding support for MSSQL in addition to the DB server we've been using for ages. This is one of the pain points. They differ in how they handle "parameterized queries" enough to be a PITA.
default-kramer 5y ago
> Except there is no such thing as "actual parametrized queries" [...] as supported by any of the major RDBMS vendors. That would surprise me... I thought that when I send a parameterized query to PostgreSQL or MS SQL Server, most (or even all) of the query plan gets created without looking at any parameter values. And if that is true, then I think your "no such thing" claim cannot be true. If the query plan has already been created based on the unparameterized SQL string, then parameter values cannot cause it to do something crazy like drop an unrelated table. (But I haven't read the source code to either of those RDBMS, so maybe I am about to be surprised.)
- tomnipotent 5y ago
  > when I send a parameterized query to PostgreSQL or MS SQL Server You're not sending a parameterized query. The libraries are creating prepared statements under the hood, and managing them for you. This generally requires one call to prepare the statement and fetch some sort of handle, and then executing the statement with the handle from the first call. Part of the confusion is that it's common to see SQL libraries offer an API to parametrize queries using some sort of placeholder in the query, but it's really just a facade to perform local string interpolation along with some sort of vendor-specific sanitation scheme.
  - hn_throwaway_99 5y ago
    > This generally requires one call to prepare the statement and fetch some sort of handle, and then executing the statement with the handle from the first call. Not always. The Node `pg` module pipelines down the commands on the connection: it sends a `prepare` command, then a `bind` command, then an `execute` command. Yes, it uses prepared statements under the hood, but there is no wire round trip between prepare and bind.
    - tomnipotent 5y ago
      Looks like Postgres 14 added pipeline mode in libpq, good to know!
- hn_throwaway_99 5y ago
  > I thought that when I send a parameterized query to PostgreSQL or MS SQL Server, most (or even all) of the query plan gets created without looking at any parameter values. Your comment peaked my interest so I looked into what postgres does, and it's pretty ingenious in my opinion. It can create either a generic plan (not looking at parameter values) or a custom plan where it looks at parameter values and then uses statistics to find the best plan. By default, for the first 5 executions of a prepared statement uses a custom plan. If the execution costs of these different plans are pretty close to each other, from then on it just uses the generic plan (idea being that the extra cost of generating a custom plan each time isn't worth it), but if they are different enough (meaning looking at statistics can have a big speedup), then custom plans are used going forward. This behavior can be tweaked using DB flags.
tus666 5y ago
Of course there is. If the parameters are incorporated server side after the SQL is parsed then you have true parameterized queries. If the parameters are inserted before the SQL is parsed you have trouble.
- tomnipotent 5y ago
  > you have true parameterized queries In the sense that stored procedures and prepared statements are queries, sure. But your SQL statement and your values are not being sent in the same call to the database.

magicalhippo 5y ago

> Except there is no such thing as "actual parametrized queries", either as defined by the SQL standard or as supported by any of the major RDBMS vendors. We're in the process of adding support for MSSQL in addition to the DB server we've been using for ages. This is one of the pain points. They differ in how they handle "parameterized queries" enough to be a PITA.

default-kramer 5y ago

> Except there is no such thing as "actual parametrized queries" [...] as supported by any of the major RDBMS vendors. That would surprise me... I thought that when I send a parameterized query to PostgreSQL or MS SQL Server, most (or even all) of the query plan gets created without looking at any parameter values. And if that is true, then I think your "no such thing" claim cannot be true. If the query plan has already been created based on the unparameterized SQL string, then parameter values cannot cause it to do something crazy like drop an unrelated table. (But I haven't read the source code to either of those RDBMS, so maybe I am about to be surprised.)

tomnipotent 5y ago
> when I send a parameterized query to PostgreSQL or MS SQL Server You're not sending a parameterized query. The libraries are creating prepared statements under the hood, and managing them for you. This generally requires one call to prepare the statement and fetch some sort of handle, and then executing the statement with the handle from the first call. Part of the confusion is that it's common to see SQL libraries offer an API to parametrize queries using some sort of placeholder in the query, but it's really just a facade to perform local string interpolation along with some sort of vendor-specific sanitation scheme.
- hn_throwaway_99 5y ago
  > This generally requires one call to prepare the statement and fetch some sort of handle, and then executing the statement with the handle from the first call. Not always. The Node `pg` module pipelines down the commands on the connection: it sends a `prepare` command, then a `bind` command, then an `execute` command. Yes, it uses prepared statements under the hood, but there is no wire round trip between prepare and bind.
  - tomnipotent 5y ago
    Looks like Postgres 14 added pipeline mode in libpq, good to know!
hn_throwaway_99 5y ago
> I thought that when I send a parameterized query to PostgreSQL or MS SQL Server, most (or even all) of the query plan gets created without looking at any parameter values. Your comment peaked my interest so I looked into what postgres does, and it's pretty ingenious in my opinion. It can create either a generic plan (not looking at parameter values) or a custom plan where it looks at parameter values and then uses statistics to find the best plan. By default, for the first 5 executions of a prepared statement uses a custom plan. If the execution costs of these different plans are pretty close to each other, from then on it just uses the generic plan (idea being that the extra cost of generating a custom plan each time isn't worth it), but if they are different enough (meaning looking at statistics can have a big speedup), then custom plans are used going forward. This behavior can be tweaked using DB flags.

tus666 5y ago

Of course there is. If the parameters are incorporated server side after the SQL is parsed then you have true parameterized queries. If the parameters are inserted before the SQL is parsed you have trouble.

tomnipotent 5y ago
> you have true parameterized queries In the sense that stored procedures and prepared statements are queries, sure. But your SQL statement and your values are not being sent in the same call to the database.