How Can I Safely Use Dynamic Table and Field Names in Prepared Statements?

Using Prepared Statements to Bind Identifiers and Keywords

Problem:

Creating a dynamic query using prepared statements, but attempts to bind table names, field names, or syntax keywords result in an empty array.

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);
}

Expected Results:
An array of results from the database.

Actual Results:
An empty array.

Cause:
Prepared statements can only bind data literals, not identifiers (such as table or field names) or syntax keywords.

Solution:

To use dynamic identifiers or keywords, follow these steps:

1. Format Identifiers:

   • Enclose identifiers in backticks: "identifier".
   • Escape backticks within the identifier by doubling them: "identifier".

2. Whitelist Dynamic Values:

   • Create a list of allowed identifiers or keywords.
   • Check the dynamic value against the whitelist before using it.

Modified Code:

$field = "`" . str_replace("`", "``", $field) . "`";
$table = "`" . str_replace("`", "``", $table) . "`";
$sql = "SELECT * FROM $table WHERE $field = ?";

Keywords:

If necessary, whitelist and validate dynamic keywords as well. By following these guidelines, you can safely include dynamic identifiers and keywords in your prepared statements.

The above is the detailed content of How Can I Safely Use Dynamic Table and Field Names in Prepared Statements?. For more information, please follow other related articles on the PHP Chinese website!