How to encrypt/decrypt password using MysqliDB

Question

Is there a way to insert/update in database using MysqliDB and AES_ENCRYPT?

$data = Array("Password" => "AES_ENCRYPT('" . $varToEncrypt . "', 'encryptKey')");
        $db->where("Username", "admin")
           ->update('user', $data);

with this method, I get this in my query :

UPDATE user SET `Password` = 'AES_ENCRYPT('000000', \'blablabla\')' WHERE Username = 'admin';

it looks like when I place the value "AES_ENCRYPT..." in the array, it takes as a string...

Or I need to use $db->rawQuery ??

Answer 1

I am not familiar with that library php-mysqlidb but from the look of it I don't think that will work as as Lawrence has pointed out in the comments.

What you may have to do is indeed run this as a raw query: https://github.com/joshcam/PHP-MySQLi-Database-Class#running-raw-sql-queries

Also, I am not sure if you are using the MySQL function correctly, take a look at this: https://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html#function_aes-encrypt

You should avoid storing password as an encrypted string (which can be decrypted, some would say this is is poor security / practice).

Instead; why would you make use the PHP's built in hashing functionality?

Take a look at: http://php.net/manual/en/function.password-hash.php

When a user enters the plain text password, e.g. $my_password = '123456' - hash it like this:

$hashed_password = password_hash($my_password, PASSWORD_DEFAULT);

Then store the value of $hashed_password in the database.

When you want to then validate if the user has entered the correct password, simply take the user input and the stored value in the database and use this function to compare if the hash match:

http://php.net/manual/en/function.password-verify.php

Like this:

$login_password = '123456';
$db_hashed_pass = '$2y$10$.vG....'; // this value is loaded from db for that user

if (password_verify($login_password, $db_hashed_pass)) {
    // password is correct
} else {
    // password is wrong
}

This way, it's more secure and even if your db is compromised; I believe no one will be able to workout what the used password was as it is only a hash of the original password.