Page 1 of 1

AES_ENCRYPT / DECRYPT HELP

Posted: Mon Mar 09, 2009 3:39 pm
by WhiteWolf
Here is my issue -
I have a database with maybe 60 fields in it. Only 3 of the fields need to be encypted in my database (MySQL), one of which is the users Social Security Number.
I have read that if you want to use AES_ENCRYPT, then the field must be binary. I have done this, and the insert statement reads as follows:

Code: Select all

     
     INSERT INTO $usertable values( //...about 25 fields here,  AES_ENCRYPT('$SSN', 'text_key'), //... about 30 fields here);
That seems to work fine - I don't get any errors and there is something written to the database. The problem occurs when I try to use AES_DECRYPT. Because it's not a PHP function, I guess you have to use it in the SELECT statement. But I am unsure how to do that - I have seen it done as follows:

Code: Select all

SELECT AES_DECRYPT( SSNbr, 'text_key' ) AS decrypted FROM $usertable.
But how do I incorporate the above into my overall select statement, which right now is a simple

Code: Select all

"SELECT * FROM $table";
Like I said, I don't need all 60 fields decrypted, only a few.

Thanks

Re: AES_ENCRYPT / DECRYPT HELP

Posted: Tue Mar 17, 2009 8:51 pm
by WhiteWolf
ok, since nobody saw fit to provide a response to the White Wolf, he has done the requisite research and solved the dilemma himself. Damn quoatation marks! That's what caused my frustration! In the AES_DECRYPT statement, when you put the name of your database field, you have to use the slanted quote: ` , not the straight quote: '
So it can't be AES_DECRYPT('subjectDateOfBirth', 'secretKey'), it must be
AES_DECRYPT(`subjectDateOfBirth`, 'secretKey')

For the edification of the his fellow programmers, here is the solution:

use this to encrypt the fields as they go into the database:

"INSERT INTO $usertable values ( AES_ENCRYPT('$formDateOfBirth','secretkey') , AES_ENCRYPT('$formsocialSecurityNumber','secretKey') )";

** Now, make sure you've made the field type BLOB in the database - so in my database, birthday and SocSecNbr are both BLOBS**

Here's the relevant part of the query to retrieve them from your database:

$query = "SELECT AES_DECRYPT(`subjectDateOfBirth`, 'secretKey') as decryptedBirthday , AES_DECRYPT(`subjcetSSN`, 'secretKey') as decryptedSubjectSSN from Table";

C'est tout!