How to Safely Insert PHP Variables into MySQL Queries?

Safely include PHP variables in MySQL statements

You may encounter problems when trying to use PHP variables as part of a SQL statement to insert values ​​into a MySQL table. To ensure correct execution, be sure to follow these rules:

1. Use prepared statements

Prepared statements are essential for adding PHP variables that represent SQL data literals (strings or numbers). You must replace variables with placeholders in the SQL statement and then prepare, bind, and execute the query.

The following is an example using mysqli:

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

For PDO, the binding and execution parts can be combined:

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

2. Implement whitelist filtering

If a PHP variable represents part of a query (beyond a data literal), such as a keyword or identifier, it must be checked against a predefined "whitelist" of allowed values. This ensures that only valid values ​​are included in the query string.

The following is an example of whitelist filtering for sort field names:

<code><br></br>$orderby = $_GET['orderby'] ?: "name"; // 设置默认值<br></br>$allowed = ["name", "price", "qty"]; // 允许的字段名称白名单<br></br>$key = array_search($orderby, $allowed, true);<br></br>if ($key === false) {throw new InvalidArgumentException("无效的字段名称");<p>}<br></br></p></code>

After whitelist filtering, the $orderby variable can be safely included in SQL queries.

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