19 ms·

I wonder how everyone in the original thread comments is still falling for this infamous "sanitize input!" trap. If "the input" is sanitized, poor guy would be

I wonder how everyone in the original thread comments is still falling for this infamous "sanitize input!" trap. 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.

MattBearman 11y ago
I agree that prepared statements are much better than sanitising inputs, but surely a sanitised version of his name would be Gijs in \'t Veld Leaving the apostrophe in tact
- oneeyedpigeon 11y ago
  "Sanitising" is a vague term and has different meanings depending on context. All it really means, at minimum, is "transforming data, if necessary, to ensure it won't do 'bad stuff'". That might be escaping it, but it could also be stripping certain characters out of it, or just rejecting it altogether.
- jameshart 11y ago
  you can't sanitize a name, because a name is not dirty. 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 \&#37;t Veld
- Scarblac 11y ago
  That would be silly, then the backslash will be shown everywhere.
  - tux3 11y ago
    I'm not advocating for escape characters instead of proper prepared queries, but in that case you would "simply" need to un-escape the name before showing it.
    - paulasmuth 11y ago
      No, you don't need to unescape it when displaying it. This SQL literal string: 'Gijs in \'t Veld' Is a representation for these ASCII bytes/content: Gijs in 't Veld In other words. The backslash will not be stored as part of the string into the database. It's just a hint to the SQL parser that the following single quote should be interpreted as a literal single quote char and not as the end-of-string-character. From a security perspective there is nothing more "proper" about prepared queries than correctly escaped non-prepared queries.
  - paulasmuth 11y ago
    I can't tell if you're trolling or not, but this is not how string escaping works in SQL or any other programming language that I know. >> SELECT 'here is an apostrophe: \''; >> returns: here is an apostrophe: ' The backslash is not part of the string but just a hint to the compiler. The literal string '\'' represents a one byte (if ASCII) string containing only a single apostrophe character. https://en.wikipedia.org/wiki/Escape_character https://en.wikipedia.org/wiki/Escape_character
    - jameshart 11y ago
      There's a difference between saying that Gijs in \'t Veld is a 'sanitized' version of Gijs in 't Veld and saying that 'Gijs in \'t Veld' is a SQL String literal representing the string Gijs in 't Veld
    - Scarblac 11y ago
Pxtl 11y ago
To me this is a failing of popular SQL APIs that don't provide a good way to escape characters and force you to either deal with horribly clumsy prepared statement APIs or just concatenate strings together.
- jameshart 11y ago
  Which popular SQL APIs force you to concatenate strings together? That is terrifying.
  - Pxtl 11y ago
    My point is that every SQL API allows that as a fallback simply because they can't stop it. So basically, if your proper, official, "this is how you should do it" API sucks, users will fallback to mashing strings together. Doubly true for dynamic SQL, for those people who are "everything must be done in procs" types.
    - vetinari 11y ago
      SQL is not an API. SQL is a language. It doesn't care how you came up with your statements. If you concatenate strings, that's your private business.
      - Pxtl 11y ago
        SQL Servers have an API for a client language, and often they are extremely cumbersome for prepared statements. Not to mention that in many cases this "language" has no coherent way to say the obvious operation of "Escape all the control characters in this string". If you don't offer that, your language sucks.
        vetinari 11y ago
        There are multiple APIs - from ODBC, ADODB to DB-API for Python or database-specific client libraries. They describe how to send SQL to the server, but they are not SQL and the SQL is not the API. Different layer.
saulr 11y ago
Sanitizing usually means escaping. i.e., sanitizing as to not be dangerous, escape any dangerous sequences.
billpg 11y ago
Shameless plug: http://blog.hackensplat.com/2013/09/never-sanitize-your-inputs.html http://blog.hackensplat.com/2013/09/never-sanitize-your-inpu...
- kjs3 11y ago
  tl;dr: Do all the things you should be doing under the moniker of "sanitize inputs", but just don't call it sanitizing inputs because I'm very literal and it bugs me. See also: 'hacker' v. 'cracker'.
dozzie 11y ago
> I wonder how everyone in the original thread comments is still falling for this infamous "sanitize input!" trap. 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.
- Etheryte 11y ago
  I think the issue stems from the fact that the thing you're likely to Google is "how to X in SQL", not "how to X in language Y SQL api".
- qb45 11y ago
  > And placeholders make any input sanitization unnecessary, because it's the library that does the escaping part of constructing a query. 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.
  - dozzie 11y ago
    According to how DBI, DBAPI2, and PDO are designed, it's database driver's job to add escaping as appropriate (if not using binary encoding; but this is driver's call, too), so there's not much to exploit. Remember that you don't only quote single-quotes with a backslash, you also need to quote backslashes in raw input, too. Only the bandwidth could be of any concern, but I don't remember any database with a text-based communication protocol.
    - lmm 11y ago
      > Only the bandwidth could be of any concern, but I don't remember any database with a text-based communication protocol. Huh? MySQL at least you can telnet into and query with a text protocol, no?
paulasmuth 11y ago
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)
- jameshart 11y ago
  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.
  - paulasmuth 11y ago
    >> 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.
    - biot 11y ago
      Make sure that junior programmer is a true Scotsman as well since they're more likely to have an apostrophe in their name.
    - jameshart 11y ago
