[SOLVED] Help! AES_DECRYPT WON'T decrypt anything!

[jackwh]

I didn't know if this should be in the PHP or MySQL forum... if I'm wrong, sorry!

 

Anyhoo. Basically I'm a step further in my web application that some of you have helped me with in another thread.

 

I've been storing user's registration data in the database like this:

 

INSERT INTO user (firstname, lastname, country, email, password, registerdate, dbversion, userversion, membertype) VALUES ('$firstname', '$lastname', '$country', '$email', AES_ENCRYPT('$password1','$key_str'), CURDATE(), '$dbversion', '$userversion', '$membertype')

 

The important part is in bold, AES_ENCRYPT. Looking in my databases, regular passwords appear like this: 5ù#šØ©W!1Ó^™4

 

(As you'd hope them too).

 

Now, I'm trying to get a login form to work. So obviously it has to check the email is valid and the password matches too.

 

My code looks like this:

 

<?php

include("mysql/connecttodb.php");
include("mysql/resources/keystr.php");						// Define the AES_DECRYPT Keystring

$email = $_POST['email'];									// Gets email from form
$password = $_POST['password'];								// Gets password from form

//Select from the database a matching user
$sql = "SELECT email FROM user WHERE email = '$email' and password = AES_DECRYPT('$password','$key_str') ";	

$result = mysqli_query($cxn,$sql);

// Check the query was valid
if(!$result)
{						// If result is baaaad...
$err=mysqli_error($cxn);		// Print the error
print $err;
exit();					// Then exit. Ha.
}

// Check the username exists
if (mysqli_affected_rows($cxn )== 0)				// If nothing matches...
{
print "Email/Password error. Please try again.";	// Print error...
exit();										// and exit.
}
else
{
print "Login successful. Redirecting you to member's area...";

//proceed to perform website’s functionality – e.g. present information to the user

}

// Database connected. 

?>

 

It decrypts the password with exactly the same passkey as it encrypts it with. It is defined in the file "mysql/resources/keystr.php"; that is include()ed near the beginning of the script.

 

The problem I'm getting each time is "Email/Password error. Please try again.".

 

I believe it isn't decrypting the password correctly.

 

A little help anyone?

 

Thank you for your assistance.

[gizmola]

Select the AES_DECRYPT(password, ...) where the email = $email. 

 

Compare your now unencrypted password to the one in the post.  If === then the user logs in.

 

Word of advice, you need to use mysql_real_escape_string() on your input.

 

 

[jackwh]

I'm sorry, but I don't really get you? Could you explain a little more clearly? 

 

And mysql_real_escape_string() is coming next, this is simply a prototype still.

[Brian W]

The method I use for this may be easier.

MD5 the password when they enter it the first time, store the hashed value of their password. When they go to sign in and type in the password, MD5 their input and use it in the SELECT statement.

Example (dirty):

<?php
$Pass = "test";
$hashed = md5($Pass);
//INSERT INTO users ...... , '$hashed');

//Now for when they signing

$_POST['password'] = "test";
$hashed = md5($_POST['password']);
//SELECT FROM users WHERE username='$username' AND password='$hashed'
if(mysql_num_rows($query) == 1){
//signin
} else {
//fail
}
?>

[Mark Baker]

mysql_num_rows() not mysql_affected_rows when you're selecting data.

 

[Brian W]

mysql_num_rows() not mysql_affected_rows when you're selecting data.

Good catch

[gizmola]

I'm sorry, but I don't really get you? Could you explain a little more clearly? 

 

And mysql_real_escape_string() is coming next, this is simply a prototype still.

 

You are storing the password encrypted.  So select the row for that user matching the username/email address associated with the account, and in the process decrypt the stored password.  This gives you the plaintext password, which the user is supplying in the form when they login.  If these match then the user has provided the right email/password pair.

[jackwh]

OK guys, problem solved! 

 

Thank you so much for your help. I replaced mysqli_affected_rows() with mysqli_num_rows, and then changed the methodology a little bit. I changed the code from this:

 

<?php 
$sql = "SELECT email FROM user WHERE email = '$email' and password = AES_DECRYPT('$password','$key_str') ";
?>

 

To this:

 

<?php 
$sql = "SELECT email FROM user WHERE email = '$email' and password = AES_ENCRYPT('$password','$key_str') ";
?>

 

So, basically instead of decrypting it to see if it matches the user's inputted password, I swapped it round to see if the user's inputted password, when encrypted, matches the one in the database.

 

Huzzah!

 

Thanks for being such great forum members!