Is hexing input sufficient to sanitize SQL Queries?

Question

I was reading last night on preventing SQL injections, and I ran across this answer:

How can I prevent SQL injection in PHP?

The comments from 'Your Common Sense' made it sound like that was dysfunctional/unsafe. However, in my (albeit limited) testing, I found that php's "bin2hex($var)" worked with anything I threw at it - literal number, number string, string of text - even when matching a numerical (tinyint) column.

My question is this: Is there a way to inject SQL when every user input is sanitized via hexing it? In essence, any time a query was made, it would look something like this:

$query="SELECT * FROM table WHERE someidentifier=UNHEX('".bin2hex($unsafe_user_input)."') LIMIT 1"

Basically translating to:

SELECT * FROM table WHERE someidentifier=UNHEX('0b99f') LIMIT 1

Are there any holes in this type of security?

PS - I'm not just looking for answers like "Why not just use PDO or MySQLi with prepared statements?" It may fall under the vast evil of preemptive optimization, but I'd rather not double my query overhead (and yes, I do understand that it can be faster with multiple identical queries, but that's not a situation I often encounter).

Answer 1

Is there a way to inject SQL when every user input is sanitized via hexing it?

If you knew why an SQL injection occurs, you would be able to answer this question yourself.

---

Let’s see. The CWE describes SQL injections (CWE-89) as follows:

The software constructs all or part of an SQL command using externally-influenced input […], but it does not neutralize or incorrectly neutralizes special elements that could modify the intended SQL command […]

Furthermore:

Without sufficient removal or quoting of SQL syntax in user-controllable inputs, the generated SQL query can cause those inputs to be interpreted as SQL instead of ordinary user data.

So basically: externally-influenced inputs in a generated SQL query are not interpreted as intended. The important part here is: not interpreted as intended.

If a user input is intended to be interpreted as a MySQL string literal but it isn’t, it’s an SQL injection. But why does it happen?

Well, string literals have a certain syntax by which they are identified by the SQL parser:

A string is a sequence of bytes or characters, enclosed within either single quote (“'”) or double quote (“"”) characters.

Additionally:

Within a string, certain sequences have special meaning […]. Each of these sequences begins with a backslash (“\”), known as the escape character. MySQL recognizes the escape sequences shown in Table 9.1, “Special Character Escape Sequences”.

Additionally, to be able to use quotes within string literals:

There are several ways to include quote characters within a string:

• A “'” inside a string quoted with “'” may be written as “''”.
• A “"” inside a string quoted with “"” may be written as “""”.
• Precede the quote character by an escape character (“\”).
• A “'” inside a string quoted with “"” needs no special treatment and need not be doubled or escaped. In the same way, “"” inside a string quoted with “'” needs no special treatment.

As all these latter mentioned sequences are special to string literals, it is necessary that any data, that is intended to be interpreted as a string literal, is properly processed to conform to these rules. This means in particular: if any of the mentioned characters are intended to be used in a string literal, they have to be written as one of the mentioned ways.

So if you look at it from this way, it is not even a question of security but simply of processing data so that they are being interpreted as intended.

The same applies to the other literals as well as other aspects of SQL.

---

So what about your question?

My question is this: Is there a way to inject SQL when every user input is sanitized via hexing it? In essence, any time a query was made, it would look something like this:

$query="SELECT * FROM table WHERE someidentifier=UNHEX('".bin2hex($unsafe_user_input)."') LIMIT 1"

Yes, that would be safe from SQL injections. bin2hex returns a string that contains only hexadecimal characters. And neither of these characters require a special treatment when using them in a MySQL string literal.

But seriously, why would anyone want to use these cumbersome formatting technique when there libraries and frameworks that supply convenient techniques like parameterized/prepared statements?