problem displaying field from db after decrypting

[mottwsc]

I'm using mcrypt to encrypt a field and decrypt it.  That works OK.  But when I insert the encrypted field into a mySQL table, select it, decrypt it and try to display it, I get garbage.

 

What am I missing?  Thanks for any suggestions.

 

Here's the code:

 

echo "The encrypt and decrypt example...<br/>";

// Designate string to be encrypted
$string = "Ed O'Reilly";

// Encryption/decryption key
$key = "arG4thaker2";

// Encryption Algorithm
$cipher_alg = MCRYPT_RIJNDAEL_128;

// Create the initialization vector for added security.
$iv = mcrypt_create_iv(mcrypt_get_iv_size($cipher_alg, 
MCRYPT_MODE_ECB), MCRYPT_RAND);

// Output original string
print "Original string: $string <p>";

// Encrypt $string
$encrypted_string = mcrypt_encrypt($cipher_alg, $key, 
$string, MCRYPT_MODE_CBC, $iv);

// Convert to hexadecimal and output to browser
print "The encrypted string: ".bin2hex($encrypted_string)."<p>";

$decrypted_string = mcrypt_decrypt($cipher_alg, $key, 
$encrypted_string, MCRYPT_MODE_CBC, $iv);

print "The decrypted string: ".rtrim($decrypted_string)."";

echo "<br/>testing db storing & retrieval for the encryption/decryption...<br/>";

# make connection (Game server connections are variable and are done later when needed)
		if (!$cxnAdmin = mysqli_connect($hostAdmin, $userAdmin, $passwordAdmin, $dbnameAdmin))
		{
			die("Could not connect to MySQL server Admin (".mysqli_connect_error().")");
		}

$sql="INSERT INTO testdb.nametable (
			name,
			email
			)
			VALUES (
			'$encrypted_string',
			'test@test.com'
			)
			;
			";

$result = mysqli_query($cxnAdmin,$sql);
			if($result == false)
			{
				echo "<h4>Error on nametable Insert: ".mysqli_error($cxnAdmin)."</h4>";
			}

$sql="SELECT name
FROM testdb.nametable
WHERE email='test@test.com'";

		if (!$result = mysqli_query($cxnAdmin,$sql))
		{
			echo "<h4>Error on nametable Select: ".mysqli_error($cxnAdmin)."</h4>";
		}

		$row = mysqli_fetch_assoc($result);
		extract($row);

// Convert to hexadecimal and output to browser
print "The encrypted string from db: ".bin2hex($name)."<p>";

$decrypted_name = mcrypt_decrypt($cipher_alg, $key, 
$name, MCRYPT_MODE_CBC, $iv);

print "The decrypted string from db: ".rtrim($decrypted_name)."";	

 

and here's the output:

 

The encrypt and decrypt example...

Original string: Ed O'Reilly

 

The encrypted string: 01bc70f5dc5a7ca204cb45bd2eeb0214

 

The decrypted string:Ed O'Reilly

testing db storing & retrieval for the encryption/decryption...

The encrypted string from db: 5938f7fc81961d2efc6db64607baf465

 

The decrypted string from db: <garbage characters I can't display here>

 

[ManiacDan]

You're storing $encrypted_string (binary data) as a string.  Run bin2hex on it before you put it into the database, and then run the reverse when you pull it out.

 

-Dan

[mottwsc]

Thanks for the direction, Dan.  I'm seeing some errors when trying to convert it back from hex using pack, such as:

Warning: pack(): Type H: illegal hex digit Y (plus errors on the other characters)...

 

Here's what I modified:

...
$hexString = bin2hex($encrypted_string);

$sql="INSERT INTO testdb.nametable (
			name,
			email
			)
			VALUES (
			'$hexString',
			'test@test.com'
			)
			;
			";
...
// Convert to hexadecimal and output to browser
print "The encrypted string from db: ".bin2hex($name)."<p>";

$binString = pack("H*",$name);
$decrypted_name = mcrypt_decrypt($cipher_alg, $key, 
$$binString, MCRYPT_MODE_CBC, $iv);

print "The decrypted string from db: ".rtrim($decrypted_name)."";	
...

[ManiacDan]

There's no exact opposite of bin2hex, use base64_encode and base64_decode instead.

 

-Dan

[mottwsc]

wish this were easier  :-\

 

I've changed out the bin2hex & pack for base64_encode & base64_decode, but have strange characters when displaying the decrypted version.  Do I have some steps wrong?

 

$encodedString = base64_encode($encrypted_string);

$sql="INSERT INTO testdb.nametable (
			name,
			email
			)
			VALUES (
			'$encodedString',
			'test@test.com'
			)
			;
			";

$result = mysqli_query($cxnAdmin,$sql);
			if($result == false)
			{
				echo "<h4>Error on nametable Insert: ".mysqli_error($cxnAdmin)."</h4>";
			}

$sql="SELECT name
FROM testdb.nametable
WHERE email='test@test.com'";

		if (!$result = mysqli_query($cxnAdmin,$sql))
		{
			echo "<h4>Error on nametable Select: ".mysqli_error($cxnAdmin)."</h4>";
		}

		$row = mysqli_fetch_assoc($result);
		extract($row);

// Convert to hexadecimal and output to browser
print "The encrypted string from db: ".bin2hex($name)."<p>";

$decodedString = base64_decode($name);
$decrypted_name = mcrypt_decrypt($cipher_alg, $key, 
$decodedString, MCRYPT_MODE_CBC, $iv);

print "The decrypted string from db: ".rtrim($decrypted_name)."";

 

Output:

 

Original string: Ed O'Reilly

 

The encrypted string: 1f8003be3507493cbc0da42805cfcb3b

 

The decrypted string: Ed O'Reilly

testing db storing & retrieval for the encryption/decryption...

The encrypted string from db: 5938f7fc81961d2efc6db64607baf465

 

The decrypted string from db: <garbage characters>

 

 

 

[PFMaBiSmAd]

If you look directly in your database table, you will find that each test run of your script is inserting a new row. This is the reason why your The encrypted string: and The encrypted string from db: values don't match. When you are retrieving the value, you are getting the first row from your table and since that encrypted value was generated using a different $iv than the current $iv being used in your script, it decrypts to a nonsense string.

 

You would need to insure that your email column value is unique and replace any existing name value on each test run and if you are doing this for a real application, you will need to store the random $iv that was generated in the row so that you can decrypt the value (or you will need to generate a fixed $iv value and use that everywhere.)

 

You can directly insert the $encrypted_string value into a BLOB field and you would need to use mysqli_real_escape_string on it so that the special SQL characters that will eventually get generated in the value won't break the sql syntax and produce a sql error.

[mottwsc]

OK - that works.  Thanks to both of you.

[ManiacDan]

Make a primary key for the email and use

INSERT INTO `table` (name, email) VALUES ('$name', '$email') ON DUPLICATE KEY UPDATE name = $name;

Obviously that needs to be secured and cleaned up, but it will allow you to run one query instead of two.

 

I agree with the BLOB comment, normally I would just encode it to be varchar compatible, but blob is technically faster and more correct.

 

-Dan