What dangerous characters need to be filtered from user input prior to use in a DB2 SQL query?

Question

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!

Answer 1

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

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.

Answer 2

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

Answer 3

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;
         }
         else
         { 
            // 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.