dguaraglia 11y ago
Sanitizing input it's not just "let's replace these weird characters". That's what crappy programmers do when they adopt the "can't fail a request" mentality of certain paradigms (I'm looking at you PHP.) When modifying data using some secret criteria and doing the unexpected is better than showing a validation error, all bets are off.

MattBearman 11y ago

I agree that prepared statements are much better than sanitising inputs, but surely a sanitised version of his name would be Gijs in \'t Veld Leaving the apostrophe in tact

oneeyedpigeon 11y ago
"Sanitising" is a vague term and has different meanings depending on context. All it really means, at minimum, is "transforming data, if necessary, to ensure it won't do 'bad stuff'". That might be escaping it, but it could also be stripping certain characters out of it, or just rejecting it altogether.
jameshart 11y ago
you can't sanitize a name, because a name is not dirty. 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 \&#37;t Veld
Scarblac 11y ago
That would be silly, then the backslash will be shown everywhere.
- tux3 11y ago
  I'm not advocating for escape characters instead of proper prepared queries, but in that case you would "simply" need to un-escape the name before showing it.
  - paulasmuth 11y ago
    No, you don't need to unescape it when displaying it. This SQL literal string: 'Gijs in \'t Veld' Is a representation for these ASCII bytes/content: Gijs in 't Veld In other words. The backslash will not be stored as part of the string into the database. It's just a hint to the SQL parser that the following single quote should be interpreted as a literal single quote char and not as the end-of-string-character. From a security perspective there is nothing more "proper" about prepared queries than correctly escaped non-prepared queries.
- paulasmuth 11y ago
  I can't tell if you're trolling or not, but this is not how string escaping works in SQL or any other programming language that I know. >> SELECT 'here is an apostrophe: \''; >> returns: here is an apostrophe: ' The backslash is not part of the string but just a hint to the compiler. The literal string '\'' represents a one byte (if ASCII) string containing only a single apostrophe character. https://en.wikipedia.org/wiki/Escape_character https://en.wikipedia.org/wiki/Escape_character
  - jameshart 11y ago
    There's a difference between saying that Gijs in \'t Veld is a 'sanitized' version of Gijs in 't Veld and saying that 'Gijs in \'t Veld' is a SQL String literal representing the string Gijs in 't Veld
  - Scarblac 11y ago

Pxtl 11y ago

To me this is a failing of popular SQL APIs that don't provide a good way to escape characters and force you to either deal with horribly clumsy prepared statement APIs or just concatenate strings together.

jameshart 11y ago
Which popular SQL APIs force you to concatenate strings together? That is terrifying.
- Pxtl 11y ago
  My point is that every SQL API allows that as a fallback simply because they can't stop it. So basically, if your proper, official, "this is how you should do it" API sucks, users will fallback to mashing strings together. Doubly true for dynamic SQL, for those people who are "everything must be done in procs" types.
  - vetinari 11y ago
    SQL is not an API. SQL is a language. It doesn't care how you came up with your statements. If you concatenate strings, that's your private business.
    - Pxtl 11y ago
      SQL Servers have an API for a client language, and often they are extremely cumbersome for prepared statements. Not to mention that in many cases this "language" has no coherent way to say the obvious operation of "Escape all the control characters in this string". If you don't offer that, your language sucks.
      - vetinari 11y ago
        There are multiple APIs - from ODBC, ADODB to DB-API for Python or database-specific client libraries. They describe how to send SQL to the server, but they are not SQL and the SQL is not the API. Different layer.

saulr 11y ago

Sanitizing usually means escaping. i.e., sanitizing as to not be dangerous, escape any dangerous sequences.

billpg 11y ago

Shameless plug: http://blog.hackensplat.com/2013/09/never-sanitize-your-inputs.html http://blog.hackensplat.com/2013/09/never-sanitize-your-inpu...

kjs3 11y ago
tl;dr: Do all the things you should be doing under the moniker of "sanitize inputs", but just don't call it sanitizing inputs because I'm very literal and it bugs me. See also: 'hacker' v. 'cracker'.

dozzie 11y ago

> I wonder how everyone in the original thread comments is still falling for this infamous "sanitize input!" trap. 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.

Etheryte 11y ago
I think the issue stems from the fact that the thing you're likely to Google is "how to X in SQL", not "how to X in language Y SQL api".
qb45 11y ago
> And placeholders make any input sanitization unnecessary, because it's the library that does the escaping part of constructing a query. 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.
- dozzie 11y ago
  According to how DBI, DBAPI2, and PDO are designed, it's database driver's job to add escaping as appropriate (if not using binary encoding; but this is driver's call, too), so there's not much to exploit. Remember that you don't only quote single-quotes with a backslash, you also need to quote backslashes in raw input, too. Only the bandwidth could be of any concern, but I don't remember any database with a text-based communication protocol.
  - lmm 11y ago
    > Only the bandwidth could be of any concern, but I don't remember any database with a text-based communication protocol. Huh? MySQL at least you can telnet into and query with a text protocol, no?

paulasmuth 11y ago

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)

jameshart 11y ago
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.
- paulasmuth 11y ago
  >> 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.
  - biot 11y ago
    Make sure that junior programmer is a true Scotsman as well since they're more likely to have an apostrophe in their name.
  - jameshart 11y ago

dguaraglia 11y ago

Sanitizing input it's not just "let's replace these weird characters". That's what crappy programmers do when they adopt the "can't fail a request" mentality of certain paradigms (I'm looking at you PHP.) When modifying data using some secret criteria and doing the unexpected is better than showing a validation error, all bets are off.