CODEWITHSUNDEEP
phpmysqlaes
user3579547
user3579547

Reputation: 21

Issue with AES_DECRYPT when retrieving from MySQL database

Okay so I have this page which is supposed to allow a user to login with their password which is encrypted in the database with AES. I want to pull the encrypted password out of the DB, decrypt it and compare it to the password the user enters. Here's my code:

<?php
session_start();
$validUser = false;

// see if they want to logout from a current session

$tOption = $_GET['cmd'];

if($tOption=="logout") {
    unset($_SESSION['SAFEUSER']);
}

?>
<html>
<body>
<?php

// are they attempting to log into the menu?

// alternative syntax that uses the submit button's value
// if(   $_POST['Fsubmit'] == "lookupnow" ) {


if(   ($_POST['Femail'] > "") or ($_POST['Fpassword'] > "")  ) {
    // look 'em up in the database to validate credentials

    // establish values for DB credentials
    $hostname = 'localhost';
    $dbname = ''; 
    $username = '';
    $userpass = '';
    $passkey = "sgjbasjgbaslhflshfoashf";

    // get connected to the DB
    try {

      $DBH = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $userpass);

    }
    catch(PDOException $e) {
        echo $e->getMessage();
    }

    $tEmail = $_POST['Femail'];
    $tPassword = $_POST['Fpassword'];

    /*$secureSQL = 'SELECT FirstName, LastName, Phone from Members WHERE Email="' 
            . $tEmail . '" AND Password="' . $tPassword . '"';*/

    $secureSQL = 'SELECT FirstName, LastName, Phone from Members WHERE Email="' 
            . $tEmail . '" AND AES_DECRYPT(Password, $passkey) ="' . $tPassword . '"';

    //echo $secureSQL . "<br>";

    // estalish SQL query  
    $STH = $DBH->query($secureSQL);

    # setting the fetch mode -- this comes into effect when the fetch() function is called
    $STH->setFetchMode(PDO::FETCH_ASSOC);

    while($row = $STH->fetch()) {
        $validUser = true;
        $_SESSION['SAFEUSER'] = true;
    }
    if($validUser==false) {
        echo "<font color='red'>Those credentials were not authenticated.</font><br/><br/>";
    }


}

// are they logged in?

if(!$_SESSION['SAFEUSER']) {
    // if not, make them login
?>
    Please enter your credentials:<br />
    <form method="post" action="mysecuremenu.php">
        Your email address: <input type="text" name="Femail"><br>
        Your password: <input type="password" name="Fpassword"></br>
        <input type="submit" name="Fsubmit" value="lookupnow">
    </form>

<?php   
} else {
    // otherwise, show them the menu
?>
<h2>Please select one of the following:</h2>
<ul>
    <li><a href="MySecureFirstDBForm.php">Enter a new employee</a></li>
    <li><a href="mySecuredbviewall_withdatatable.php">View all employees</a></li>
    <li><a href="mysecuremenu.php?cmd=logout">Logout</a></li>
</ul>

<?php

} // end if(safeuser)

?>
</body>
</html>

I get this error:

Fatal error: Call to a member function setFetchMode() on a non-object on line 58

I feel like it has to have something to do with my SELECT statement because the one I have commented out above it works fine for logging in with non-encrypted passwords. I would appreciate any and all help. Thanks!

Upvotes: 1

Views: 348

Answers (1)

user1864610
user1864610

Reputation:

You have a syntax error in your query, but more on that later. You'd have found it if you'd checked your calls to PDO for errors. PDO doesn't flag errors explicitly unless you ask it to, so you need to check the return value of your call to $DBH->query(...)

$STH = $DBH->query($secureSQL);
if ($STH === false) {
    $errorInfo = $DBH->errorInfo();
    echo $errorInfo[0].':'.$errorInfo[1].' ('.$errorInfo[2].')';
    exit;
}

(You could set PDO to throw an exception when it encounters an error and use a try...catch block to handle it. That's an exercise for the reader)

Now - that syntax error. You're building your query like this:

 $secureSQL = 
    'SELECT FirstName, LastName, Phone from Members WHERE Email="' 
            . $tEmail . '" AND AES_DECRYPT(Password, $passkey) ="' . $tPassword . '"';

You've tried to interpolate the $passkey variable, but that portion of your string is enclosed in single quotes so interpolation doesn't operate. You've concatenated the rest of the parameters, so it's not clear why you've done something different here.

You've also omitted enclosing single quotes around the $passkey so the query would have failed even if the interpolation had worked. Simply swapping your use of single- and double-quotes would have made this easier.

You need this:

 $secureSQL = 
    "SELECT FirstName, LastName, Phone from Members WHERE Email='" 
            . $tEmail . "' AND AES_DECRYPT(Password, '$passkey') ='" . $tPassword . "'";

There's more.

You have added your user input directly into your query string without any form of escaping. This leaves you wide open to SQL injection. Combine that with an encrypted password and you have an open invitation to a hacker to raid your database. At the very least you should use PDO::quote() to escape the variables, but a better approach would be to use prepared statements and avoid the injection risk altogether. (Another exercise for the reader).

And finally (this has been covered in the comments) do not encrypt passwords - hash them PHP provides password_hash() for this purpose in PHP 5.5+, and there is a shim you can use for earlier versions.

If your professor is teaching you to use AES_ENCRYPT() to secure passwords you really should take him to task over it.

Upvotes: 1

Related Questions