Which tokens can be parameterized in PDO prepared statements?

Question

I'm playing around with prepared statements in PHP/PDO. The basic queries work fine, passing a value to the WHERE clause:

$stmt = $db->prepare( 'SELECT title FROM episode WHERE id=:id' );
$stmt->bindParam( ':id', $id, PDO::PARAM_INT );
$id = 5;
$stmt->execute();

However I have a situation where I need to pass variables for the field names. This query (with appropriate binding) works fine:

SELECT :field FROM episode WHERE id=:id

This one gives an error:

SELECT title FROM :field WHERE id=:id

This one doesn't give an error, but returns no rows:

SELECT title FROM episode WHERE :field=:id

So, what things should work in prepared statements? Can I 'parameterize' field names, table names and so on?

Answer 1

You cannot parameterize table names, column names, or anything in an IN clause (thanks to c0r0ner for pointing out the IN clause restriction).

See this question, and subsequently this comment in the PHP manual.

Answer 2

@ Josh Leitzel

That thinking is very restrictive (and is in my opinion just an excuse for being too lazy to implement a robust solution), especially for dynamic tree structures expressed in a database.

Consider the following example:

My project has a logical structure:

A company hierarchy is expressed in terms of entities. Each entity can treated in the general case of being a member of the hierarchy or as a member of a specific level of the hierarchy. The hierarchy itself is defined in a table as a single tree branch as follows:

entity_structure (
   id
   name
   parent_entity_structure_id
);

and the entities themselves are expressed as:

entities (
   id
   name
   entity_structure_id
   parent_id
);

For ease of use I've built an algorithm that creates a flat view of the tree. The following concrete example illustrates what I mean:

SELECT * FROM entity_structure;

id      | name               | entity_structure_parent_id
-----------------------------------------------------------
1       | Company            | null    (special one that always exists)
2       | Division           | 1
3       | Area               | 2
4       | Store              | 3

This would result in the following flat representation being produced:

entity_tree (
   entity_id
   division_id
   area_id
   store_id
)

Entities that are at the division level would have division_id, area_id and store_id as NULL, An area area_id and store_id as NULL, etc.

The nice thing about this is it lets you query all the children of a division using a statement similar to the follow:

SELECT * FROM entity_tree WHERE division_id = :division_id;

However this assumes that I know the structure level of the entity I'm querying. It would be nice to do:

SELECT * FROM entity_tree WHERE :structure = :entity_id;

I know it's not hard to figure out the structure level of a single entity, but assume I'm looping through a collection of entities that may not all be at the same level. As it is now I have to build a separate query for each level of the hierarchy, but if I could parameterize fields I could do the following:

$children = array();
$stmt = $pdo->prepare('SELECT entity_id FROM entity_tree WHERE :structure = :entityId');
foreach ($entities AS $entity) {
   $stmt->execute(array(
      ':structure' = $entity->getEntityStructureId(),
      ':entityId'  = $entity->getId()
   ));

   $children[$entity->getId()] = $stmt->fetchAll(PDO::FETCH_COLUMN);
}

resulting in cleaner code and only one prepared statement.

The entire example uses no user input whatsoever.

Just something to consider.

Answer 3

You can't parameterize anything withing IN clause as well.