SQL injection when single quotes are escaped with two single quotes

Question

Is there any way to perform a SQL injection when single quotes are escaped by two single quotes? I know the MySQL server is using this specific technique to prevent against an attack. I'm trying to log in as a specific user but all of the common injections I've tried for the password have not worked successfully (i.e. ' or '1'='1, ' or ' 1=1, etc.).

possible duplicate of How can I prevent SQL-injection in PHP? — imtheman

spencer7593 spencer7593 · Accepted Answer · 2015-01-31T01:14:30

No, and yes.

There's no way to have an unsafe values "breakout" of literal values that are enclosed in single quotes, if the value being supplied is "escaped" by preceding single quotes by with an additional single quote.

That is, assuming that your statement is guaranteeing that string literals are enclosed in quotes, as part of the "static" SQL text.

example perl-ish/php-ish

$sql = "...  WHERE t.foo = '" . $safe_value . "' ... ";
                           ^                   ^

I've underscored here that the single quotes enclosing the literal are part of the SQL text. If $safe_value has been "escaped" by preceding each single quote in the "unsafe" value with another single value to make it "safe"...

$unsafe_value  $safe_value
-------------  ------------
I'm going      I''m going
'she''s'       ''she''''s''
1'='1 --       1''=''1 --

As long as the escaping is handled properly, that we guarantee that potentially unsafe values are are run through the escaping, then including single quotes in data values is not a viable way to "breakout" of a literal with the SQL text.

That's the "no" part of the answer.

The "yes" part of the answer.

One of the biggest problems is making sure this is done EVERYWHERE, and that a mistake has not been made somewhere, assuming that a potentially unsafe string is "safe", and is not escaped. (For example, assuming that values pulled from a database table are "safe", and not escaping them before including them in SQL text.)

Also, the single quote trick is not the only avenue for SQL injection. The code could still be vulnerable.

Firstly, if we're not careful about other parts of the statement, like the single quotes enclosing string literals. Or, if for example, the code were to run the $sql through some other function, before it gets submitted to the database:

$sql = some_other_function($sql);

The return from some_other_function could potentially return SQL text that was in fact vulnerable. (As a ridiculous example, some_other_function might replace all occurrences of two consecutive single quotes with a single single quote. DOH!)

Also, with the vast number of possible unicode characters, if we're ever running through a characterset translation, there's also a possibility that some unicode character could get mapped to a single quote character. I don't have any specific example of that, but dollars to donuts that somewhere, in that plethora of multibyte encodings, there's some unicode character somewhere that will get translated to a single quote in some target.

There's a default character in the target for unmapped characters in the source, and that's usually a question mark (or a white question mark in a black diamond.) It would be a huge problem if the default character in the target (for unmapped characters in the source) was a single quote.

Bottom line: escaping unsafe strings by replacing single quotes with two single quotes goes a long ways towards mediating (mitigating?) SQL injection vulnerabilities. But in and of itself, it doesn't guarantee that code is not vulnerable in some other way.

SQL injection when single quotes are escaped with two single quotes

2 Answers