Using AES_ENCRYPT AND AES_DECRYPT I don't get returning data

Question

I'm trying to use AES_ENCRYPT AND AES_DECRYPT, I do get a string of random characters in the MYSQL data field but can't return anything with the AES_DECRYPT

My table field for password is setup as VARBINARY, 600 Length.

ADDING INTO THE DATABASE I HAVE:

mysqli_select_db( $connection , $databaseName );
$request = '
    UPDATE person
    SET firstName = AES_ENCRYPT("Fred","key123")
    WHERE ID="34"';
$result = mysqli_query( $connection , $request );

SELECTING FROM THE DATABASE I HAVE:

mysqli_select_db( $connection , $databaseName );
$request = "SELECT AES_DECRYPT(firstName, 'key123') FROM account WHERE ID = 34";    
$result2 = mysqli_query($connection , $request);
$row = mysqli_fetch_array($result2);
$firstName = $row['firstName'];

echo $firstName;

This just returns nothing. Can anyone tell me where I'm going wrong? Thanks!

You should never encrypt your user's passwords. You need to use hashing instead with some strong ones being PBKDF2, bcrypt, scrypt and Argon2. Since hash functions are one-way function, you won't be able to "decrypt" the hashes. In order to authenticate your user, you can run the password through the hash function again in order to compare with the hash that is stored in the database. See more: [How to securely hash passwords?](http://security.stackexchange.com/q/211/45523) — Artjom B., Dec 08 '15 at 11:27
you entered "key123" but trying to retrieve "123". by *"just returns nothing"*, I don't see where you're echoing anything. — Funk Forty Niner, Dec 08 '15 at 12:29
*"ADDING INTO THE DATABASE I HAVE"* - that's not "adding", it's "updating". Adding is "INSERT INTO...". how do you know it even updated successfully? Your question's unclear. — Funk Forty Niner, Dec 08 '15 at 12:31
@ Artjom B The use of password is just an example, I plan on hashing all passwords. @Fred -ii- I have tried changing the salt to both key123 still returns nothing, also I have checked the MYSQL database as can see the data in the field. Anymore ideas? — Fred Perry Munnelly, Dec 08 '15 at 12:51
see if this answer helps http://stackoverflow.com/a/16556522/ — Funk Forty Niner, Dec 08 '15 at 12:57
you'll need to clarify what you mean by *"This just returns nothing"*. Your question doesn't show us if you're echoing anything. check for errors also on your query http://php.net/manual/en/mysqli.error.php — Funk Forty Niner, Dec 08 '15 at 13:03
This doesn't work unless I have to encrypt the data different? $request = "SELECT CAST(AES_DECRYPT(password, 'key123') AS CHAR(50)) FROM account_decrypt WHERE ID = 42"; — Fred Perry Munnelly, Dec 08 '15 at 13:10
I'm comparing $password to the $enteredPassword, this is just an example I know passwords need to be hashed. — Fred Perry Munnelly, Dec 08 '15 at 13:11
I have updated the question code, see if that makes more sense? — Fred Perry Munnelly, Dec 08 '15 at 13:23

score 0 · Answer 1 · answered Dec 08 '15 at 13:10

0

In the update query you use ID as a string and in the SELECT query it is used as an int, try to fix this. You should change the select to WHERE ID = "34"

answered Dec 08 '15 at 13:10

in need of help

1,606
14
27

I have updated the question code to make a little more sense. – Fred Perry Munnelly Dec 08 '15 at 13:18
it still looks to me like the string vs int issue – in need of help Dec 08 '15 at 13:52

score 0 · Answer 2 · answered Apr 02 '20 at 06:56

0

You are working on two different tables. You are updating the 'person' table. Then, you are selecting from the 'account' table.

answered Apr 02 '20 at 06:56

jcag08

1

score 0 · Answer 3 · answered Apr 02 '20 at 07:23

0

Change

$request = "SELECT AES_DECRYPT(firstName, 'key123') FROM account WHERE ID = 34";

to this:

$request = `SELECT AES_DECRYPT(firstName, 'key123') FROM person WHERE ID = "34"`;

answered Apr 02 '20 at 07:23

Ayman Arif

1,456
3
16
40

Muhammad Sohail Arif · Answer 4 · 2015-12-09T11:31:55.080

-1

Try this:

SELECT AES_DECRYPT(AES_ENCRYPT('firstName','mykeystring')FROM account WHERE ID = 34";

Explanation:

I read online that the above sql statement decrypts the encrypted string 'firstName' using mykeystring and returns the original string firstName.

edited Dec 09 '15 at 11:31

answered Dec 08 '15 at 14:21

Muhammad Sohail Arif

158
2
15

1

Do you think that this will fix this the problem? Then you should add an explanation instead of _Try this:_. Otherwise you should add this as comment. – Tobias Liefke Dec 08 '15 at 15:54
Hey Muhammad, I have tried this but just get a error return of: Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in ... – Fred Perry Munnelly Dec 09 '15 at 11:24
Where it says '$result2 = mysqli_query($connection , $request)' replace it with '$result2 = mysqli_query($connection , $request) or die (mysqli_error($connection)); – Muhammad Sohail Arif Dec 09 '15 at 11:29
what is proposed hasn't even been tested (parenthesis missing, "I read online", not working in my environment) – Bob Yoplait Aug 29 '18 at 17:13

Using AES_ENCRYPT AND AES_DECRYPT I don't get returning data

4 Answers4