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:

  1. Why does db2_real_escape_string() backslash-escape quotes instead of doubling them up? This seems incorrect.
  2. 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.
  3. 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.
  4. 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!

  • I have to agree - I think db2_real_escape_string is 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. – bobince Mar 10 '13 at 21:36
  • sorry, I know the answer is already marked. But I had my own experience from building a CMS from scratch and I would like to share my experience with you all. Hope thats alright. – BerggreenDK Mar 11 '13 at 11:34


The \x00 and \x1a hex representations for a blank space and a substitute character. \n and \r are line feed and carriage returns.

In most cases I don't think these would be terribly explotable characters. Thinking as I type here, you might be able to use \x00 (blank) to flood a static buffer. A \x1a (sub) character might have potential if the system is in binary mode, or if you're streaming binary, to trick the system into thinking that you've reached the end of a file prematurely.

As for \n & \r... I can't think of much.

Depending on how much information you have/can get on the system you may want to investigate the different possible character encodings. Sometimes you can do things like pass a UTF-8 into a process set to UTF-7 (random example, not any practical application that I'm thinking of) and get exciting results from the magic of conversion errors.

Best of luck with your pen-test.


"Sanitization" is fragile. You have to think of every single detail of how the database interprets "special characters", and you have to keep track of it closely, in case a new sub-version of the database introduces extra special characters. This is exhausting work and it often fails.

The (much) preferred way of inserting user data into SQL queries is to use prepared statement. This will be easier for you (the programmer), more robust, more secure, and quite possibly faster.

  • I appreciate this (correct) advice. However, I am a pentester and I would like to determine exploitability of the current design. If I can provide some sort of proof of concept sql injection attack, the client will be much more likely to fix the code (e.g., move to prepared statements). Too often I will point out something like this ("hey, you guys really shouldn't be relying on sanitization functions/ad-hoc queries") and the response will be "can you prove that it's currently vulnerable?". Thus, I'm trying to determine why these special characters are dangerous, and how they might be abused. – octagonC Mar 10 '13 at 0:59
  • It's hard to suggest ways to bypass a sanitizer without being able to play with the sanitizer itself. But, as always with security, the default assumption must be that something is insecure unless proven otherwise. – Stephen Touset Mar 10 '13 at 4:43

For validation of critical user input, that can break your SQL strings I would make a "valid characters array/string" and then check each character in the user input must be within the allowed string.

Like if you only would allow numbers the string could be done something like:

function ParseIntoValid(userinput)
    string validOutput = "";
    Array allowedChars = new Array("0123456789");

    foreach (char c in userinput[])

        if (c.instr(allowedChars))

            // if this is a parser, then add valid chars to new output
            validOutput += c;
            // exit loop and return false/error if you just need a check or just ignore the illigal chars

you can easily wrap this check into a simple "CheckIfValid(input)" or "ParseIntoValid(input)" depending on your goal.

By parsing and not just checking, you get a valid output, perhaps it has less characters, but if the user enters a single illigal char by fault, this function will help the "good user" and the "bad user" will still not gain access using them.

  • Agreed, whitelisting is almost always a better approach than blacklisting. I think this weak sanitization function I'm analyzing is a good example of this concept. – octagonC Mar 12 '13 at 6:13

Your Answer

