Storing mcrypt Variables In A Database

[Laxidasical]

I created a function that both encrypts and decrypts an array holding credit card data using mcrypt, depending on what I pass.  For example:

 

// SET CREDIT CARD ARRAY

$cc['type'] = 'Visa';
$cc['name'] = 'John Doe';
$cc['number'] = '1111111111111111';
$cc['month'] = '03';
$cc['year'] = '2008';

// RETURN AN ARRAY CONTAINING THE SAME KEYS WITH ENCRYPTED VALUES

$ecc = cc_crypt($cc, 'e');

/*
$cc['type'] = 'lúí²/Ó—5ùï¨Òè±'óKø@ È¤ãÖ^';
$cc['name'] = 'K5È8ø:3~óY"«FU¯s7ÏÀèjrE%½á';
$cc['number'] = 'ô­mOQM—³Ýld³Ì‰ŸÊÀ´­ïv5g$c';
$cc['month'] = '-®åF[9•ÐRBÓá²ðãì˜È^èGJJ”B:';
$cc['year'] = '_RaWª£¶Îc€ åss)Y«¹jŒ§ érþj8Ó';
*/

// RETURN AN ARRAY CONTAINING THE SAME KEYS WITH DECRYPTED VALUES

$dcc = cc_crypt($ecc, 'd');

/*
$cc['type'] = 'Visa';
$cc['name'] = 'John Doe';
$cc['number'] = '1111111111111111';
$cc['month'] = '03';
$cc['year'] = '2008';
*/

 

It works great within PHP, but when I store/retrieve the data using a my MySQL database it breaks!  I know it has something to do with how the data is being stored.  I've tried storing it in MySQL field types TEXT and BLOB with no luck.  Every so often I noticed I'd get a MySQL error when inserting.  I tried addslashes/stripslashes.  This solved my insertion problem, but breaks encryption/decryption (both within a script and with the database).

 

Has anyone here successfully stored mcrypt variables in their database?  If so, how?  ???

 

 

[Cep]

Are you storing the array or the string values seperately? The issue judging from your code is more likely because you have not used charsets correctly.

[Laxidasical]

I'm storing the string values separately.  I hadn't thought of storing the entire array in the database (like session data), but I'm going to try that now!

 

In case anyone is wondering...when the array is encrypted, the iv is also included in the array containing the encrypted data.  I forgot that in my original post:

 

$cc['iv'] = 'lúí²/Ó—5ùï¨Òè±'óKø@ ȤãÖ^';

 

[Cep]

Well I only ask because if you don't need to store the fields separately you could serialize the array and then encrypt that single string.

 

However that is a slightly different issue. Your problem is most likely that your database charset is not set to handle the characters produced by the mcrypt functions. This is why it breaks.

 

My next question is, why are you using mcrypt which is a two way encryption, as opposed to a hashing algorithm like sha1 which is a one way encryption method? (Which is technically more secure).

[Laxidasical]

Thanks, I hadn't thought of that!  You have any idea what charset I should use?  The script has to bill users monthly.  I'm order to do so it needs the unencrypted credit card info.

[Cep]

Ok that would make sense then if you need to decrypt the data at some stage.

 

"UTF-8 General" will pretty much cover all the characters you can think of.

 

You need to store the data in a text field or blob, this is because varchar removes spaces at the end of strings which will break your encryption because this is one of the characters mcrypt may use at the end of the encrypted string.

 

Don't use any string manipulation functions like you mention above, it alters the encrypted string.

 

If the problem occurs because mcrypt is producing a string that contains SQL breakable code, you may need to use prepared statements instead of standard SQL queries.

[Laxidasical]

I was always storing it as either a TEXT or a BLOB, I learned varchar strips spaces at the end of a string the hard way about a year ago.

 

I tried UTF-8 as you suggested, no such luck.  I also tried several others just for good measure, still wasn't working.  Then I decided to have my mcrypt script base64_encode the final output (and of course base64_decode encrypted data that comes in).  Bingo!  Well, almost...

 

Everything encrypts/decrypts correctly within my script (local variables only).  The "month" ALWAYS messes up when storing data in MySQL though.  The only thing different about month from the other data is that it is less than three characters.  I thought maybe there is a minimum string length limitation when using mcrypt on the data at first, but it works fine when I'm not storing it in MySQL. 

 

Off to do some more testing...

[Laxidasical]

Not sure why, but when I stopped the MySQL service and restarted it the broken month variable problem went away.  Very weird...but I'm not about to complain!  :-X