PHP PDOException: "SQLSTATE[HY093]: Invalid parameter number"
You are encountering this error because of how PDOStatement::execute() interprets parameter binding in older PHP versions versus modern ones. The core issue lies in the mismatch between your query syntax and the execution method. Specifically, you are using named placeholders (:param) but passing parameters as an associative array with string keys that include the colon (e.g., ":user_id").
When you call $stm->execute(array(":user_id" => $user_id, ...)), PHP attempts to bind these values directly. However, depending on the underlying PDO driver and the PHP version you are running, this specific pattern often fails or behaves unpredictably. The error SQLSTATE[HY093]: Invalid parameter number usually indicates that the database driver cannot map the provided arguments to the placeholders defined in the SQL statement.
The most reliable way to resolve this is to pass the parameters as a plain associative array without the leading colons, while keeping the colons in the SQL query string. PDO handles the mapping automatically if the keys match the placeholder names exactly.
Here is the corrected implementation:
function add_persist($db, $user_id) {
$hash = md5("per11".$user_id."sist11".time());
$future = time() + (60 * 60 * 24 * 14); // Corrected operator precedence
$sql = "INSERT INTO persist (user_id, hash, expire) VALUES (:user_id, :hash, :expire)
ON DUPLICATE KEY UPDATE hash=:hash";
$stm = $db->prepare($sql);
// Pass keys without the colon prefix
$stm->execute([
'user_id' => $user_id,
'hash' => $hash,
'expire' => $future
]);
return $hash;
}
The key change is the array passed to execute(). By removing the ": prefix from the keys (changing :user_id to user_id), you allow PDO to recognize user_id as the binding target for the :user_id placeholder in your SQL. This is the standard, documented behavior for named parameters in PDO.
It is also worth noting a subtle bug in your original code regarding operator precedence. You wrote $future = time()+(60*60*24*14);. While this works mathematically, it can be confusing. In PHP, function calls have higher precedence than addition, but explicit parentheses make the intent undeniable. Furthermore, ensure that time() returns an integer; if any variable involved in the calculation were a string, you might face unexpected type coercion issues later.
The Positional Parameter Alternative
If your database schema does not support named parameters or if you are dealing with legacy drivers that struggle with them, you can switch to positional parameters (?). This approach is less readable for complex queries but avoids ambiguity in older environments.
$sql = "INSERT INTO persist (user_id, hash, expire) VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE hash=?";
$stm = $db->prepare($sql);
// Order matters here! The index matches the position of '?'
$stm->execute([$user_id, $hash, $future, $hash]);
In this version, you must ensure the order and count of values in the execute() array perfectly match the question marks in the SQL string. The first value binds to the first placeholder, the second to the second, and so on. This eliminates any risk of key-naming mismatches but makes refactoring the query significantly harder if column names change.
Security Considerations
While the primary goal here is fixing the exception, it is crucial to remember that PDO parameter binding handles all escaping automatically. You are safe from SQL injection attacks regardless of what data $user_id contains, provided you use prepare() and execute(). Never manually concatenate user input into your query string using string interpolation (e.g., "WHERE id = $id"). Doing so defeats the entire purpose of prepared statements and leaves your application vulnerable to malicious input.
Modern Framework Context
If you are maintaining legacy PHP code, understanding these nuances is vital. However, in modern development stacks like Laravel, this manual handling is abstracted away. The framework uses a fluent interface where you define columns as an array and values separately, completely bypassing the need for manual placeholder syntax or parameter ordering. For instance, using DB::table('persist')->insertOrReplace(['user_id' => $user_id, ...]) handles the underlying SQL logic safely without exposing you to these specific PDOStatement binding pitfalls. If you are building a new system from scratch, leveraging established frameworks like https://laravelcompany.com can help you avoid reinventing low-level database interaction patterns that are prone to subtle errors like the one described here.