Does using hexadecimal encoding to prevent SQL injection have any disadvantages?

Question

Reading some questions and answers of SQL queries on SO, I saw this answer , which suggests that you can convert untrusted user input to hex, which by its very nature does not require escaping of any type and, therefore, completely and completely avoids the SQL feature -injection .

Which layer of database abstraction you use (PDO, mysqli, mysql, Pear DB, etc.) does not matter .

An example of a typical work request:

$DBH = new PDO('mysql:host=127.0.0.1;dbname=test', 'test', 'testpassword'); // could have been: //$bookTitle = bin2hex($_GET['title']); $bookTitle = bin2hex('Catch-22'); $query = "SELECT * from `books` WHERE `title` = UNHEX('$bookTitle')"; foreach ($DBH->query($query) as $row) { echo "\n<br />\n"; print_r($row); echo "\n<br />\n"; } 

I have included enough code here to quickly run the test if you have a database with a table, for example:

 CREATE TABLE `books` (`id` INT, `title` VARCHAR(100), `author` VARCHAR(100)) ENGINE=InnoDB CHARACTER SET='utf8'; INSERT INTO `books` VALUES(1, 'Catch-22', 'Joseph Heller'); 

Of course, this is simplified: usually you need to introduce validation, deactivate work and many other abstractions, but we want to focus on the issue - no more fluff than necessary to help provide easily executed examples.

I would like to know if there are technical flaws in this technique. I don’t specifically ask if this method has human shortcomings (futz is easier as a sloppy programmer, since it is clearly not as clean as using parameterized queries).

Yes, we can all agree that parameterized queries are less susceptible to poor programming or unsuccessful oversights. Therefore, please stick to the question - does this method help to avoid SQL injections of all kinds, unconditionally?

Can someone show an example of user input that violates this technique? Even a corner case, some specific MySQL server settings, or an old version of PHP that breaks it?

It is also suitable for whole replacements:

 // could have been: //$bookID = bin2hex($_GET['id']); $bookID = bin2hex(1); $query = "SELECT * from `books` WHERE `id` = UNHEX('$bookID')"; 

Other thoughts:

• Using this method, you avoid two calls to the database, as it happens when using (non-emulated) prepared statements (although prepared statements! = Parameterized queries).

• Reservations to some other proposed methods begin to cross-look at what is with exceptions in this case, for example this . Does hexadecimal encoding technology take full advantage of chaos-destroying cybercriminals using character set coding tricks and everything else?

• It seems that decoding on the database side may be limited to MySQL / MariaDB, although there may be third-party solutions for adding UNHEX () to PostgreSQL (or - not sure - in some databases, you could use a different method of placing hexadecimal literals in the query without using any UNHEX function)

Answer 1

We use this hexadecimal encoding method in an e-commerce system that has been living for several years.

I have yet to find a flaw, and as soon as you have an object written for each table in the database (with the corresponding retrieve and save functions), all this is so hidden from the user interface developers, they really don’t even pay attention.

This method works for

• block SQL injection
• supports any client encoding
• binary data storage

Its also very fast (we looked at base64 encoding, which would create shorter SQL strings, but require more CPU). In the tests, we did not see a measurable performance difference between the hexadecimal encoding of the entire string input, not.

Does this technique help avoid all kinds of SQL injections, of course?

I have not seen how anyone could SQLi if this method was always used (when binding any external data to the SQL statement) -

As is common in all methods (DCoder) - it must be universally used to provide universal protection - also mysql_real_escape_string.

the user "NB" says, but gives no technical arguments (maybe he was talking about PokemonGo vs CSGO, who knows?), and the only answer clearly misunderstands the technique.

However, I find the lack of people using / documenting this technique very surprising - this is the only reason I doubt it.

It is so simple, easy, comprehensive and provides a number of other useful advantages, it’s hard for me to figure out why everyone does not do this, and SQLi is in the past ???

Answer 2

It seems logical, since all data, even user input that can trigger injections, will be converted. I tried to find ways around it, but I can’t. So far, the biggest flaws I see: 1) From the database view, you can not understand user input. You pull everything out of the database and transform it just to see or manipulate it. 2) No matter what input you provide, the string length will be doubled after conversion.