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!