PDO BindValue doesn't work but works with direct paste

So I have some code

//passed as function param
$clause[2] = "'2016-09-09' AND '2016-09-09'"

$sql = "SELECT {$columns} FROM `{$table}` WHERE `{$clause[0]}` {$clause[1]} :clause";
$stm = $this->db->prepare($sql);
$stm->bindValue("clause", $clause[2]);
if ($stm->execute()) {
   return $stm->fetchAll(PDO::FETCH_OBJ);
}
d
//echo'd $sql
SELECT * FROM `deliveries` WHERE `delivery-date` BETWEEN :clause

If I replace the :clause in $sql with the raw input, '2016-09-09' AND '2016-09-09' then it works fine. As soon as I try to bind it with either :clause or using a ? then it fails. I have no clue what to do about it :( Thanks for the help!

Solution

You can't bind whole expressions like that. Binding values is not just string substitution. You can bind a value in an SQL query only where you would normally put a single scalar value. If you need two values for example for a BETWEEN predicate, you need two placeholders.

Furthermore, you must not put quotes in your bound values. The fact that a placeholder means exactly one scalar value makes quotes unnecessary.

It looks like you're trying to make a general-purpose function so you can make any conditions you want, and your $clause array is supposed to contain the column, the operator, and the value.

You're going to have to write code to format the SQL differently for multi-value predicates like IN() or BETWEEN:

$column = $clause[0];
$operator = $clause[1];
$valuesArray = (array) $clause[2];
switch ($operator) {
case 'IN':
    $expression = "(" . implode(",", array_fill(1, count($valuesArray), "?") . ")";
    break;
case 'BETWEEN':
    $expression = "? AND ?";
    break;
default:
    $expression = "?";
}
$sql = "SELECT {$columns} FROM `{$table}` WHERE `{$column}` {$operator} {$expression}";
$stm = $this->db->prepare($sql);
$stm->execute($valuesArray);
return $stm->fetchAll(PDO::FETCH_OBJ);

I don't bother to test the return value of execute() because you should just enable PDO::ERRMODE_EXCEPTION.