3 ms·

> ('Gijs in \'t Veld') You might be interested to know your example doesn't escape that correctly. SQL syntax escapes quotes by doubling them: ('Gijs

> ('Gijs in \'t Veld') You might be interested to know your example doesn't escape that correctly. SQL syntax escapes quotes by doubling them: ('Gijs in ''t Veld') Correctness is not as easy as falling out of bed. > Prepared statements have nothing to do with this and are _in no way_ more resilient to SQL injection vulnerabilities than correctly interpolated non-prepared SQL queries. Actually, that's why prepared statements have everything to do with it: correct escaping is error prone. The simple advantage of prepared statements is that the parameters are handled as data, not as syntax. There's nothing to invent, and nothing to go wrong. > some databases implement prepared statements as simple query string interpolation Do you have one in mind? I can name 10 that don't, all names you'd recognize. Besides, you've hoisted yourself on your own petard. If, ad arguendum, correct interpolation is _in no way_ more vulnerable than prepared statements, and prepared statements are correct interpolation, how exactly are prepared statements worse?

paulasmuth 10y ago
>> You might be interested to know your example doesn't escape that correctly. It does -- You're right that the double-quote escaping is part of the original SQL standard, while the c-style escaping is an extension to it. However so is a lot of behaviour in modern SQL databases and it doesn't make my example incorrect. Off the top of my head, here is an incomplete list of databases that implement c-style string escaping: Mysql, Postgres, Vertica, BigQuery, Oracle 9.2 >> some databases implement prepared statements as simple query string interpolation -- Do you have one in mind? Yes, immediately both mongodb and bigquery come to my mind which are both pretty popular and do not currently support server-side prepared statements. If you google for jdbc drivers for theses databases some will implement 'prepared queries' as string interpolation on the client side. Random Example: https://github.com/jonathanswenson/starschema-bigquery-jdbc/blob/master/src/main/java/net/starschema/clouddb/jdbc/BQPreparedStatement.java#L175 https://github.com/jonathanswenson/starschema-bigquery-jdbc/... >> how exactly are prepared statements worse? That's not what I said. My point (and I think we agree here) was that using a correct interpolation routine is just as secure as using a prepared statement with regards to SQL injection vectors.

paulasmuth 10y ago

>> You might be interested to know your example doesn't escape that correctly. It does -- You're right that the double-quote escaping is part of the original SQL standard, while the c-style escaping is an extension to it. However so is a lot of behaviour in modern SQL databases and it doesn't make my example incorrect. Off the top of my head, here is an incomplete list of databases that implement c-style string escaping: Mysql, Postgres, Vertica, BigQuery, Oracle 9.2 >> some databases implement prepared statements as simple query string interpolation -- Do you have one in mind? Yes, immediately both mongodb and bigquery come to my mind which are both pretty popular and do not currently support server-side prepared statements. If you google for jdbc drivers for theses databases some will implement 'prepared queries' as string interpolation on the client side. Random Example: https://github.com/jonathanswenson/starschema-bigquery-jdbc/blob/master/src/main/java/net/starschema/clouddb/jdbc/BQPreparedStatement.java#L175 https://github.com/jonathanswenson/starschema-bigquery-jdbc/... >> how exactly are prepared statements worse? That's not what I said. My point (and I think we agree here) was that using a correct interpolation routine is just as secure as using a prepared statement with regards to SQL injection vectors.