Home >Database >Mysql Tutorial >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:
Format Identifiers:
Whitelist Dynamic Values:
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!