Can Prepared Statements Handle Database Identifiers and Keywords?

Prepared Statements: Can They Handle Identifiers and Keywords?

Dynamic queries utilize variables to specify tables, fields, and search values. While concatenating variables into the query has proven successful, using PDO's bindParam() or bindValue() to bind variables results in empty arrays.

Why It Doesn't Work:

PDO prepared statements only allow placeholders for data literals. Attempting to represent identifiers (table or field names) or keywords using placeholders will not function.

The Solution:

• Identifiers: To include variables representing identifiers, follow these rules:

  • Enclose identifiers in backticks (`).
  • Escape backticks within the variable by doubling them (``).

• Keywords:

  • Check user-provided keywords against a whitelist.
  • Use only whitelisted keywords in the query.

Example Code:

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

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

The above is the detailed content of Can Prepared Statements Handle Database Identifiers and Keywords?. For more information, please follow other related articles on the PHP Chinese website!