8 ms·

The key point is that an abstraction layer trying to implement an API that looks like parametrized queries is not equivalent to actual parametrized queries wher

The key point is that an abstraction layer trying to implement an API that looks like parametrized queries is not equivalent to actual parametrized queries where the query and parameters are kept separately, and the SQL text is parsed and execution plan is formed by the DB engine before the parameters are even considered. If your DB library is inserting the parameters in a text query before sending it off to the server to be parsed as arbitrary SQL, that's not a parametrized query, but just fake smoke and mirrors.

Aeolun 5y ago
> that's not a parametrized query, but just fake smoke and mirrors. And still much preferable to not having it at all. Don’t let perfect be the enemy of good.
- hn_throwaway_99 5y ago
  Well, in this case mysqljs pretty well fucked up the implementation here in that this sort of bug should never happen when using prepared statements, so I'd say it's worse than not having it at all, because it breaks the standard safety guarantees of prepared statements.
  - Aeolun 5y ago
    This implies that the alternative to using mysqljs is using prepared statements, which is most definitely not the case. The alternative for people using a library like this is to send plain queries to the db.
- moron4hire 5y ago
  That only applies when perfect is not yet achievable. Real parameterized queries exist and can be had today.
  - exac 5y ago
    And has since June 2004 in MySQL. https://en.wikipedia.org/wiki/MySQL#Milestones https://en.wikipedia.org/wiki/MySQL#Milestones
  - DaiPlusPlus 5y ago
    > Real parameterized queries exist and can be had today /s/today/30 years ago: SQL-92 describes them in chapter 4.18 - which at the time was just standardizing something almost all vendors already had in some form or another, for example, I'm pretty sure even the first release of ODBC in the late 1980s also had parameters. Disclaimer: My pontifications below derive from my life's experiences starting with Access/JET Red as a sprog, to my current professional work with MS SQL Server, Postgres (and MySQL, I'll admit - but I'll say MariaDB instead) built over the past 17 years - but I have absolutely zero experience with Oracle and Db2, so I honestly don't know what cool language features they have (and they must be way ahead of the ISO spec, otherwise why else would people pay so much for it?... Hmm, then again, Oracle still doesn't have a bit/bool column type, does it?). Anyway: However, the expressiveness of parameters hasn't changed much since the original design of ODBCS - at least as far as I'm aware: Query parameters are still mere scalar value placeholders instead of hardcoding literals inside queries and statements: with limited exceptions like T-SQL's ceremony-laden table-valued-parameters and Postgre's array types, it's still not possible to parameterize database object identifiers or even have a true variadic `WHERE IN ( ... )` predicate without resorting to Dynamic-SQL, nor can we use parameters to conditionally enable or disable query predicate clauses: yet these are all essential features for any kind of ORM or language-integrated-query system built today (the canonical example being Linq/EF in .NET and TypeORM or Prisma for TypeScript/JS. Why isn't anyone meaningfully advocating for a "SQL/2"? I know backwards-compatibility is 100% essential, but that's the easy part (because relational calculus and relational are isomorphisms, hence why semantic-preserving query translation between different SQL engines is a solved problem), but lack-of backwards-compatibility is usually the reason most good-ideas die in committee - so how come Google was successful in pushing HTTP/2 and even HTTP/3, but we're still using 1980s-level SMTP and SQL? Are all of the major RDBMS vendors so afraid of change that they're willing to forgoe winning-over millions of new customers if they can ship a usable, flexible, expressive, and safe query (and query-building) language? I'm just blabbering at this point. Forgive me, but I need something to distract me from the outside world right now.
    - jhgb 5y ago
      > /s/today/30 years ago: SQL-92 describes them in chapter 4.18 - which at the time was just standardizing something almost all vendors already had in some form or another, for example, I'm pretty sure even the first release of ODBC in the late 1980s also had parameters. Interbase must have had them in 1980s since Interbase (and now Firebird) compiles ESQL queries into static BLR code. Since there's no way to compile a new query at runtime (other than by outright switching to using Interbase/Firebird's DSQL C API for a non-compile-time query), all these queries have to be parameterized to be of any use.
  - Aeolun 5y ago
    Yeah, but you’re just shifting the location you expect bugs. Using parametrized queries at SQL level doesn’t guarantee you don’t have bugs of this kind at the database layer. Obviously they’ve been proven for tens of years now, so it’s extremely unlikely, but conceptually it isn’t different.
- sqliwtf 5y ago
  That’s the kind of thing you say to ship a product to market, to eschew a non-critical feature. Parametrized Queries for a SQL library are a critical “do not ship without” feature. You do not lie and tell your user you have a safe product causing them to have a compromised system. I hope to God you are not creating production systems anywhere.
  - jsiaajdsdaa 5y ago
    Imagine working on a flight controlller that has functions that produce the wrong values for flight control! "it's ok, something is better than nothing" is not true!
  - tragictrash 5y ago
    We had a candidate with 'wrote dynamic sql' listed under a job he held previously. They also couldn't answer the question 'how do you prevent a sql injection attack'. He had something like 5 years of experience. I don't understand how anyone but the greenest of devs doesn't comprehend the importance of these kind of things. But alas, I see it everywhere, including the comment you are responding to.
    - papercrane 5y ago
      I had a similar candidate a couple weeks ago. Their CV made a big deal about a dynamic SQL framework they wrote for a previous job, but they just gave me a blank look when I asked how they mitigated SQL injection attacks. I ask about SQL injection pretty regularly, and it's scary how many devs don't seem to even know what it is.
      - DaiPlusPlus 5y ago
        > I ask about SQL injection pretty regularly, and it's scary how many devs don't seem to even know what it is. That's either a good thing: because they grew-up with database libraries designed to encourage, if not force, the use of parameterized queries, so it was never a problem for them. ...or it's a bad thing, because they grew-up in the early-days of PHP 4x, learning from PHP/MySQL tutorials written by people who'd today be considered utterly unqualified to speak at length about programming: the kinds of things that only happen when the blind were leading the blindfolded: nightmares like actively encouraging concatenating $_GET values directly into mysql_query() strings because it means having less variables, and less variables means better performance (right?!) - and they never learned otherwise. ----- I have a pet theory that people who got started with PHP in the 2000s who are still working today are so burned from their earlier experiences that they're now the most detail-oriented and best-practices-following programmers around, regardless of the language they use today - while the people who never experienced hardship (to the extent that having to use PHP is a hardship...) become complacent, and our ever-increasing reliance on unvetted external dependencies (in all language ecosystems, imo) is going to end badly. Or not. No idea, honestly!
gruez 5y ago
>If your DB library is inserting the parameters in a text query before sending it off to the server to be parsed as arbitrary SQL, that's not a parametrized query, but just fake smoke and mirrors. Do you have examples of libraries that's actually a "parametrized query"? The python libraries I encountered (psycopg2, sqlalchemy) both seem to be "fake smoke and mirrors".
- tedunangst 5y ago
  sqlite3 con.execute() appears to prepare and execute a statement, with bound parameters.
- kayodelycaon 5y ago
  Active Record from Ruby on Rails does. It’s actually a problem on older versions because it may incorrectly generate duplicates of queries that could use the same prepared statement but don’t. Can cause unexpected memory usage.
- PeterisP 5y ago
  This is one of the major differences between psycopg2 and psycopg3 - https://www.psycopg.org/psycopg3/docs/basic/from_pg2.html#server-side-binding https://www.psycopg.org/psycopg3/docs/basic/from_pg2.html#se...
- bgrainger 5y ago
  MySqlConnector (C# - https://github.com/mysql-net/MySqlConnector https://github.com/mysql-net/MySqlConnector) uses "smoke and mirrors" for the "text" protocol (because MySQL Server has no way to send parameters as part of a `COM_QUERY` packet, https://dev.mysql.com/doc/internals/en/com-query.html https://dev.mysql.com/doc/internals/en/com-query.html) and true parameters for the "binary" protocol, which does (https://dev.mysql.com/doc/internals/en/com-stmt-execute.html https://dev.mysql.com/doc/internals/en/com-stmt-execute.html). Every MySQL client needs to do something similar, due to the design of the MySQL protocol.
- deleted 5y ago
  [deleted]
- 5y ago
tedunangst 5y ago
From the mysqljs readme: "Caution This also differs from prepared statements in that all ? are replaced, even those contained in comments and strings." Eek. "select * from classes where teacher = '?'" -> boom.
tyingq 5y ago
Wordpress also has faked parameterized queries. https://github.com/WordPress/WordPress/blob/4ae0744585ea9417da0b8834b97e15da6fcdbe5f/wp-includes/wp-db.php#L1395 https://github.com/WordPress/WordPress/blob/4ae0744585ea9417... They do seem to have resisted attacks for quite a while though.
- jolux 5y ago
  Reading WordPress internals like this makes me feel inclined not to use it.
- SahAssar 5y ago
  Hasn't WP had multiple SQL injection CVE's? WP is famously terrible at security.
bgrainger 5y ago
One major problem is that the MySQL "text" protocol doesn't support true parameterised queries. There's no way to pass parameters in the `COM_QUERY` packet: https://dev.mysql.com/doc/internals/en/com-query.html https://dev.mysql.com/doc/internals/en/com-query.html That's probably why many of the libraries mentioned in this thread use "smoke and mirrors". Of course, it is quite possible to correctly escape a value by rendering it to a string first, _then_ encoding the whole thing. To have true parameterised queries, you need to use the "binary" protocol, which many MySQL libraries don't offer support for. (MySQL also has some frustrating limitations with the binary protocol, such as not allowing a SQL string containing more than one statement to be prepared.)
- namibj 5y ago
  PostgreSQL shares that limitation on it's extended query protocol (needed for prepared statements), though you can send multiple queries off before sending the "I'm waiting, hurry up and get back with answers" packet to the server. Not that the native C library (libpq) supports such pipelining...
tomnipotent 5y ago
> 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.
tus666 5y ago
This is a perfect example of why I often just read the comments before the links on HN. Saves a lot of face-palming.
cromd 5y ago
> [...] execution plan is formed by the DB engine before the parameters are even considered Is this really possible if the server considers table statistics (i.e. frequencies of certain values) when forming a plan?
- thinkharderdev 5y ago
  Typically the SQL statement will be parsed to a logical plan first, which then gets optimized and then turned into a physical execution plan. Statistics are used for a second layer of optimization on the physical execution plan.
mirekrusin 5y ago
You can do it right with ie. combinators [0] where you explicitly specify at which position you are in (value, identifier etc) and have much more control over query building (dynamism and flexibility that value-only parametrized query/prepared statement doesn't provide). Tagged templates are fantastic to represent safe query mini-"dsl". [0] https://github.com/appliedblockchain/tsql/ https://github.com/appliedblockchain/tsql/

Aeolun 5y ago

> that's not a parametrized query, but just fake smoke and mirrors. And still much preferable to not having it at all. Don’t let perfect be the enemy of good.

hn_throwaway_99 5y ago
Well, in this case mysqljs pretty well fucked up the implementation here in that this sort of bug should never happen when using prepared statements, so I'd say it's worse than not having it at all, because it breaks the standard safety guarantees of prepared statements.
- Aeolun 5y ago
  This implies that the alternative to using mysqljs is using prepared statements, which is most definitely not the case. The alternative for people using a library like this is to send plain queries to the db.
moron4hire 5y ago
That only applies when perfect is not yet achievable. Real parameterized queries exist and can be had today.
- exac 5y ago
  And has since June 2004 in MySQL. https://en.wikipedia.org/wiki/MySQL#Milestones https://en.wikipedia.org/wiki/MySQL#Milestones
- DaiPlusPlus 5y ago
  > Real parameterized queries exist and can be had today /s/today/30 years ago: SQL-92 describes them in chapter 4.18 - which at the time was just standardizing something almost all vendors already had in some form or another, for example, I'm pretty sure even the first release of ODBC in the late 1980s also had parameters. Disclaimer: My pontifications below derive from my life's experiences starting with Access/JET Red as a sprog, to my current professional work with MS SQL Server, Postgres (and MySQL, I'll admit - but I'll say MariaDB instead) built over the past 17 years - but I have absolutely zero experience with Oracle and Db2, so I honestly don't know what cool language features they have (and they must be way ahead of the ISO spec, otherwise why else would people pay so much for it?... Hmm, then again, Oracle still doesn't have a bit/bool column type, does it?). Anyway: However, the expressiveness of parameters hasn't changed much since the original design of ODBCS - at least as far as I'm aware: Query parameters are still mere scalar value placeholders instead of hardcoding literals inside queries and statements: with limited exceptions like T-SQL's ceremony-laden table-valued-parameters and Postgre's array types, it's still not possible to parameterize database object identifiers or even have a true variadic `WHERE IN ( ... )` predicate without resorting to Dynamic-SQL, nor can we use parameters to conditionally enable or disable query predicate clauses: yet these are all essential features for any kind of ORM or language-integrated-query system built today (the canonical example being Linq/EF in .NET and TypeORM or Prisma for TypeScript/JS. Why isn't anyone meaningfully advocating for a "SQL/2"? I know backwards-compatibility is 100% essential, but that's the easy part (because relational calculus and relational are isomorphisms, hence why semantic-preserving query translation between different SQL engines is a solved problem), but lack-of backwards-compatibility is usually the reason most good-ideas die in committee - so how come Google was successful in pushing HTTP/2 and even HTTP/3, but we're still using 1980s-level SMTP and SQL? Are all of the major RDBMS vendors so afraid of change that they're willing to forgoe winning-over millions of new customers if they can ship a usable, flexible, expressive, and safe query (and query-building) language? I'm just blabbering at this point. Forgive me, but I need something to distract me from the outside world right now.
  - jhgb 5y ago
    > /s/today/30 years ago: SQL-92 describes them in chapter 4.18 - which at the time was just standardizing something almost all vendors already had in some form or another, for example, I'm pretty sure even the first release of ODBC in the late 1980s also had parameters. Interbase must have had them in 1980s since Interbase (and now Firebird) compiles ESQL queries into static BLR code. Since there's no way to compile a new query at runtime (other than by outright switching to using Interbase/Firebird's DSQL C API for a non-compile-time query), all these queries have to be parameterized to be of any use.
- Aeolun 5y ago
  Yeah, but you’re just shifting the location you expect bugs. Using parametrized queries at SQL level doesn’t guarantee you don’t have bugs of this kind at the database layer. Obviously they’ve been proven for tens of years now, so it’s extremely unlikely, but conceptually it isn’t different.
sqliwtf 5y ago
That’s the kind of thing you say to ship a product to market, to eschew a non-critical feature. Parametrized Queries for a SQL library are a critical “do not ship without” feature. You do not lie and tell your user you have a safe product causing them to have a compromised system. I hope to God you are not creating production systems anywhere.
- jsiaajdsdaa 5y ago
  Imagine working on a flight controlller that has functions that produce the wrong values for flight control! "it's ok, something is better than nothing" is not true!
- tragictrash 5y ago
  We had a candidate with 'wrote dynamic sql' listed under a job he held previously. They also couldn't answer the question 'how do you prevent a sql injection attack'. He had something like 5 years of experience. I don't understand how anyone but the greenest of devs doesn't comprehend the importance of these kind of things. But alas, I see it everywhere, including the comment you are responding to.
  - papercrane 5y ago
    I had a similar candidate a couple weeks ago. Their CV made a big deal about a dynamic SQL framework they wrote for a previous job, but they just gave me a blank look when I asked how they mitigated SQL injection attacks. I ask about SQL injection pretty regularly, and it's scary how many devs don't seem to even know what it is.
    - DaiPlusPlus 5y ago
      > I ask about SQL injection pretty regularly, and it's scary how many devs don't seem to even know what it is. That's either a good thing: because they grew-up with database libraries designed to encourage, if not force, the use of parameterized queries, so it was never a problem for them. ...or it's a bad thing, because they grew-up in the early-days of PHP 4x, learning from PHP/MySQL tutorials written by people who'd today be considered utterly unqualified to speak at length about programming: the kinds of things that only happen when the blind were leading the blindfolded: nightmares like actively encouraging concatenating $_GET values directly into mysql_query() strings because it means having less variables, and less variables means better performance (right?!) - and they never learned otherwise. ----- I have a pet theory that people who got started with PHP in the 2000s who are still working today are so burned from their earlier experiences that they're now the most detail-oriented and best-practices-following programmers around, regardless of the language they use today - while the people who never experienced hardship (to the extent that having to use PHP is a hardship...) become complacent, and our ever-increasing reliance on unvetted external dependencies (in all language ecosystems, imo) is going to end badly. Or not. No idea, honestly!

gruez 5y ago

>If your DB library is inserting the parameters in a text query before sending it off to the server to be parsed as arbitrary SQL, that's not a parametrized query, but just fake smoke and mirrors. Do you have examples of libraries that's actually a "parametrized query"? The python libraries I encountered (psycopg2, sqlalchemy) both seem to be "fake smoke and mirrors".

tedunangst 5y ago
sqlite3 con.execute() appears to prepare and execute a statement, with bound parameters.
kayodelycaon 5y ago
Active Record from Ruby on Rails does. It’s actually a problem on older versions because it may incorrectly generate duplicates of queries that could use the same prepared statement but don’t. Can cause unexpected memory usage.
PeterisP 5y ago
This is one of the major differences between psycopg2 and psycopg3 - https://www.psycopg.org/psycopg3/docs/basic/from_pg2.html#server-side-binding https://www.psycopg.org/psycopg3/docs/basic/from_pg2.html#se...
bgrainger 5y ago
MySqlConnector (C# - https://github.com/mysql-net/MySqlConnector https://github.com/mysql-net/MySqlConnector) uses "smoke and mirrors" for the "text" protocol (because MySQL Server has no way to send parameters as part of a `COM_QUERY` packet, https://dev.mysql.com/doc/internals/en/com-query.html https://dev.mysql.com/doc/internals/en/com-query.html) and true parameters for the "binary" protocol, which does (https://dev.mysql.com/doc/internals/en/com-stmt-execute.html https://dev.mysql.com/doc/internals/en/com-stmt-execute.html). Every MySQL client needs to do something similar, due to the design of the MySQL protocol.
deleted 5y ago
[deleted]
5y ago

tedunangst 5y ago

From the mysqljs readme: "Caution This also differs from prepared statements in that all ? are replaced, even those contained in comments and strings." Eek. "select * from classes where teacher = '?'" -> boom.

tyingq 5y ago

Wordpress also has faked parameterized queries. https://github.com/WordPress/WordPress/blob/4ae0744585ea9417da0b8834b97e15da6fcdbe5f/wp-includes/wp-db.php#L1395 https://github.com/WordPress/WordPress/blob/4ae0744585ea9417... They do seem to have resisted attacks for quite a while though.

jolux 5y ago
Reading WordPress internals like this makes me feel inclined not to use it.
SahAssar 5y ago
Hasn't WP had multiple SQL injection CVE's? WP is famously terrible at security.

bgrainger 5y ago

One major problem is that the MySQL "text" protocol doesn't support true parameterised queries. There's no way to pass parameters in the `COM_QUERY` packet: https://dev.mysql.com/doc/internals/en/com-query.html https://dev.mysql.com/doc/internals/en/com-query.html That's probably why many of the libraries mentioned in this thread use "smoke and mirrors". Of course, it is quite possible to correctly escape a value by rendering it to a string first, _then_ encoding the whole thing. To have true parameterised queries, you need to use the "binary" protocol, which many MySQL libraries don't offer support for. (MySQL also has some frustrating limitations with the binary protocol, such as not allowing a SQL string containing more than one statement to be prepared.)

namibj 5y ago
PostgreSQL shares that limitation on it's extended query protocol (needed for prepared statements), though you can send multiple queries off before sending the "I'm waiting, hurry up and get back with answers" packet to the server. Not that the native C library (libpq) supports such pipelining...

tomnipotent 5y ago

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

tus666 5y ago

This is a perfect example of why I often just read the comments before the links on HN. Saves a lot of face-palming.

cromd 5y ago

> [...] execution plan is formed by the DB engine before the parameters are even considered Is this really possible if the server considers table statistics (i.e. frequencies of certain values) when forming a plan?

thinkharderdev 5y ago
Typically the SQL statement will be parsed to a logical plan first, which then gets optimized and then turned into a physical execution plan. Statistics are used for a second layer of optimization on the physical execution plan.

mirekrusin 5y ago

You can do it right with ie. combinators [0] where you explicitly specify at which position you are in (value, identifier etc) and have much more control over query building (dynamism and flexibility that value-only parametrized query/prepared statement doesn't provide). Tagged templates are fantastic to represent safe query mini-"dsl". [0] https://github.com/appliedblockchain/tsql/ https://github.com/appliedblockchain/tsql/