#1664 hex/unhex revisited
Hex/Unhex revisited. There is already a ticket for the inclusion of hex/unhex on incoming information. This request is to generate the unhex command on existing data when an export is performed. This is NOT a request for just BLOB information to be unhexed. It is a request for an option so ALL of the data be output as unhex function calls. Such as this:
INSERT INTO <TABLE> VALUES (unhex('3230'), ...)
Why? As talked about on the Wikipedia SQL Injection web page (http://en.wikipedia.org/wiki/SQL_injection) using hexadecimal conversion ensures no SQL injection can occur on data coming in to the database. Since we use this feature extensively to ensure hackers can not break in to the database via SQL - we would like to ensure the extracted data from the database can't get used to break in to our databases. (A different way to put this is that the hexadecimal conversion only ensures that the data is treated AS DATA. So in a character field the SQL injection still gets stored but not executed because of the unhex() function and how MySQL handles data that is passed through the unhex() function.) As such, it would be nice if phpMyAdmin could automatically produce the unhex( bin2hex() ) commands.
Here is an example of what I am talking about:
INSERT INTO
zipcodes(zip_id,zipcode,types,cities,counties,area_codes,date_added,state,country,state_ab,country_ab,latitude,longitude) VALUES (unhex('313332373135'), unhex('27333530303427'), unhex('275374616e6461726427'), unhex('274d6f6f6479'), unhex('41636d617227'), unhex('27537420436c61697220436f756e747927'), unhex('2732303527'), unhex('27323031342d30382d30362031313a30383a343827'), unhex('27416c6162616d6127'), unhex('27556e6974656420537461746573206f6620416d657269636127'), unhex('27414c27'), unhex('27555327'), unhex('4e554c4c'), unhex('4e55') );This is a record from our zipcode table. The zipcodes were downloaded from SourceForge's zipcode database and combined with other free zipcode databases to create the above record layout. The problems come when various characters get in to the database. Such as single quotes, control characters, and other things. By converting everything to hex and using the unhex() function found in MySQL - any kind of problems which might occur are eliminated.
I am sure the question comes to mind "Why not use mysql_escape_string()?" or any of the other functions to eliminate problems in processing data to ensure it is ok. The answer is - bin2hex() and unhex() are a lot faster because they are not testing each character to see if it is one of the bad characters to have. As the code on Wikipedia shows - all that is done is to obtain the ordinal value of each character and then split it via mathematical means. A maximum of three steps. Whereas using the escape string function it would be one test for each of the characters it is looking for. Further, should someone figure out, in the future, some new combination - the unhex() function would have already caught it.