mysql aes_encrypt and aes_decrypt

Question

I'm trying to make a login system which encrypts and decrypt passwords in database(for my project). I can use aes_encrypt to encrypt password and store them in database.However, when I decrypt them later to find matching passwords for login, they don't work. It's like aes_decrypt is skipped and not ran because I have accounts with plaintext password stored in database and I can login with them but for accounts with encrypted passwords they don't work. I'm using Xampp with phpmyadmin for database.

Signup file
<?php

if(isset($_POST['signup']))
{
mysql_connect("localhost","root","");
mysql_select_db("faceback");

$Email=$_POST['email'];

$que1=mysql_query("select * from users where Email='$Email'");
$count1=mysql_num_rows($que1);

if($count1>0)
{
echo "<script>
alert('There is an existing account associated with this email.');
</script>";
}
else
{
$Name=$_POST['first_name'].' '.$_POST['last_name'];
$Password=$_POST['password'];
$Gender=$_POST['sex'];
$Birthday_Date=$_POST['day'].'-'.$_POST['month'].'-'.$_POST['year'];
$FB_Join_Date=$_POST['fb_join_time'];

$day=intval($_POST['day']);
$month=intval($_POST['month']);
$year=intval($_POST['year']);
if(checkdate($month,$day,$year))
{
$que2=mysql_query("insert into 
users(Name,Email,Password,Gender,Birthday_Date,FB_Join_Date) 
values('$Name','$Email',AES_ENCRYPT('$Password','897sdn9j98u98jk'),
'$Gender','$Birthday_Date','$FB_Join_Date')");

session_start();
$_SESSION['tempfbuser']=$Email;
}

Login file
<?php


if(isset($_POST['Login']))
{
mysql_connect("localhost","root","");
mysql_select_db("faceback");

$user=$_POST['username'];
$pass=$_POST['password'];



$que1=mysql_query("select Email,AES_DECRYPT(Password,'897sdn9j98u98jk') from 
users where Email='$user' and Password='$pass'");
$count1=mysql_num_rows($que1);

if($count1>0)
{
session_start();
$_SESSION['tempfbuser']=$user;
}

Your code is vulnerable to SQL injection attacks. You should use [mysqli](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) or [PDO](http://php.net/manual/en/pdo.prepared-statements.php) prepared statements as described in [this post](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — Alex Howansky, Apr 07 '17 at 15:02
The `mysql_*` functions are deprecated as of PHP v5.5 and have been removed as of v7.0. They should not be used for new code and should be swapped out for [mysqli](http://php.net/manual/en/book.mysqli.php) or [PDO](http://php.net/manual/en/book.pdo.php) equivalents as soon as possible. — Alex Howansky, Apr 07 '17 at 15:02
Don't use MySQL's AES encryption for passwords. Instead use PHP's [`password_hash()`](http://php.net/manual/en/function.password-hash.php) and [`password_verify()`](http://php.net/manual/en/function.password-verify.php) functions. — Alex Howansky, Apr 07 '17 at 15:04
**Do NOT play with fire if you don't want to get burned**. You **will** get hacked if you intend on using this code online. If this scared you; great, because I have done my job well. — Funk Forty Niner, Apr 07 '17 at 15:06
It should **not** be possible to decrypt passwords ... ever. — CD001, Apr 07 '17 at 15:12
I agree with everyone else here - you never store passwords in a way that lets you get its plaintext value. If you encrypt those passwords, what exactly did you achieve? Why do you encrypt it if you are not transmitting it over insecure network? Simply hash it using `password_hash` like people here suggested already. — Mjh, Apr 07 '17 at 15:16

score 0 · Answer 1 · edited May 23 '17 at 12:10

0

You save the password into your database using AES_ENCRYPT('$Password','897sdn9j98u98jk' (in your query).

But you try to retrieve the plain-text password from the database using

$pass=$_POST['password'];
// code omitted
$que1=mysql_query("select Email,AES_DECRYPT(Password,'897sdn9j98u98jk') from 
users where Email='$user' and Password='$pass'");
//                            ^^^^^^^^^^^^^^^^^

That is the reason why you can login on accounts that have a plaintext password stored in your database.

To fix that you have to search for the encrypted password in your WHERE clause:

(...) WHERE (...) AND Password = AES_DECRYPT($pass, '897sdn9j98u98jk')

However:

You also should not use encryption to store your passwords. You should hash the password using password_hash() and verify them using password_verify(). For more information on why you need to hash your password instead of encrypting them read Fundamental difference between Hashing and Encryption algorithms.

edited May 23 '17 at 12:10

Community

1
1

answered Apr 07 '17 at 15:06

Tom Udding

2,264
3
20
30

I tried using $Password but they don't work either. I used encryption because I need to provide password in case a user forgot his password and needs to recover it. – mr.newbie Apr 07 '17 at 15:08
@mr.newbie You should **never** send passwords in the clear! You should offer them a "reset password" function, which sends a one-time reset link to their account. – Tom Udding Apr 07 '17 at 15:13
$que1=mysql_query("select Email,AES_DECRYPT(Password,'897sdn9j98u98jk') from users where Email='$user' and Password='$pass'"); I thought Password refers the decrypted password and $pass the password entered by user? – mr.newbie Apr 07 '17 at 15:15
Yeah, but you store the encrypted password! Than you should check if the database contains (WHERE clause) that encrypted password instead of the plaintext version. You are trying to retrieve the decrypted password. – Tom Udding Apr 07 '17 at 15:16
@mr.newbie I added some new code that should help you, BUT you should hash your passwords instead of encrypting them. – Tom Udding Apr 07 '17 at 15:22

mysql aes_encrypt and aes_decrypt

1 Answers1

However: