PDOStatement::bindParam doesn't work after the first call

Question

I have a little problem writing a simple class that uses PDO to connect to the db.
The problem is that the PDOStatement::bindParam doesn't work after the first call in the cycle, I mean if I have two parameters to give to the PDOStatement the query doesn't return the rignt result, instead if i give only one parameter it gives the right result.

Here's the code:

public function query($sql, $params = NULL) {
        // Opens the PDO connection
        $this->open();

        $this->stmt = $this->pdo->prepare($sql);

        if (isset($params)) {
            foreach ($params as $key => $value) {
                // This doesn't work after the second cicle.
                $this->stmt->bindParam(':' . $key, $value);
            }
        }

        $result = NULL;
        if (!$this->stmt->execute()) {
            $result = false;
        } else {
            $result = $this->stmt->fetchAll();
        }

        // Closes the PDO connection
        $this->close();

        return $result;
}

And here's the PDOStatement::debugDumpParams:

SQL: [114]
SELECT 1
FROM   users
WHERE  EXISTS
       (
              SELECT *
              FROM   users
              WHERE  username = :username
              AND    password = :password) limit 1
PARAMS: 2
KEY:NAME: [9] :username paramno=0 NAME=[9] ":username" is_param=1 param_type=2
KEY:NAME: [9] :password paramno=1 NAME=[9] ":password" is_param=1 param_type=2

Thanks for helping!

Have you tried this with other tables? As for using this to match a username and password, you really should be looking into using [`password_hash()`](https://stackoverflow.com/questions/30279321/how-to-use-password-hash) — Nigel Ren, Oct 14 '18 at 17:56
@NigelRen I'm using md5() function and a salt string to hash and then store the passwords, isn't that enough? And yes I've tried it with other tables — Vladoski, Oct 14 '18 at 18:02
Ok I found the problem. I fixed it using PDOStatement::bindValue instead of bindParam. — Vladoski, Oct 15 '18 at 07:22

score 1 · Accepted Answer · answered Oct 15 '18 at 13:19

TL;DR always use bindValue() unless you want to use the special behaviour of bindParam().

foreach ($params as $key => $value) {
    // This doesn't work after the second cicle.
    $this->stmt->bindParam(':' . $key, $value);
}

The reason this doesn't work as intended is the misconception of what PDO means with bindParam(). It doesn't mean "bind the SQL parameter" but "bind the value (i.e. the parameter for bindParam()) as referenced variable". Thus when execute() is called, it will use the value for $value (which is the variable bound to all SQL parameters) at execution time, not at the time bindParam() was called.

Cf. http://php.net/manual/en/pdostatement.bindparam.php where this behaviour is explained.

The solution is to use bindValue() instead of bindParam().

PDOStatement::bindParam doesn't work after the first call

1 Answers1