PDO Parameterized Query - Reuse named placeholders?

Question

In essence, I have a value that I have to call a couple times in my SQL query. Thus, is it possible to reuse the same named placeholder in the statement e.g. SELECT :Param FROM Table WHERE Column = :Param, then simply bindValue(":Param"), and have the value be there for both :Params?

Answer 1

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.

Answer 2

You can if you set PDO::ATTR_EMULATE_PREPARES = true.

E.g. $connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);.

If you're using Laravel you can set this in an options array in config/database.php. e.g. PDO::ATTR_EMULATE_PREPARES => true

Answer 3

Apart from reuse, the main issue here is that you are trying to dynamically change col names.

This answer posted by an anonymous user on http://php.net/manual/en/pdo.prepare.php :

To those wondering why adding quotes to around a placeholder is wrong, and why you can't use placeholders for table or column names:

There is a common misconception about how the placeholders in prepared statements work: they are not simply substituted in as (escaped) strings, and the resulting SQL executed. Instead, a DBMS asked to "prepare" a statement comes up with a complete query plan for how it would execute that query, including which tables and indexes it would use, which will be the same regardless of how you fill in the placeholders.

The plan for "SELECT name FROM my_table WHERE id = :value" will be the same whatever you substitute for ":value", but the seemingly similar "SELECT name FROM :table WHERE id = :value" cannot be planned, because the DBMS has no idea what table you're actually going to select from.

Even when using "emulated prepares", PDO cannot let you use placeholders anywhere, because it would have to work out what you meant: does "Select :foo From some_table" mean ":foo" is going to be a column reference, or a literal string?

When your query is using a dynamic column reference, you should be explicitly white-listing the columns you know to exist on the table, e.g. using a switch statement with an exception thrown in the default: clause.

Answer 4

Many queries like yours can be rewritten to use only one placeholder.

SELECT :Param FROM Table WHERE Column = :Param

would be the same as

SELECT Column FROM Table WHERE Column = :Param

But sometimes it's not that simple. For example:

SELECT *
FROM my_table
WHERE first_name LIKE :Param
   OR last_name  LIKE :Param
   OR biography  LIKE :Param

In such case you could reuse the parameter value storing it in a cross joined derived table (subquery in FROM clause):

SELECT t.*
FROM my_table t
CROSS JOIN (SELECT :Param as Param) AS x
WHERE first_name LIKE x.Param
   OR last_name  LIKE x.Param
   OR biography  LIKE x.Param

Answer 5

There's a workaround:

  function search($criteria) {

     $sql = "SELECT * FROM my_table
        WHERE column_1 like CONCAT('%', :criteria1, '%') 
           OR column_2 like CONCAT('%', :criteria2, '%') 
           OR column_3 like CONCAT('%', :criteria3, '%')  
     ";

     $stmt =  $this->db->prepare($sql);
     $stmt->bindParam(':criteria1', $criteria);
     $stmt->bindParam(':criteria2', $criteria);
     $stmt->bindParam(':criteria3', $criteria);
     $stmt->execute();
     return($stmt->fetchAll(PDO::FETCH_ASSOC));
  }

In summary, use different placeholders with the same criteria.