Hacker News Reader

Comment by paulasmuth | Hacker News Reader

This is false. Of course you can use any special characters in a literal string without using prepared statements. You just have to escape them correctly.

Example:

     INSERT INTO USERS (name) VALUES ('Gijs in \'t Veld');

Inserts the following row into the database

     ... | name             | ...
     ========================
     ... | Gijs in 't Veld  | ...

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.

(In fact, some databases implement prepared statements as simple query string interpolation in the client/driver. So this might be exactly what's happening when you use 'prepared statements' depending on the db)

View on HN

How are you turning the user supplied string

   Gijs in 't Veld

into the SQL string literal string

   'Gijs in \'t Veld'

Is your method for doing so guaranteed to always produce a valid SQL string literal which represents the supplied string? Even if the user supplied string contains, for example:

* control characters * NULs * non-ASCII characters * Unicode combining characters * surrogate pairs

...?

Wouldn't it be nice not to have to worry about that?

Good. Then use a mechanism that lets you send the parameters as data outside of your SQL, such as parameterized prepared statements.

>> How are you turning the user supplied string into the SQL string literal string? Is your method for doing so guaranteed to always produce a valid SQL string literal which represents the supplied string?

Yes. This is trivial. Any self-respecting junior programmer should be able to write this routine.

Make sure that junior programmer is a true Scotsman as well since they're more likely to have an apostrophe in their name.

With respect, most junior programmers, whether self respecting or otherwise, would have trouble even recognizing that there is a need to cover some of those cases I mentioned, let alone successfully accommodating them.

Most likely you'll get a

   return "'" + replace(str, "'", "\\'") + "'"

which only after the first bug report gets turned into a

   return "'" + replace(replace(str,"\\","\\\\"),"'","\\'") + "'"

and that will have been arrived at after a lot of trial and error and incorrect numbers of backslashes.

And if you think that is robust, you're likely in for a surprise when someone sends you some unicode data.

Again, my problem is that the APIs for parameterized prepared statements in SQL servers are often ludicrously awful which leads programmers to throw up their hands for non-trivial SQL queries and just say "screw it, I'll just concatenate strings together".

Which could have easily been avoided if the APIs weren't crap, or if the API had exposed a method to say "escape all special characters in this string because it's getting concatenated into a query".

I mean, I can't count the DB/client-language APIs where writing a query that involves an IN clause with a provided array so that ["foo", "bar", "baz"]

became

    SELECT * FROM MyTable WHERE Name IN ('foo', 'bar', 'baz')

without writing a non-trivial amount of fiddly code to handle building a list of parameters.

That's an obvious use-case, but writing it in a parametric format is a gigantic PITA so developers just concat strings. It's not right but it's the expected outcome of a bad interface that drives people away from the best practices.

I have to agree with your assessment of SQL APIs. Funny how not a single one seems to have been designed by someone who'd seen printf & scanf.

In their defense, your obvious use case is evidence of a database design problem and shouldn't be in the API. The thing `Name` is in should itself have a name or type or class or group or something, capturing the reason foo, bar, and baz go together. Then the SQL is

    SELECT * FROM MyTable WHERE NameType = 'foo_group';

For the ad hoc alternative, you need an easy way to insert your array into a temporary table (again, API deficiency) or supply a table parameter, so you can do

    INSERT INTO #T values -- [your array here]
    SELECT * FROM MyTable WHERE Name IN (SELECT t from #t);

    SELECT * FROM MyTable WHERE Name IN ?;  -- vaporware SQL syntax

The table-valued approach is always my preferred option, but often that's a where bad APIs become a nuisance.

I mean, look at all this boilerplate:

http://stackoverflow.com/questions/10409576/pass-table-value...

> ('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?

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

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

Reply on news.ycombinator.com