CODEWITHSUNDEEP
phpaes
iaintunderstand
iaintunderstand

Reputation: 155

AES en-and decrypt PHP MySQL

Well, I definitively rightly chose my nick, as I am baffled over this:

First I encrypt it, and I get an encrypted row in my table. But when I try to decrypt it, I get zero rows as the result set.

Encrypting:

INSERT INTO accesobases (company, username,email) VALUES
 ('hola',
AES_ENCRYPT('pedro','capullo'),
AES_ENCRYPT('myemail',' capullo')
);

and when I run the decryption query, 0 rows show up.

SELECT company,
AES_DECRYPT('username', 'capullo'),
AES_DECRYPT('email', 'capullo')
from acceso
where company = 'hola';

Note that I did not encrypt company, but I will definitively need to, but I wanted to see where the error it could be. That is, not even departing from a non encrypted word (company = hola) I get any results. So, it will be even worse when I try to do as:

where AES_DECRYPT ('company', 'capullo') = ' " . $company . " '

which is how it would look like on my php pages. The above block was being run on the MySQL database itself.

So, the questions are two:

  1. What is wrong with not showing any data
  2. Whether the version of ..."$company.." will work.

However, if I say: 

SELECT * from acceso 
WHERE company = 'hola';

then, it does show the row with encrypted words

So, it is as if it did not have the time to decrypt the row before showing it and then shows nothing

Upvotes: 0

Views: 707

Answers (2)

Marcus Adams
Marcus Adams

Reputation: 53870

These are column names, so don't put them in quotes:

AES_DECRYPT('username', 'capullo'),
AES_DECRYPT('email', 'capullo')

Do this instead:

AES_DECRYPT(username, 'capullo') AS username,
AES_DECRYPT(email, 'capullo') AS email

Also, since the output of AES_ENCRYPT is binary, ensure your columns are binary.

It also looks like you might have two different tables, 'accesobases' and 'acceso', so ensure you're using the correct table.

Your statement:

where AES_DECRYPT (company, 'capullo') = ' " . $company . " '

Seems fine, as long as you remove the quotes from the column name here too. It will be slow since it won't be able to use an index. Instead, you should do this:

where company = AES_ENCRYPT('$company', 'capullo')

Upvotes: 1

user149341
user149341

Reputation: 

SELECT company,
AES_DECRYPT('username', 'capullo'),
AES_DECRYPT('email', 'capullo')
from acceso
where company = 'hola';

This won't work because you're telling MySQL to decrypt the constant string "username", not the value of the username column. Remove the quotes on 'username' and 'email'.

where AES_DECRYPT ('company', 'capullo') = ' " . $company . " '

Same issue here. However, it'd be more efficient to do this the other way around in order to leverage indices:

where company = AES_ENCRYPT(?, 'capullo')

That being said, using AES_ENCRYPT on the MySQL server side is NOT SECURE. The encryption key is passed to the server with each query, and, as such, will appear in the MySQL processlist, as well as potentially in server query, slow, and/or error logs; if you are not using SSL for your MySQL connection, they will be passed over the network in cleartext, as will the decrypted data.

Upvotes: 2

Related Questions