MYSQL AES_DECRYPT错误

Question

I have some data that I have stored encrypted on one of my db's. These are personal details I want to have stored encrypted on the db. The encryption part (AES_ENCRYPT(fname, $key) works fine - it stores the data in an encrypted form, just the way I want them. The problem is when the users return to the form they filled in their personal details to make adjustments to those details. I want the data they entered originally to appear in the respective fields for them to be able to amend them. The decryption returns empty values or even nothing at all. This is my code:

$key = pack('H*', "bcb04b7e103a0cd8b54763051cef08bc55abe029fdebae5e1d417e2ffb2a00a3");

$sql = "SELECT username, AES_DECRYPT(AES_DECRYPT (fname, '$key'),'$key'), 
AES_DECRYPT(AES_DECRYPT (lname, '$key'),'$key'), AES_DECRYPT(AES_DECRYPT 
(address1, '$key'),'$key'), AES_DECRYPT(AES_DECRYPT (address2, '$key'),'$key'), 
AES_DECRYPT(AES_DECRYPT (zip, '$key'),'$key'), AES_DECRYPT(AES_DECRYPT (city, 
'$key'),'$key'), AES_DECRYPT(AES_DECRYPT (country, '$key'),'$key'), 
AES_DECRYPT(AES_DECRYPT (phone, '$key'),'$key') FROM persdetails WHERE username 
= '$_username'";

$query = mysqli_query($db_conxpd, $sql);
$numrows = mysqli_num_rows($query);
while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)) {
$_u = $row['username'];
$_fname = $row['fname'];
$_lname = $row['lname'];
$_addr1 = $row['address1'];
$_addr2 = $row['address2'];
$_zippo = $row['zip'];
$_citys = $row['city'];
$_cntry = $row['country'];
$_phone = $row['phone'];

}

This does not return any result as far as the decryption is concerned. I know the query works as such as it does return the username if I echo all the fields. Also it returns the number of rows. There's just nothing coming back whereas the encrypted fields are concerned. What am I doing wrong here?

NB: the key is the same I used to encrypt the data.

Let me know if you need more information.

Thanks a million!

Answer 1

I fixed it - it was an issue with the input: I had, after the input php had received the data from the form, the data sanitized with mysqli_real_escape_string and with preg_replace, so for whatever reason either nothing was going into the db in the first place, or it had somehow other characters added to it. Rather strange as I restricted the input to only numbers and letters, but that's a problem of a different kind :) Anyway, after adding "cast" to the select query, it worked like a charm!

cast(aes_decrypt(fname, '1234567') as char) as fname, etc... (changed the key) shows me all the data :)

Thank you very much all of you for your efforts!