Posted on

Recently I came across an unusual method of preventing SQL injection during a source code review. It is unusual in a sense whereby the function corrupts the input intentionally in an attempt to prevent SQL injection.

The following is a modified version of the function but the idea is similar:

    function formatString($query){
        $key = "z9"; $output = "";
        $arr = str_split($query, 2);
        foreach($arr as $value){
            $output = $output . $value . $key;
        }
        return $output;
    }

The code snippet above splits the string using _strsplit() into an array of groups of 2 characters. For example, if the input is “hello”, it will be split into [“he”,”ll”, “o”].

Subsequently, the code will append the $key value to the back of each element and return the output. So if the input was “hello”, the output in this case would be “hez9llz9oz9”.

And to revert the string to its original state, we can simply use the following function:

    function unformatString($query){
      $key = "z9"; $output = "";
      $outarray = explode($key,$query);
      foreach($outarray as $value){
        $output = $output . $value;
      }
      return $output;
    }

Prevention of SQL Injection

Suppose we have the following query:

SELECT * FROM TABLE WHERE name = ‘{user input}’

Our good old ‘ or 1=1’ will cause an SQL injection to occur leading to the statement always being true.

SELECT * FROM TABLE WHERE name = ‘’ or 1=1’

Now let’s see what happens when we use our custom formatString() function.

SELECT * FROM TABLE WHERE name = ‘ z9orz9 1z9=1z9”

The new query would result in an SQL syntax error. And if the error is suppressed (which it should, to prevent error-based SQLi), then we have successfully prevented an SQL injection.

Design

There are two factors to this design of defense mechanism.

1) The key must be more than one characters

Since the key is injected into the user’s input, the use of 1 character might pollute the original value.

If the user input is ‘Peter’, the key must not be one of the four letters used as the application is unable to “unformat” the string.

For example, if the letter t was used as the key then the input would be formatted to ‘pettetr’. Using the unformatString() function, the user input would strip all instances of ’t’ resulting in ‘peer’.

2) The injection length must be less than or equals to two

If the formatString() method splits in groups of 3 characters, we could actually form a malicious query. Since the attacker is allowed to form a payload with three character, ‘|’ is one of the example that works.

Using the same query, SELECT * FROM TABLE WHERE name = ‘{user input}’

The query would look like this after formatString() function:

SELECT * FROM TABLE WHERE name = ‘’|’z9’

The above is a valid query which queries the name where an empty string is bitwise OR-ed with the string ‘z9’.

In practice, the malicious user would aim to achieve the integer 0 by guessing the key and changing the operator since comparing string with an integer in MySQL always evaluates to true. You can read more here.

This is an unconventional way of prevention SQL injection, however, it is still recommended to sanitise user’s input and to use prepared statements.