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

I'm working on a dynamic query that uses variables to specify a table, a field/column, and a value to search for. I've gotten the query to work as expected without the variables, both in phpMyAdmin (manually typing the query) and from within the code by concatenating the variables into a complete query.

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

Here's 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: an empty array

Solution 1:

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

Unfortunately, prepared statement can represent a data literal only. So, a very common pitfall is a query like this:

$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 query will result with either error (in case of using real prepared statements) or just a literal string 'id' in the fieldset (in case of emulated prepares).

So, a developer have to take care of identifiers oneself - PDO offers no help for this matter.

To make a dynamical identifier safe, one have to follow 2 strict rules:

• to format identifier properly
• to verify it against a hardcoded whitelist.

To format an identifier, one have to apply these 2 rules:

• Enclose identifier in backticks.
• Escape backticks inside by doubling them.

After such formatting, it is safe to insert the $table variable into query. So, the code would be:

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

However, although such a formatting would be enough for the cases like ORDER BY, for the most other cases there is a possibility for a different sort of injection: letting a user to choose a table or a field they can see, we may reveal some sensitive information, like password or other personal data. So, it's always better to check dynamical identifiers against a list of allowed values. Here is a brief example:

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

For keywords rules are same, but of course there is no formatting available - thus, only whitelisting is possible and ought to be used:

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

See also this user contributed note in PHP documentation: User note on PDO::quote