PHP & MySQL: AES_Encrypt / AES_Decrypt

[DjmUK]

Alrighty then. If I were to use the following commands they work just fine:

AES_Encrypt (PHP):

$un = david;

$pw = pass;

mysql_query(INSERT into tablename (un,pw) values ('$un',aes_encrypt('$pw','key'))",$db);

AES_Encrypt (Command Prompt):

SELECT un, aes_decrypt(pw,'key') from tablename where id=1;

However. I'm trying to write a login page based upon the encrypted data via PHP (send the data via a form and process it). I had it working perfectly - but as soon as encryption is entered things don't go my way. Here's a sample of my efforts which aren't looking too good (I am extracting from a form, but for simplicity I'm using static data).

<?php

$un = david;
$pw = pass;

$result = mysql_query("SELECT un, aes_decrypt(pw,'key') FROM table_login where un='$un' and pw='$pw' ",$db);

$array_r = mysql_fetch_array($result);

echo "User". $array_r["un"] ."<br />";
echo "Pass". $array_r["pw"];
?>

I searched google and other sites with no luck, but from what I'm told - the aes_decrypt function has to remain in the SELECT clause because it's a MySQL function and not a PHP function.

Please help me and thanks in advance.

[Michael_C]

Your WHERE clause is comparing the unencrypted password(submitted by the user) to the encrypted password in the database, it might be easier if you use PHP to do the encryption.

[DjmUK]

Your WHERE clause is comparing the unencrypted password(submitted by the user) to the encrypted password in the database, it might be easier if you use PHP to do the encryption.

585535073[/snapback]

Exactly what I meant - what I cannot figure out is how to do this in PHP :(

[Michael_C]

I would suggest mcrypt but it doesn't seem to support AES, if you are willing to use a different algorithm it might be useful.

[DjmUK]

I would suggest mcrypt but it doesn't seem to support AES, if you are willing to use a different algorithm it might be useful.

585539169[/snapback]

Thanks, I'm looking into it now.

[GatorV]

Why don't you try to do 2 querys?

<?php

$un = david;
$pw = pass;

$result = mysql_query("SELECT aes_encrypt(pw,'key')");
$encrypted = mysql_fetch_array($result));
$result = mysql_query("SELECT un, aes_decrypt(pw,'key') as pw FROM table_login where un='$un' and pw='$encrypted[0]' ",$db);

$array_r = mysql_fetch_array($result);

echo "User". $array_r["un"] ."<br />";
echo "Pass". $array_r["pw"];
?>

:unsure:

[DjmUK]

Why don't you try to do 2 querys?

<?php
$un = david;
$pw = pass;

$result = mysql_query("SELECT aes_encrypt(pw,'key')");
$encrypted = mysql_fetch_array($result));
$result = mysql_query("SELECT un, aes_decrypt(pw,'key') as pw FROM table_login where un='$un' and pw='$encrypted[0]' ",$db);

$array_r = mysql_fetch_array($result);

echo "User". $array_r["un"] ."<br />";
echo "Pass". $array_r["pw"];
?>

:unsure:

585544078[/snapback]

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\site\login_check.php on line 4

[GatorV]

Well the code was wrong, but try this:

<?php
$un = "david";
$pw = "pass";

$result = mysql_query("SELECT aes_encrypt('$pw','key')");
$encrypted = mysql_fetch_array($result));
$result = mysql_query("SELECT un, aes_decrypt(pw,'key') as pw FROM table_login where un='$un' and pw='$encrypted[0]' ",$db);

$array_r = mysql_fetch_array($result);

echo "User". $array_r["un"] ."<br />";
echo "Pass". $array_r["pw"];
?>

:cool:

[HeyRatFans]

If you must use AES encryption do the comparision in PHP, i.e. have the query return the decrypted password and then do a simple == comparision in PHP.

<?php

$un = "david";
$pw = "pass";

$result = mysql_query("SELECT un, aes_decrypt(pw, 'key') as pw FROM table_login where un = '$un'");
$array_r = mysql_fetch_array($result);

if ($array_r['pw'] == $pw) {

   // Password is okay

   echo "User", $array_r["un"] ."<br />";
   echo "Pass", $array_r["pw"];

}else {

   // password is invalid!

   echo "Boo! Hiss!";
}

?>

[Cailin]

That still won't work, you have an error on line 7:

$encrypted = mysql_fetch_array($result));

Extra end bracket. Get rid of the second bracket.

[DjmUK]

I removed the bracket...I think I'm almost there - I found out that the data field type has to be BLOB (for some reason)...but I'm close.

[DjmUK]

FINALLY..! (1 query aswell)

$un = $_POST["un"];
	$pw = $_POST["pw"];

	$result = mysql_query("SELECT un, aes_decrypt(pw,'mykey') from tb_login where un='$un' ",$db);
	$encrypted = mysql_fetch_array($result);

if ($encrypted[1]==$pw)
{
echo "yes";
}
else
{
echo "no";
}

I had to encrypt the password into the BLOB field first. Thanks for all the help guys.