使用准备好的语句提取MySQL信息时，使用AES解密

Question

After being educated yesterday about trying to hash my usernames I decided on using the AES_ENCRYPT and DECRYPT functions in MySQL to securely store my usernames. As it stands I'm able to encrypt and store them in my database with this:

$con=mysqli_connect($servername, $dbusername, $dbpassword, $dbname);
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$stmt = $con->prepare("INSERT INTO users (username, password) VALUES (AES_ENCRYPT(?, '$aesKey'), ?)");
$stmt->bind_param("ss", $username, $password);
$username = $_POST['username'];
$plainPass = $_POST['password'];
$password = password_hash($plainPass, PASSWORD_ARGON2I);
$stmt->execute();
$stmt->close();
$con->close();
echo "<script> location.href='login.php'; </script>";
}
?>

I'm using a 256 bit encryption key which is loaded from a config file and is called as $aesKey

However, when I try and log in I'm unable to. Nothing happens. No SQL errors are printed but I do get PHP Notice: Undefined variable: username. Here's the code:

$submittedUser = $_POST['username'];
$submittedPass = $_POST['password'];

$con=mysqli_connect($servername, $dbusername, $dbpassword, $dbname);
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
if ($stmt = mysqli_prepare($con, "SELECT * FROM users Where username =AES_DECRYPT(?, '$aesKey')")) {
                    mysqli_stmt_bind_param($stmt, "s", $submittedUser);
                    mysqli_stmt_execute($stmt);
                    $result = mysqli_stmt_get_result($stmt);
                    echo mysqli_error($con);

}

while($row = mysqli_fetch_array($result))
{
$username = $row['username'];
$password = $row['password'];
}
if ($submittedUser == $username && password_verify($submittedPass, $password))
{
$_SESSION['user']=$username; 

//echo "<script> location.href='index.php'; </script>";
//        exit;   
}
else
{
 //echo "<script> location.href='login.php'; </script>";
//        exit;   
}
mysqli_close($con);
}
?>

I commented out the redirects as I was originally thinking they were what was causing the error messages not to be printed. Can anyone help me out? I can't seem to figure out what's not working.

Edit: I changed the statement back to "SELECT * FROM users Where username =AES_ENCRYPT(?, '$aesKey')" and I receive the following output:

Array ( [0] => 5�uY��V�s~"�ܮ� [username] => 5�uY��V�s~"�ܮ� [1] => $argon2i$v=19$m=1024,t=2,p=2$akcuREppYk1ERjRlUi5ZQw$yIdC5oMblekARArcB0XjwwmWywJ824iM8FmKqAu+9ys [password] => $argon2i$v=19$m=1024,t=2,p=2$akcuREppYk1ERjRlUi5ZQw$yIdC5oMblekARArcB0XjwwmWywJ824iM8FmKqAu+9ys )`

Edit 2: Here is the database structure. The unencrypted username should be evan and the unhashed password should be edwards.

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `username` text NOT NULL,
  `password` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`username`, `password`) VALUES
('5ÇuY¯úVõs~\"ÐÜ®€', '$argon2i$v=19$m=1024,t=2,p=2$NWZQS2Q3dE1ORndjaUdpWg$vOR3DBT+IhdAXIt7YUPDiExalf1lsMeIVe/zTZDDYfU');

Edit 3: Running the following code:

<?php

include 'config.php';
$con=mysqli_connect($servername, $dbusername, $dbpassword, $dbname);
$result = mysqli_query($con,"SELECT * FROM `users`");
while($row = mysqli_fetch_array($result))
{
echo $row['username'];
echo "<br>";
echo $row['password'];
}
mysqli_close($con);
?>

Gives me the following result:

5�uY��V�s~"�ܮ�
$argon2i$v=19$m=1024,t=2,p=2$NWZQS2Q3dE1ORndjaUdpWg$vOR3DBT+IhdAXIt7YUPDiExalf1lsMeIVe/zTZDDYfU

Answer 1

In order to get what you want from the database you will need two things in the query, the decrypted username (for use against the submitted user name) and the comparison to the submitted username. Thanks to @msbit for kicking this off in the right direction:

if ($stmt = mysqli_prepare($con, SELECT AES_DECRYPT(username, '$aesKey') FROM users WHERE AES_DECRYPT(username, '$aesKey') = ?){...

The first decryption, SELECT AES_DECRYPT(username, '$aesKey') as username, will give you the the username identifier to use in $row['username'] later in the code.

The second, WHERE AES_DECRYPT(username, '$aesKey') = ?, actually performs the comparison of the decrypted username to the submitted username.

EDIT

Using the OP's key and name (with TEXT as the column type) here is the insert statement:

INSERT INTO aes_test (username, encrypted) VALUES ('evan', AES_ENCRYPT('evan', '69552E16F55C3E88CF3CBC44EB5F71B24DD0CF5CB3A7C65EA97BC69224CF42F1'));

Here is the query, as it should be for a particular user:

SELECT username, AES_DECRYPT(`encrypted`, '69552E16F55C3E88CF3CBC44EB5F71B24DD0CF5CB3A7C65EA97BC69224CF42F1') FROM aes_test WHERE AES_DECRYPT(`encrypted`, '69552E16F55C3E88CF3CBC44EB5F71B24DD0CF5CB3A7C65EA97BC69224CF42F1') = 'evan'

Here is the result:

evan | evan