Can I use a prepared PDO document to bind an identifier (table or field name) or syntax keyword?

Question

I am working on a dynamic query that uses variables to indicate a table, field / column, and value to search for. I got a request to work as expected without variables, both in phpMyAdmin (manually entering the request) and inside the code, combining the variables into a complete request.

However, when I use bindParam() or bindValue() to bind variables, it returns an empty array.

Here is my code:

 function search_db($db, $searchTerm, $searchBy, $searchTable){ try{ $stmt = $db->prepare(' SELECT * FROM ? WHERE ? LIKE ? '); $stmt->bindParam(1, $searchTable); $stmt->bindParam(2, $searchBy); $stmt->bindValue(3, '%'. $searchTerm.'%'); $stmt->execute(); } catch(Exception $e) { return array(); } return $stmt->fetchAll(PDO::FETCH_ASSOC); } // database initialization, creates the $db variable require(ROOT_PATH . "include/database.php"); $matches = search_db($db, 'search term', 'myColumn', 'myTable'); var_dump($matches); 

Expected Results : an array of rows from the database

Actual results : empty array

Answer 1

Can I use a prepared PDO statement to bind an identifier (table or field name) or syntax keyword?

Unfortunately, a prepared statement can only represent a data literal. So, a very common mistake is this request:

 $opt = "id"; $sql = "SELECT :option FROM t WHERE id=?"; $stm = $pdo->prepare($sql); $stm->execute(array($opt)); $data = $stm->fetchAll(); 

Depends on the PDO settings, this request will be caused either by an error (in the case of using real prepared statements) or by the literal string 'id' in the set of fields (in the case of emulated preparations).

So, the developer must take care of the identifiers himself - PDO offers without help .

To make a dynamic identifier secure, you need to follow two strict rules:

• to properly format the identifier
• to check it on a hard coded whitelist .

To format an identifier, you need to apply these 2 rules:

• Enter the id in the backticks.
• Run back, doubling them.

After this formatting, it is safe to insert the $ table variable into the query. Thus, the code will look like this:

 $field = "`".str_replace("`","``",$field)."`"; $sql = "SELECT * FROM t ORDER BY $field"; 

However, although such formatting would be sufficient for cases such as ORDER BY, for most other cases there is a possibility for various kinds of injections: by allowing the user to select a table or field that they can see, we can detect some confidential information, such as password or other personal data. Therefore, it is always better to check dynamic identifiers for a list of valid values. Here is a quick example:

 $allowed = array("name","price","qty"); $key = array_search($_GET['field'], $allowed); $field = $allowed[$key]; $query = "SELECT $field FROM t"; //value is safe 

The rules are the same for keywords, but of course there is no formatting - thus, only a whitelist is possible and should be used:

 $dir = $_GET['dir'] == 'DESC' ? 'DESC' : 'ASC'; $sql = "SELECT * FROM t ORDER BY field $dir"; //value is safe 

See also the user note in the PHP documentation: PDO user quote :: quote