I'm looking to fully understand how to properly filter/escape dangerous characters from user input that will be interpolated into a DB2 SQL query.
The sanitization routing that I'm analyzing works like this:
- Remove all backslashes ( s/\\// )
- Replace all single quotes with double single quotes ( s/'/''/ )
My first instinct was to examine an existing db2 sanitization function, to check for any differences. So I took a look at db2_real_escape_string which, it turns out, is quite different: it prepends backslashes to special characters in the string argument. Characters that are prepended with a backslash are \x00, \n, \r, \, ', " and \x1a.
Some specific questions I have are:
- Why does db2_real_escape_string() backslash-escape quotes instead of doubling them up? This seems incorrect.
- The sanitization function I'm analyzing fails to escape or filter \x00, \n, \r, \x1a, ". How could these characters be abused by an attacker? This is my primary question.
- Another deficiency of my sanitization function is that it is not charset-aware, but I'm guessing that this isn't an issue because db2, in this case, is configured to use to the default charset (which I'm guessing isn't multi-byte). So AFAIK, unless a multi-byte charset like GBK was configured, multi-byte sql injection attacks like this known vulnerability in mysql GBK shouldn't be an issue.
- Are there any other considerations/steps that the sanitization function I'm analyzing is missing?
I understand that using parameterized queries is the obvious solution. However, in this case, I am responsible for A) determining if the current design is exploitable and B) providing a patch for the sanitization function (if one is necessary).
edit I am basically looking to construct a proof of concept sql injection payload, given the above sanitization function and the following usage:
execute( " SELECT foo FROM bar WHERE col='" + my_sanitize($_GET['input']) + "'" )
Given the current sanitization function design (remove backslashes and double-up single quotes), is there any way to break out of single quotes as in the above usage example?
Thanks for your assistance!
db2_real_escape_stringis rubbish (really awful even by PHP standards). As I understand it (and I don't have an instance handy to verify) DB2 uses ANSI standard SQL string literal syntax, in which the only metacharacter is the apostrophe, and the way to escape it is to double it. The backslash removal should not be necessary, but if there is a vulnerability from omitting it (unlikely) then it'll be deeply app-logic-specific and trickier to find than typical SQL injection issues.