How Can I Safely Include PHP Variables in MySQL Queries?

Including PHP Variables in MySQL Queries

Issue:

Including PHP variables within MySQL statements can cause errors, especially when inserted as values within the VALUES clause.

Solution 1: Use Prepared Statements

Prepared statements provide a secure and efficient way to include PHP variables in queries. Here's how to do it:

1. Prepare the Query: Replace PHP variables with placeholder characters (e.g., ?).
2. Bind Variables: Associate PHP variables with placeholders.
3. Execute the Query: Run the prepared statement with the bound variables.

Example Using mysqli:

$type = 'testing';
$reporter = "John O'Hara";
$sql = "INSERT INTO contents (type, reporter, description) VALUES (?, ?, ?)";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("sss", $type, $reporter, $description);
$stmt->execute();

Example Using PDO:

$type = 'testing';
$reporter = "John O'Hara";
$sql = "INSERT INTO contents (type, reporter, description) VALUES (?, ?, ?)";
$stmt = $pdo->prepare($sql);
$stmt->execute([$type, $reporter, $description]);

Solution 2: Use White List Filtering

For query parts that represent identifiers (e.g., table or field names), use white list filtering to ensure they are valid. This involves:

1. Creating a White List: Specify a list of allowed values.
2. Checking Variables: Verify that PHP variables match allowed values.
3. Formatting Identifiers: Format identifiers according to database syntax (e.g., backticks for MySQL).

Example of White List Filtering for Order By:

$orderby = $_GET['orderby'] ?: "name"; // Set default
$allowed = ["name", "price", "qty"]; // White list
if (!in_array($orderby, $allowed)) {
    throw new InvalidArgumentException("Invalid ORDER BY field name");
}

The above is the detailed content of How Can I Safely Include PHP Variables in MySQL Queries?. For more information, please follow other related articles on the PHP Chinese website!