What markers can be parameterized in prepared PDO reports?

Question

I play with prepared instructions in PHP / PDO. The main queries work fine, passing the 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 field names. This query (with the appropriate binding) works fine:

 SELECT :field FROM episode WHERE id=:id 

This gives an error:

 SELECT title FROM :field WHERE id=:id 

This does not give an error, but does not return rows:

 SELECT title FROM episode WHERE :field=:id 

So what should work in prepared statements? Can I "parameterize" field names, table names, etc.?

Answer 1

You cannot parameterize table names, column names, or anything in the IN clause (thanks to c0r0ner for specifying the IN constraint) .

See this question and then this comment in the PHP manual .

Answer 2

@ Josh Leitzel

This thinking is very restrictive (and, in my opinion, just an excuse for laziness to implement a reliable solution), especially for dynamic tree structures expressed in the database.

Consider the following example:

My project has a logical structure:

The hierarchy of companies is expressed in units. Each object can be considered in the general case as a member of the hierarchy or as a member of a certain level of the hierarchy. The hierarchy itself is defined in the table as a separate branch of the tree 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 created an algorithm that creates a flat view of a tree. The following specific 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 will result in the following flat view:

 entity_tree ( entity_id division_id area_id store_id ) 

Entities at the division level will have division_id, area_id and store_id as NULL, Area area_id and store_id as NULL, etc.

The nice thing about this is that it allows you to query all the children of a department using an operator similar to the following:

 SELECT * FROM entity_tree WHERE division_id = :division_id; 

However, this assumes that I know the structure level of the object I am requesting. It would be nice to do:

 SELECT * FROM entity_tree WHERE :structure = :entity_id; 

I know that it is not difficult to determine the level of structure of one object, but suppose that I am fixated on a collection of objects that may not be all on the same level. Since I must now create a separate query for each level of the hierarchy, but if I could parameterize the 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); } 

which leads to the creation of cleaner code and only one prepared statement.

The whole example does not use any user input.

Just think it over.

Answer 3

You cannot parameterize anything within an IN clause.