Hex methods are often used as attacks in SQL injection

As you can see, people suggest you to use prepared statements at the most. It's not wrong, but when your query is executed just once per process, there would be a slightly performance penalty.

I was facing this issue, but I think I solved it in very sophisticated way - the way hackers use to avoid using quotes. I used this in conjuction with emulated prepared statements. I use it to prevent all kinds of possible SQL injection attacks.

My approach:

• If you expect input to be integer make sure it's really integer. In a variable-type language like PHP it is this very important. You can use for example this very simple but powerful solution: sprintf("SELECT 1,2,3 FROM table WHERE 4 = %u", $input);
• If you expect anything else from integer hex it. If you hex it, you will perfectly escape all input. In C/C++ there's a function called mysql_hex_string(), in PHP you can use bin2hex().
  Don't worry about that the escaped string will have 2x size of its original length because even if you use mysql_real_escape_string, PHP has to allocate same capacity ((2*input_length)+1), which is the same.
• This hex method is often used when you transfer binary data, but I see no reason why not use it on all data to prevent SQL injection attacks. Note that you have to prepend data with 0x or use the MySQL function UNHEX instead.

So for example the query:

SELECT password FROM users WHERE name = 'root'

Will become:

SELECT password FROM users WHERE name = 0x726f6f74

or

SELECT password FROM users WHERE name = UNHEX('726f6f74')

Hex is the perfect escape. No way to inject.

Difference between UNHEX function and 0x prefix

There was some discussion in comments, so I finally want to make it clear. These two approaches are very similar, but they are a little different in some ways:

0x prefix can only be used on data columns such as char, varchar, text, block, binary, etc.
Also its use is a little complicated if you are about to insert an empty string. You'll have to entirely replace it with '', or you'll get an error.

UNHEX() works on any column; you do not have to worry about the empty string.

---

Hex methods are often used as attacks

Note that this hex method is often used as an SQL injection attack where integers are just like strings and escaped just with mysql_real_escape_string. Then you can avoid use of quotes.

For example, if you just do something like this:

"SELECT title FROM article WHERE id = " . mysql_real_escape_string($_GET["id"])

an attack can inject you very easily. Consider the following injected code returned from your script:

SELECT ... WHERE id = -1 union all select table_name from information_schema.tables

and now just extract table structure:

SELECT ... WHERE id = -1 union all select column_name from information_schema.column where table_name = 0x61727469636c65

And then just select whatever data ones want. Cool isn't it?

But if the coder of injectable site would hex it, no injection would be possible because the query would look like this: SELECT ... WHERE id = UNHEX('2d312075...3635')