SQL injection using letters and numbers?

Question

I was wondering if it is at all possible for a hacker to perform an SQL injection attack using just letters and numbers. For example, let's take a look at this PHP code which uses regular expressions instead of the builtin PHP functions:

<?php
$id = preg_replace('/[^a-zA-Z0-9]/', '', $_GET['id']);
$result = $mysqli->query("SELECT * users WHERE id = '".$id."'");

In case your wondering what the code does, it removes all characters that are not letters or numbers and executes that query it in MySQL. I was wondering if a hacker could be able to exploit that code in some way and if not, what might the difference be between that and mysqli::prepare or mysqli::escape_string?

Answer 1

There would be only a security flaw if it would be possible to leave the MySQL string literal context the $id value is inserted into and to supply arbitrary SQL fragments. And this is only possible if $id contains the plain ' that would denote the ending delimiter of the MySQL string literal:

// resulting SQL statement with $id = "' OR '1'='1"
SELECT * users WHERE id = '' OR '1'='1'

Now since you’re removing any ' character from $id, it is not possible to leave the string literal context and thus it’s not possible to supply arbitrary SQL fragments.

However, this conclusion applies only for the case the value is used in a MySQL string literal. If you’re using the same input filtering on values in a different query, you still may be vulnerable to SQL injections.

This said, methods like mysqli::prepare and mysqli::escape_string are proper mitigation techniques. mysqli::prepare implements so called parameterized statement/query via prepared statements where the command (SQL statement) and the user supplied data are separated from each other so that the user supplied data cannot be mistakenly interpreted as command. mysqli::escape_string would be similar to you current technique as it just escapes certain characters of data to not being confused as control characters like the string literal ending delimiter.

Answer 2

(I would post this as a comment, but comments don't do links neatly).

This is quite similar to many questions at Stack Overflow.

The Open Web Application Security Project (OWASP) does note that escaping special characters is a defense against SQL injection:

1. Use Prepared Statements (Parameterized Queries) and Stored Procedures
2. If a parameterized API is not available...Escape Special Characters
3. Perform White List Input Validation

As far as proof of concept code that achieves SQLi without special characters - I have not seen any, but absence of evidence is not evidence of absence. If you can implement Parameterized Queries, why not do so?

Answer 3

A general rule of thumb: don't reinvent the wheel!

Although the code you provided is probably not exploitable there are some good reasons to use prepared statements (a good persistence framework is even better) whenever possible:

• Prepared statements are efficient
• You don't want to take care of every possible outcome of feeding exotic characters to your regular expression engine (see the mysql_escape_string() fiasco)
• Relating to the previous point: if the user data is stored/encoded/formated in non-trivial ways SQL smuggling may be possible
• In your example, defense is implemented in two phases: both the RE and the SQL expression must be tuned with the other component in mind. As your code base starts to grow (and maybe other developers join) it will be difficult to always take care of both steps and you will make an exploitable mistake sooner or later

Answer 4

SQL injection works is MySQL’s support for hexadecimal literals and the fact that the id column of the vote table is of a string type as in that case the following applies:

In string contexts, they act like binary strings, where each pair of hex digits is converted to a character:

mysql> SELECT 0x0f+0;
    -> 15

For PHP 0x… is numeric (i.e., is_numeric) and for MySQL 0x… is interpreted and stored as string, which later gets inserted into the above mentioned SELECT statement.

This wouldn’t be possible if either

id would have been a numeric data type, or the SELECT would have been a prepared statement.

So hexencoded payloads may get through as you are allowing 0-9 and a-z which can be used to inject hex encoded payloads starting with 0x[0-9a-f].....