PHP: PDO bindValue() causes 0 results to be returned

Question

I've having some troubles with the PDO bindValue() function. Whenever I seem to use it, my queries always return 0 results. However it works fine if I put $user and $pass straight into the sql without the use of bindValue()

$user is a string
$password is a sha1() hash

public function login($user, $pass) {
            global $CMS;

            $sql = "SELECT `username,`password` FROM `" . TB_PREFIX . "users` WHERE `username` = ':user' AND `password` = ':pass'";
            $query = $CMS->prepare_query($sql);
            $query->bindValue(':user', $user, PDO::PARAM_STR);
            $query->bindValue(':pass', $pass, PDO::PARAM_STR);
            $query->execute();

            # User successfully authenticated
            if ($query->rowCount() == 1) {
                # Get all data from DB and populate class variables
                self::populate_user_data($user);
                session_register($user . "-" . base64_encode($_SERVER['REMOTE_ADDR']));
                return true;
            }

            # User failed authentication            
            return false;
        }

`':user'` - why not just `:user`? Read the manual, it has *examples*: [`bindyValue`](http://php.net/manual/en/pdostatement.bindvalue.php). — hakre, Jan 02 '12 at 18:54
And also check the syntax, it's broken (apart from the placeholders). So be more careful next time. — hakre, Jan 02 '12 at 18:57
Reading [php PDOStatement->bindValue doc](http://php.net/manual/en/pdostatement.bindvalue.php) it seems that problem are quotes. You should write: WHERE `username` = :user insteat WHERE `username` = ':user' — dani herrera, Jan 02 '12 at 18:57
possible duplicate of [PDO Prepare statement not processing parameters](http://stackoverflow.com/questions/2434560/), [Having an issue binding parameters to a PDO Statement](http://stackoverflow.com/questions/8436963/), [Using named paramaters with PDO for LIKE](http://stackoverflow.com/questions/7252283/) — outis, Jan 02 '12 at 19:17

score 3 · Accepted Answer · answered Jan 02 '12 at 18:54

You should not put the quotes around the values yourself, they will be added (if needed, such as in the case of strings - this case):

$sql = "SELECT `username,`password` FROM `" . TB_PREFIX . "users` 
        WHERE `username` = :user AND `password` = :pass";

score 1 · Answer 2 · answered Jan 02 '12 at 18:55

1

The placeholders in a prepared statement must not be quoted; PDO is already doing all the quoting. You want:

 $sql = "SELECT `username,`password` FROM `" . TB_PREFIX . "users` " .
        "WHERE `username` = :user AND `password` = :pass";

answered Jan 02 '12 at 18:55

phihag

278,196
72
453
469

score 1 · Answer 3 · answered Jan 02 '12 at 18:57

When using prepared statements, the values get escaped automagically.

This means, that you don't have to set quotes around the arguments.

try:

$sql = "SELECT `username,`password` ".
       "FROM `" . TB_PREFIX . "users` ".
       "WHERE `username` = :user AND `password` = :pass";

and you should be fine.

As a side note though: you should NEVER store user passwords literally. Check this excellent article: You're Probably Storing Passwords Incorrectly

PHP: PDO bindValue() causes 0 results to be returned

3 Answers3