3

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

Bob John
  • 447
  • 2
  • 4
  • 13
  • possible duplicate of [How can I prevent SQL-injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – imtheman Jan 31 '15 at 00:41

2 Answers2

4

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.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
2

if the input accepts unicode and is implicitly converted to ascii in the database (not as uncommon as it sounds) then an attacker can simply substitute ʻ or ʼ (0x02BB or 0x02BC) in place of single tick to get around the escaping mechanism and the implicit conversion will map those characters to single ticks (at least that's the case in SQL Server)

gordy
  • 9,360
  • 1
  • 31
  • 43
  • What would the corresponding inputs look like? "Username: Me Password 0x02BB 1=1" ...? – Bob John Jan 31 '15 at 02:39
  • @BobJohn the corresponding input would just be the unicode ʻ - we're assuming the whole input is already unicode and then gets converted to ascii later on. But if say the escaping mechanism is looking for it then there are all sorts of ways to hide, e.g.: nchar(0x02BB) nchar(0x02BA + 1) etc. – gordy Jan 31 '15 at 03:09
  • 1
    I tried nchar(0x02BB) or nchar(0x02BA + 1) 1=1 in the password field and that does not work. – Bob John Jan 31 '15 at 03:23
  • @BobJohn: To exploit a vulnerability like this requires a characterset translation. e.g. the form sends data encoded as UTF8, the application server processes the UTF8 encoding, the values get "escaped", and then later, the SQL text gets run through a characterset translation, and some of the UTF8 characters get translated to a single quote. The vulnerability is those special characters in the data that aren't recognized as single quote by the "escape" function, but that later get translated (by characterset translation) into single quote. – spencer7593 Jan 31 '15 at 05:33