How to Safely Insert PHP Variables into MySQL Statements to Prevent SQL Injection?

Inserting PHP Variables into MySQL Statements

When including PHP variables in MySQL statements, it's essential to understand the rules to ensure data integrity and prevent potential security vulnerabilities.

1. Use Prepared Statements for Data Literals

What: All PHP variables representing SQL data literals (strings or numbers) must be included through prepared statements.

Why: Prepared statements sanitize and protect data by preventing SQL injection attacks.

How:

• Replace variables with placeholders in the SQL query.
• Prepare the query.
• Bind variables to placeholders.
• Execute the query.

Example using mysqli:

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

How using PDO:

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

2. White List Filtering for Query Parts

What: Variables representing other query parts (keywords, identifiers) must be filtered through a "white list" of allowed values.

Why: To prevent malicious users from injecting unauthorized query parts or keywords.

How:

• Create a list of allowed values.
• Check the variable against the list before including it in the query.
• Format identifiers according to the database syntax (e.g., backticks for MySQL).

Example:

$orderby = $_GET['orderby'] ?: "name";
$allowed = ["name", "price", "qty"];
$key = array_search($orderby, $allowed, true);
if ($key === false) {
    throw new InvalidArgumentException("Invalid field name");
}
$query = "SELECT * FROM `table` ORDER BY `$orderby` $direction";

The above is the detailed content of How to Safely Insert PHP Variables into MySQL Statements to Prevent SQL Injection?. For more information, please follow other related articles on the PHP Chinese website!