If "the input" is sanitized, poor guy would be left without an apostrophe in his name, and rightfully pissed off.
Prepared statements are the only way to do it.
Example:
INSERT INTO USERS (name) VALUES ('Gijs in \'t Veld');
... | name | ...
========================
... | Gijs in 't Veld | ...
(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)
Gijs in 't Veld
'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.
Yes. This is trivial. Any self-respecting junior programmer should be able to write this routine.
Most likely you'll get a
return "'" + replace(str, "'", "\\'") + "'"
return "'" + replace(replace(str,"\\","\\\\"),"'","\\'") + "'"
And if you think that is robust, you're likely in for a surprise when someone sends you some unicode data.
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')
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.
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';
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 syntaxI mean, look at all this boilerplate:
http://stackoverflow.com/questions/10409576/pass-table-value...
You might be interested to know your example doesn't escape that correctly. SQL syntax escapes quotes by doubling them:
('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.
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?
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.
Sanitization basically means taking a string which purports to conform to a particular grammar, verifying that it does and, if it does not forcing it to. But names don't really have a formal grammar, so you can't 'sanitize' one much (maybe trimming leading and trailing white space and normalizing internal white space runs to a single space? Maybe?).
Gijs in \'t Veld is a 'sanitized' string in a grammar which doesn't allow bare apostrophes - such as some string literal syntaxes - and escapes them with a backslash. If someone presents you with a string Gijs in 't Veld with the claim that it conforms to that grammar, they are wrong, and maybe the right thing to do then is to sanitize it as you did. Or you could strip the apostrophe out. Or replace it with a question mark. Your sanitization approach is arbitrary because by definition the string you have been given does not conform to the grammar it purports to so you do not know what to do with that bare apostrophe. Sanitization is not 'representation preserving' because it converts broken, ungrammatical strings (which therefore have undefined meaning) into grammatical strings (which have a defined meaning that you hope is what was intended).
But this string hasn't been presented as a string conforming to that grammar. It's been presented as a name, which is essentially an arbitrary valid Unicode string. The process of converting it to the restricted grammar with no bare apostrophes but retaining representation of the same underlying string is not 'sanitization' but 'encoding'. It is representation preserving (so long as the grammar to which we are encoding is capable of representing the string).
Failure to grasp the differences between these operations is what causes people to wind up sending people like this poor gentleman emails addressed to Gijs in \%t Veld
This SQL literal string:
'Gijs in \'t Veld'
Gijs in 't Veld
From a security perspective there is nothing more "proper" about prepared queries than correctly escaped non-prepared queries.
>> SELECT 'here is an apostrophe: \'';
>> returns: here is an apostrophe: '
Gijs in \'t Veld
Gijs in 't Veld
'Gijs in \'t Veld'
Gijs in 't VeldOnly escaping quotes that are inserted directly into SQL query strings is not what I understood by "sanitizing all input".
I don't know, but what I do know is that plenty of people still don't understand how to use SQL APIs for their languages. Perl's DBI has variable placeholders (similar to %x sequences in printf()) for dozen years already. Python's DB-API 2.0 (PEP 249) has placeholders, too. Heck, even PHP has those. And placeholders make any input sanitization unnecessary, because it's the library that does the escaping part of constructing a query. And this way is more convenient, too, as string concatenation rarely results in pretty code.
I quietly hope that they don't send concatenated query full of escapes, but some binary encoding of the query structure followed by serialized data parameters.
Otherwise there would be a whole business of exploiting those escapers. And wasted bandwidth.
Huh? MySQL at least you can telnet into and query with a text protocol, no?
Theoretically it's possible that between various 8bit/16bit encodings, locales, collations, client/server configuration mismatches somebody could find a creative way to smuggle some unescaped quotemark.
Binary protocol which parses SQL commands client-side and treats strings as blobs of bytes removes any chance of SQL injection for good.
I have a Group and a list of names of persons.
Group myGroup = "Groupia"
Person[] myPersons = ["Foo", "Bar", "Baz"]
SELECT *
FROM dbo.Person
WHERE name IN ('Foo', 'Bar', 'Baz')
AND GroupName = 'Groupia'
It would be easy to design an API to allow putting the parameter calculation with concatenation-like semantics but without the problems of concatenation. For example (using Pythonic multi-line strings):
query = new query()
query.Text = """
FROM dbo.Person
WHERE name IN (""" + query.ParamList(myPersons) + """)
AND GroupName = """ + query.Param(MyGroup) + """
"""
resultSet = Query.Execute()
When modifying data using some secret criteria and doing the unexpected is better than showing a validation error, all bets are off.
Doubly true for dynamic SQL, for those people who are "everything must be done in procs" types.
It doesn't care how you came up with your statements. If you concatenate strings, that's your private business.
If you don't offer that, your language sucks.
They describe how to send SQL to the server, but they are not SQL and the SQL is not the API. Different layer.
Every DB API I've ever used has looked like
query("INSERT INTO table VALUES (?1, ?2, ?3)", "a", "b", "c")
insert_into("table").values("a", "b", "c")