How Can I Safely Include PHP Variables in MySQL Statements?

Including PHP Variables in MySQL Statements

You encounter an issue when inserting values into a table using a PHP variable within your VALUES statement. Understanding the proper approach for integrating PHP variables into MySQL statements is crucial.

Utilize Prepared Statements

Inserting data literals (SQL strings or numbers) into MySQL statements requires the use of prepared statements. It involves:

1. Replacing variables with placeholders in your SQL statement
2. Preparing the revised query
3. Binding variables to placeholders
4. Executing the query

Adding Data Literals with Mysqli

In PHP 8.2, these steps can be combined into a single call:

$type = 'testing';
$reporter = "John O'Hara";
$sql = "INSERT INTO contents (type,reporter,description) VALUES ('whatever',?,?)";
$mysqli->execute_query($sql, [$reporter, $description]);

For older PHP versions:

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

Adding Data Literals with PDO

PDO offers a streamlined approach:

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

Filter Variables for Other Query Parts

Variables representing query parts other than literals (keywords, identifiers) should be filtered through a whitelist. This prevents the insertion of unintended values.

For example, to filter a field name based on user input:

$orderby = $_GET['orderby'] ?: "name"; // set the default value
$allowed = ["name", "price", "qty"]; // the white list of allowed field names
$key = array_search($orderby, $allowed, true); // see if we have such a name
if ($key === false) { 
    throw new InvalidArgumentException("Invalid field name"); 
}

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