still got sql injection hacked using PDO parameterized query

Question

I have inherited an old php based website that keeps getting hacked. Per the information I found here: How can I prevent SQL injection in PHP?, I updated the sql queries using PDO parameterized queries. We got hacked last week again and it looked like a sql injection attack.
All of the usernames were deleted from the user table and replaced with their own information. Then the hackers were able to get into the admin section of the site and create all sorts of havoc.

How could this have happened with a parameterized query?

See my code below - what am I missing? I'm going to change the permissions of my db login used for this query so that only selects are allowed, but I really want to know why the query is vulnerable.

class pdoConnection
{ 
    var $conn;

    function pdoConnection()
    {               
        $this->createConnection();
    }

        function createConnection()
    {
        try
        {
            $dbConnection = new PDO('mysql:host=mysqlhostname.com;dbname=mydbname;charset=utf8', 
                    'dbusername', 'dbpassword',
                    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));                   
            $dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
            $dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $dbConnection -> exec('SET NAMES utf8');
            $dbConnection -> exec('SET CHARACTER SET utf8');
            $this->conn =$dbConnection;
        }
        catch(PDOException $e)
        {
            die('Could not connect: ' .$e->getMessage(). "<br><br>");
        }

    }

    //  GET CONNECTION
    //  --------------------------------------------------------------------- 
    function getConnection() 
    {   
        return $this->conn;
    } 

} 

$login = htmlspecialchars($_REQUEST['login'], ENT_QUOTES, 'UTF-8');
$pass =  htmlspecialchars($_REQUEST['pass'], ENT_QUOTES, 'UTF-8');

$msg = '';


//check to see if there is a value
if($login == '' || $pass == '') {
    $error = 1;
    $msg = "Please enter a username and password.<br />";

} 
else 
{
    //try to validate

    $clsPDO = new pdoConnection();
    $con = $clsPDO->getConnection();
    $stmt = $con->prepare('SELECT * FROM users where userName = :login');
    $stmt->execute(array(':login' => $login)) or die($con->errorInfo());
    $row = $stmt->fetch();

    if (isset($row))
    {
        if( $pass == $row['userPassword']) {

            $required_login = $row['userName'];
            $required_pass = $row['userPassword'];
            $userLevel = $row['userLevelID'];
            $userName = $row['userName'];
            $userID = $row['userID'];
        }
        else 
        {
            $error = 1;
            $msg = "Your username/password did not match. Please check your entries and try again.";
        }

    }
    else 
    {
        //did not validate
        $error = 1;
        $msg = "Your username/password did not match. Please check your entries and try again.";
    }

}

How did you conclude it was an SQL exploit? That does seem unlikely if the rest of the code follows that scheme. — mario, Aug 21 '14 at 01:35
Based on your code you are using plain-text passwords. That seems a far more serious problem than any sql problem you can have while using prepared statements correctly. Especially now that you have been hacked. — jeroen, Aug 21 '14 at 01:37
You're storing your passwords in cleartext. If someone's gotten into your database, you'd better inform all your users that their passwords have been compromised. Some people might be using the same password on other sites, and now the intruder knows that password. — Wyzard, Aug 21 '14 at 01:38
`$login = htmlspecialchars($_REQUEST['login'], ENT_QUOTES, 'UTF-8'); $pass = htmlspecialchars($_REQUEST['pass'], ENT_QUOTES, 'UTF-8');` - Huh? and with PDO? Plus, what type of password hashing method were you using; you ARE using one, right? See below... — Funk Forty Niner, Aug 21 '14 at 01:40
Use [**CRYPT_BLOWFISH**](http://security.stackexchange.com/q/36471) or PHP 5.5's [`password_hash()`](http://www.php.net/manual/en/function.password-hash.php) function. For PHP < 5.5 use the [`password_hash() compatibility pack`](https://github.com/ircmaxell/password_compat); in case you're not using that already. — Funk Forty Niner, Aug 21 '14 at 01:42
Why would you ever use `htmlspecialchars()` on a username and password unless you were outputting them in the context of HTML? See my answer here: http://stackoverflow.com/a/7810880/362536 — Brad, Aug 21 '14 at 01:45
Also, the `or die($con->errorInfo());` part will be shown to your users, if the execution ever fails. Not very smart. — Sverri M. Olsen, Aug 21 '14 at 01:58
Ok, points taken - the wrong method is being used to escape the user input, user input does not need to be escaped if using PDO, too much information is being shown in the event of a db connection error, and a hashing scheme needs to be used with the passwords. All of that will be rectified. However, none of that really explains how they got in. It seems like a sql injection attack because of the extra 145 entries in the user table and the fact that the damage done is what is allowed through the admin section of the site (deleting entries, etc.) Is the hole somewhere other than this code? — C Boland, Aug 21 '14 at 16:44
The problem may be elsewhere, if it was sql injection. Do your web server logs not provide any hints? — Michael - sqlbot, Aug 21 '14 at 22:07
unfortunately I don't have access to the server logs. I'm still waiting on the web hosts tech support to contact me. — C Boland, Aug 22 '14 at 18:44

Ethan · Accepted Answer · 2014-12-21T16:20:55.330

I have recently surveyed several discussions and tutorials on this topic, and I cannot see any security vulnerabilities in your code. You are turning off emulation, setting the character set to a known-safe one, and properly using the prepared statement. That said, two points come to mind:

You could confirm that a true prepared statement is being used on the database server, and not being emulated by PDO. You told it what you wanted, but that's not quite the same as confirming that it happened. Ditto for the character set configuration. Your multiple attempts at setting it properly are evidence that you don't trust the system to listen to you. Which is very understandable, from what I read about the history of the feature.
Even if someone finds a vulnerability in the code you posted, that would not seem to be sufficient cause to believe it was exploited in this case. Nor does the absence of people pointing out vulnerabilities mean that the code is safe. So, security would seem to lie in the direction of further forensics, increased logging for "next time", and the consideration of alternative attack vectors.

still got sql injection hacked using PDO parameterized query

1 Answers1