Pdo Parameterized Query - Reuse Named Placeholders

PDO Parameterized Query - Reuse named placeholders?

PDO::prepare states that "you cannot use a named parameter marker of the same name twice in a prepared statement", so I guess that's a no then.

PHP's PDO prepared statement: am I able to use one placeholder multiple times?

PDO::prepare states that

[y]ou cannot use a named parameter marker of the same name more than once in a prepared statement, unless emulation mode is on.

Since it's generally better to leave emulation mode off (so the database does the prepared statement), you'll have to use id_0, id_1, etc.

pdo can't allow reuse of placeholders - what's an alternative for searching multiple columns?

You have to include the % signs in the parameters, not in the query

$testString =%testString%

Also using unnamed parameters ,? , requires a separate parameter for each ?. Using named parameters avoids this.

$stmt = $dbh->prepare("Select * from tableX where tableX.column1 LIKE :testString
                      OR tableX.column2 Like :testString OR ... ");
$stmt->bindParam(':testString',$testString, PDO::PARAM_STR);

Do I have to rebind my PDO parameters when I reuse the (slightly modified) query?

In fact, if you only tried it yourself, you would find that there is no way to change a query in a statement. So, your assumption will fail even with making a "slight modification" to the query, not even making to the stage of "rebinding" values. There is no "second prepare" either. By calling prepare, you are creating a brand new statement that knows nothing of the others (a behavior is common for any other variable in PHP).

Why PDO doesn't allow multiple placeholders with the same name?

Is there any setting or a tweak to bypass this?

Yes, there is. You can turn emulation mode ON and be able to use the same placeholder multiple times.

So the described behavior is observed only when the emulation is turned OFF. I don't really understand why it is so but here is an explanation from Wez Furlong (the PDO author):

The change was made for two reasons; first and foremost, if you re-use the same variable in a bind, it is possible to induce a crash when using some drivers. It’s not possible to guarantee to do the right thing, and having a way to trigger a crash can sometimes be used as an attack vector for a security exploit.

The second reason is that of portability. Some drivers would internally perform this check and error out. If you code against the drivers that don’t enforce this, then your code won’t work on those that don’t.

http://paul-m-jones.com/archives/243#comment-740

PDO MySQL query failing with more than one comparison operator

Try to use different names for the parameters, even if you are using the same value:

$db = static::getDB();
$sql = 'SELECT * FROM djs WHERE day = :day 
                 AND start_hour > :hr1 AND end_hour <= :hr2 
                 AND shifts LIKE :shift';

$stmt = $db->prepare($sql);
$stmt->bindParam(':day', $arr['day'], PDO::PARAM_STR);
$stmt->bindParam(':hr1', $arr['hr'], PDO::PARAM_INT);
$stmt->bindParam(':hr2', $arr['hr'], PDO::PARAM_INT);
$stmt->bindParam(':shift', $shift, PDO::PARAM_STR);
$stmt->execute();
return $stmt->fetch(PDO::FETCH_OBJ);

PHP PDO: Can't bind value to multiple variables?

Query text should be rewritten using JOIN:

$query = $db->prepare("
    SELECT q.question_attempts_permitted, q.question_range, a.answer_text, r.attempt_count 
    FROM checkup_questions q 
    JOIN checkup_answers a ON a.question_id = q.question_id
    JOIN user_responses r ON r.question_id = q.question_id
    WHERE q.question_id=:question_id
");
// you can provide placeholder without `:`
$query->bindValue('question_id', $question_id, PDO::PARAM_INT);
$query->execute();

Here you have only one placeholder.

Problem with binding NULL value to named placeholders with associative array in execute function in PDO

The IS operator can't be used with an expression. IS NULL and IS NOT NULL are keywords.

You need a test that works with both null and non-null values of :skill. You can use the null-safe equality operator, <=>

$sql = 'SELECT * 
        FROM employees 
        WHERE salary > :salary 
        AND skill <=> :skill';

Prepared statements, SQLSTATE[HY093]: Invalid parameter number

As commentented by FunkFortyNiner and tadman, it is possible that the issue comes from the fact that you are reusing the same placeholder.

Actually the MySQL syntax does not require you to reuse the named parameter: you can use the VALUES() to refer to the values initially passed for INSERT.

Also, your attempt to update event_id using LAST_INSERT_ID() does not seem right; I am unsure that this is valid syntax - and anyway, if this is the primary key of table, then you don't want to update it.

Finally, as pinpointed by FunkFortyNiner, event is a reserved word in MySQL, so it needs to be quoted.

$q = 
    "INSERT INTO events(
        event_id, 
        `event`, 
        staff_booking_id, 
        is_read, 
        priority
    ) 
    VALUES(
        :event_id, 
        :event, 
        :staff_booking_id, 
        :is_read, 
        :priority
    ) 
    ON DUPLICATE KEY UPDATE 
        `event` = VALUES(`event`),
        staff_booking_id = VALUES(staff_booking_id),
        is_read = VALUES(is_read), 
        priority = VALUES(priority)";

---

Related Topics