6 ms·

Using Multi-Byte Characters to Nullify SQL Injection Sanitizing

56 points by wlrm 10y ago

treve 10y ago
This is a pretty well-known problem, but mostly irrelevant if you use UTF-8.
- colejohnson66 10y ago
  Or, you know, just doing the right thing(tm) and using parameterized queries
  - ams6110 10y ago
    Yes. If you are building SQL by concatenating user inputs (escaped or not) you are doing it wrong.
    - Klathmon 10y ago
      IMO building SQL by concatenating anything feels wrong. I still do it, and i haven't used an ORM yet that is actually useful, but it still feels wrong.
- SloopJon 10y ago
  Section 3.1 of Unicode technical report 36 describes a couple of similar things specific to UTF-8: overlong sequences, and ill-formed subsequences. Does your software conform to Unicode 3.0 and earlier, 3.1 through 5.1, or 5.2 and later? And do your server and client software agree? If you don't know the answer (and you depend on string sanitization), you may be at risk.
- yuhong 10y ago
  What is particularly fun is the history of GB2312/GBK/GB18030. I wonder how easy it would have been to change Win95 to use UTF-8 for example.
jcranmer 10y ago
In order for this to take effect, you need: 1. To "sanitize" SQL injection by quoting parameters and building SQL strings manually 2. Your quoting function needs to be in a language that iterates over multibyte chars properly (i.e., you're not running naively on binary strings) 3. The output multibyte charset must be one of the afflicted charsets (mostly CJK charsets) 4. Your DBMS must be incapable of properly handling the relevant charset, and is instead checking it in a different charset. Point #4 makes it really hard to envision this ever being a problem, since if you're using a CJK charset on your platform already, you're quite likely to notice very quickly that something is horribly, horribly wrong.
- hultner 10y ago
  I was thinking the same thing, and does people really still build SQL-strings programmatically in the server application? Seems like this bad practice should be dead by now, not only does it open up for injection attacks but it also prevents the database from optimizing the query by precompiling, data aggregation and building smarter execution plans.
  - collyw 10y ago
    It should be dead by now, but I had to a move a website done by an agency to a new server. I don't really code PHP, but could see that it was full of concatenated SQL strings.
  - rbritton 10y ago
    It happens a lot in junk WordPress plugins. Even with built-in support for doing it better [1], far too many just use straight concatenation. [1]: https://developer.wordpress.org/reference/classes/wpdb/prepare/ https://developer.wordpress.org/reference/classes/wpdb/prepa...
Illniyar 10y ago
As always just use parameterized queries. This is the best and only defense you need against sql injection.
- imron 10y ago
  This. I really don't get why anyone in this day and age doesn't use parameterised queries. It's such a big security win and comes with such little programming overhead that it boggles my mind to think people still use manual string escaping.
  - omgtehlion 10y ago
    Not just security, but readability (in most languages) and performance gain also (in popular RDBMSes and drivers)
  - greenleafjacob 10y ago
    In Postgres until 9.2, the query plan for prepared statements is fixed at prepare time rather than time of execution, so you might end up with worse query plans. It's worth noting that 9.1 is still the version in Ubuntu 12.04, it's only 14.04 that has 9.3.
    - raverbashing 10y ago
      In which case different values would end up have a different query plan? NULL values?
      - ComodoHacker 10y ago
        Skewed distribution.
      - sixbrx 10y ago
        That's one of the main points of gathering statistics, so the query plan can depend on the values being queried. At least in Oracle, not sure about Postgres. For long running queries it can be a big win.
anaolykarpov 10y ago
I wonder, does that 0x5c byte has to be the last one in the character or can it also be the first, or the second one, followed by the hex value of ;, the end of instruction in mysql?
jacquesm 10y ago
In-band: bad. Out-of-band: good. The same goes for JavaScript, escape sequences, SQL string concatenation and so on. It always seems like a great idea but it is just about impossible to get it airtight. Better do your signaling, commands and meta-data through another channel.
jeffdavis 10y ago
"Web applications sanitize the apostrophe (') character in strings coming from user input being passed to SQL statements using an escape (\) character." Please, please don't say this. In the SQL standard, backslash is NOT the escape character for a string literal. PostgreSQL, starting in v8.2, began transitioning from C-style escapes (using backslash) to SQL standard escapes (where a single quote is escaped with another single quote). Standard behavior is the default, but can be controlled with the variable standard_conforming_strings. But you shouldn't have to know that anyway. Use out-of-band parameters that are passed in the wire protocol separately from the string. Web frameworks should already ensure this, and if they don't, they are likely broken. If you are writing a web framework and you need to use escaping for some reason: first, make sure you can't use parameters on the wire instead; then read the product-specific literal parsing rules very carefully, considering things like multibyte characters.
Kristine1975 10y ago
String literals in SQL were a mistake.

56 points by wlrm 10y ago

treve 10y ago

This is a pretty well-known problem, but mostly irrelevant if you use UTF-8.

colejohnson66 10y ago
Or, you know, just doing the right thing(tm) and using parameterized queries
- ams6110 10y ago
  Yes. If you are building SQL by concatenating user inputs (escaped or not) you are doing it wrong.
  - Klathmon 10y ago
    IMO building SQL by concatenating anything feels wrong. I still do it, and i haven't used an ORM yet that is actually useful, but it still feels wrong.
SloopJon 10y ago
Section 3.1 of Unicode technical report 36 describes a couple of similar things specific to UTF-8: overlong sequences, and ill-formed subsequences. Does your software conform to Unicode 3.0 and earlier, 3.1 through 5.1, or 5.2 and later? And do your server and client software agree? If you don't know the answer (and you depend on string sanitization), you may be at risk.
yuhong 10y ago
What is particularly fun is the history of GB2312/GBK/GB18030. I wonder how easy it would have been to change Win95 to use UTF-8 for example.

jcranmer 10y ago

In order for this to take effect, you need: 1. To "sanitize" SQL injection by quoting parameters and building SQL strings manually 2. Your quoting function needs to be in a language that iterates over multibyte chars properly (i.e., you're not running naively on binary strings) 3. The output multibyte charset must be one of the afflicted charsets (mostly CJK charsets) 4. Your DBMS must be incapable of properly handling the relevant charset, and is instead checking it in a different charset. Point #4 makes it really hard to envision this ever being a problem, since if you're using a CJK charset on your platform already, you're quite likely to notice very quickly that something is horribly, horribly wrong.

hultner 10y ago
I was thinking the same thing, and does people really still build SQL-strings programmatically in the server application? Seems like this bad practice should be dead by now, not only does it open up for injection attacks but it also prevents the database from optimizing the query by precompiling, data aggregation and building smarter execution plans.
- collyw 10y ago
  It should be dead by now, but I had to a move a website done by an agency to a new server. I don't really code PHP, but could see that it was full of concatenated SQL strings.
- rbritton 10y ago
  It happens a lot in junk WordPress plugins. Even with built-in support for doing it better [1], far too many just use straight concatenation. [1]: https://developer.wordpress.org/reference/classes/wpdb/prepare/ https://developer.wordpress.org/reference/classes/wpdb/prepa...

Illniyar 10y ago

As always just use parameterized queries. This is the best and only defense you need against sql injection.

imron 10y ago
This. I really don't get why anyone in this day and age doesn't use parameterised queries. It's such a big security win and comes with such little programming overhead that it boggles my mind to think people still use manual string escaping.
- omgtehlion 10y ago
  Not just security, but readability (in most languages) and performance gain also (in popular RDBMSes and drivers)
- greenleafjacob 10y ago
  In Postgres until 9.2, the query plan for prepared statements is fixed at prepare time rather than time of execution, so you might end up with worse query plans. It's worth noting that 9.1 is still the version in Ubuntu 12.04, it's only 14.04 that has 9.3.
  - raverbashing 10y ago
    In which case different values would end up have a different query plan? NULL values?
    - ComodoHacker 10y ago
      Skewed distribution.
    - sixbrx 10y ago
      That's one of the main points of gathering statistics, so the query plan can depend on the values being queried. At least in Oracle, not sure about Postgres. For long running queries it can be a big win.

anaolykarpov 10y ago

I wonder, does that 0x5c byte has to be the last one in the character or can it also be the first, or the second one, followed by the hex value of ;, the end of instruction in mysql?

jacquesm 10y ago

In-band: bad. Out-of-band: good. The same goes for JavaScript, escape sequences, SQL string concatenation and so on. It always seems like a great idea but it is just about impossible to get it airtight. Better do your signaling, commands and meta-data through another channel.

jeffdavis 10y ago

"Web applications sanitize the apostrophe (') character in strings coming from user input being passed to SQL statements using an escape (\) character." Please, please don't say this. In the SQL standard, backslash is NOT the escape character for a string literal. PostgreSQL, starting in v8.2, began transitioning from C-style escapes (using backslash) to SQL standard escapes (where a single quote is escaped with another single quote). Standard behavior is the default, but can be controlled with the variable standard_conforming_strings. But you shouldn't have to know that anyway. Use out-of-band parameters that are passed in the wire protocol separately from the string. Web frameworks should already ensure this, and if they don't, they are likely broken. If you are writing a web framework and you need to use escaping for some reason: first, make sure you can't use parameters on the wire instead; then read the product-specific literal parsing rules very carefully, considering things like multibyte characters.

Kristine1975 10y ago

String literals in SQL were a mistake.