Mcrypt Issue

[ziycon]

I'm encrypting data and when i try to insert it into the mySQL database it wont enter it and throws an error, i think its to do with these strange characters, they are a little box with four characters in it like two zeros on top and underneath there is like a zero and a nine, any ideas??

[Atli]

Hi.

How exactly does the INSERT query look like?
What error are you getting?

As a general rule, you should always run all data that is to be a part of a MySQL query through the mysql_real_esca pe_string function (or it's MySQLI equivalent) before adding it to the query.
That will make sure the data will not mess up the query with weird characters or SQL Injection.

[ziycon]

The query look like this:

$query = mysql_query("INSERT INTO reports VALUES (NULL,'".secure_data($_REQUEST['name'],"encrypt")."')")

This is the secure_data function:

function secure_data($input,$type) {
 $iv_size = mcrypt_get_iv_size(MCRYPT_RIJNDAEL_256, MCRYPT_MODE_ECB);
 $iv = mcrypt_create_iv($iv_size, MCRYPT_RAND);
 $key = "an#$298d&"; 
 $data = "";
 if($type=="encrypt")
 {
    $data = mcrypt_encrypt(MCRYPT_RIJNDAEL_256, $key, $input, $iv);
 }
 else
 {
    $data = mcrypt_decrypt(MCRYPT_RIJNDAEL_256, $key, $input, $iv);
 }
return data;
}

[Atli]

A couple of thoughts on that code...

First, as I suggested earlier, your should ALWAYS use the mysql_real_esca pe_string function on ALL data you plan on using in a MySQL query.
This will probably fix the problem you were posting about now, although I can not be sure without seeing the actual error.

Also, when writing a INSERT query, you should always specify the columns you want to use, rather then leave it up to MySQL.
This will ensure that the order in which you add the values is correct, it will allow you to add columns to the table without breaking the query, and more importantly; it will allow you to leave out columns that don't need to be filled.
For example:

/* Assuming `UserTable` is a table with an AUTO_INCREMENT
 * ID field and a VarChar Name field */
 
/* Rather than doing: */
INSERT INTO `UserTable` VALUES (NULL, 'Name');
/* Do: */
INSERT INTO `UserTable`(`Name`) VALUES ('Name'); 
 

There in the first query, a NULL value would have to be passed to the AUTO_INCREMENT ID field, which I simply left out in the second query, removing the need to pass the NULL value.

Third, and least important, your secure_data function takes a $type value as it's second parameter. You use this as a string, passing "encrypt" when you want the function to encrypt rather than decrypt.
I would suggest changing this to a boolean and giving it a default value.
It should both slightly decrease the memory usage and execution time of your program (*slightly* being the keyword there), and make the function easier to use.
Consider this:

function secure_data($input,$encrypt=false) {
  if($encrypt) {
    // encrypt
  }
  else {
    // decrypt
  }
}
 
// Then you could use it like so:
$encrypted = secure_data('data', true);
$decrypted = secure_data($encrypted);
 

Hope this helps.

[ziycon]

Thanks for your reply, i understand about the INSERT statement, i've spent time going over the fields being entered and they are never going to change, every field is always going to be entered hence why i left out the field names the data was going into.
With the html entities thing, as the data is encrypted will this effect the data so when its called from the DB again and decrypted will it decrypt properly??
I don't have access to the error right now but i'll try and post it as soon as possible.

Thanks again.

[Atli]

Thanks for your reply, i understand about the INSERT statement, i've spent time going over the fields being entered and they are never going to change, every field is always going to be entered hence why i left out the field names the data was going into.

Ok, I see. Personally I would always write out the names, even if they were all being used... but that's just me :)

With the html entities thing, as the data is encrypted will this effect the data so when its called from the DB again and decrypted will it decrypt properly??

The mysql_real_esca pe_string function has nothing to do with HTML entities. It simply makes sure that no part of this string will cause an error in the SQL query, escaping anything that would do so.
It won't affect the data you are using in any way. The data stored and returned by MySQL when you select it will be exactly the same as the data before it was escaped.

[ziycon]

Heres the error i'm getting:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ºÏùØõT Þ„ý»rãwÎÞ¼ôôžO äÿ¶Lš²8','šþ� ?J³ü8 ‡‹áÐI-‚MŠ'¼ŒÔïy¾úÍ„Â ','john.doe@hot ' at line 1

[ziycon]

The mysql_real_esca pe_string function solved my problem, thanks again!