php - How can I pass an array of PDO parameters yet still specify their types?

Question

$sql = "SELECT * FROM table WHERE id LIKE CONCAT('%', :id, '%')
LIMIT :limit1, :limit2";

I want to still use the array input like this:

$stmt->execute($array);

Otherwise I cannot reuse the same method for executing my queries.

At the same time, the :limit1 and :limit2 doesn't work unless it is put in like this:

$stmt->bindParam(':limit1', $limit1, PDO::PARAM_INT);

I tried to do both but it doesn't execute with the bindParams:

$stmt->bindParam(':limit2', $limit2, PDO::PARAM_INT);
$stmt->execute($array);

What is the way around it?

I thought I could extend PDOStatement and add a new method "bindLimit" or something but I can't figure out what internal method PDO uses to bind parameters to a variable.

Answer 1

If you turn off the default setting of PDO::ATTR_EMULATE_PREPARES, then it will work. I just found out that that setting is on by default for mysql, which means you never actually use prepared statements, php internally creates dynamic sql for you, quoting the values for you and replacing the placeholders. Ya, a major wtf.

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$stmt = $pdo->prepare($sql);
$stmt->execute(array(5)); //works!

The prepares are emulated by default because of performance reasons.

See as well PDO MySQL: Use PDO::ATTR_EMULATE_PREPARES or not?