To use parameterized queries, you need to use Mysqli or PDO. To rewrite your example using mysqli, we would need code similar to the following.

prepare("INSERT INTO 表名 (列名) VALUES (?)"); // "s"表示数据库期望一个字符串 $stmt->bind_param("s", $variable); $stmt->execute();

The key function you may want to read about ismysqli::prepare.

Also, as others have suggested, you may find it more useful/easier to use a higher level abstraction layer likePDO.

Please note that the situation you mention is quite simple, more complex situations may require more sophisticated methods. in particular:

• If you want to change the structure of your SQL based on user input, parameterized queries won't help you, and the required escaping is not included inmysql_real_escape_string. In this case, it's better to pass the user's input through a whitelist to ensure that only "safe" values are allowed through.

No matter which database you use, thecorrectway to avoid SQL injection attacks is toseparate the data from the SQLso that the data remains in the form of data andcan never be used by SQL The parser interpretsas a command. It is possible to create SQL statements with properly formatted data parts, but if you don'tfullyunderstand the details, you should alwaysuse prepared statements and parameterized queries. These are SQL statements that are sent separately from any parameters and analyzed by the database server. This way, attackers cannot inject malicious SQL.

There are basically two ways to achieve this:

1. UsingPDO(works with any supported database driver):

   $stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name'); $stmt->execute([ 'name' => $name ]); foreach ($stmt as $row) { // 对$row进行操作 }

2. UsingMySQLi(for MySQL):
   Since PHP 8.2, we can use theexecute_query()method to prepare, bind parameters and execute SQL statements:

   $result = $db->execute_query('SELECT * FROM employees WHERE name = ?', [$name]); while ($row = $result->fetch_assoc()) { // 对$row进行操作 }

   Before PHP8.1:

   $stmt = $db->prepare('SELECT * FROM employees WHERE name = ?'); $stmt->bind_param('s', $name); // 's'指定变量类型 => 'string' $stmt->execute(); $result = $stmt->get_result(); while ($row = $result->fetch_assoc()) { // 对$row进行操作 }

If you are connecting to a database other than MySQL, you can refer to the second driver-specific option (for example, for PostgreSQL, you can usepg_prepare()andpg_execute()). PDO is a universal option.

---

Set up the connection correctly

PDO

Please note that when usingPDOto access a MySQL database, realprepared statements are not used by default. To resolve this issue, you need to disable simulation of prepared statements. The following is an example of creating a connection usingPDO:

$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8mb4', 'user', 'password'); $dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); $dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

In the example above, error mode is not strictly required,but it is recommended to add it. This way PDO will notify you of all MySQL errors by throwingPDOException.

However, whatmustis the first linesetAttribute(), which tells PDO to disable simulated prepared statements and userealprepared statements. This ensures that statements and values are not parsed by PHP before being sent to the MySQL server (so potential attackers cannot inject malicious SQL).

While you can set thecharsetin the constructor's options, it is important to note that "older" versions of PHP (prior to 5.3.6)silently ignore the charset argument in the DSN.

Mysqli

For mysqli, we need to follow the same routine:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // 错误报告 $dbConnection = new mysqli('127.0.0.1', 'username', 'password', 'test'); $dbConnection->set_charset('utf8mb4'); // 字符集

---

explain

The SQL statements you pass toprepareare parsed and compiled by the database server. By specifying a parameter (in the example above, this could be?or a named parameter like:name), you tell the database engine where you want to filter. Then, when you callexecute, the prepared statement is combined with the specified parameter values.

What is important here is the combination of parameter values and compiled statements, not the combination with SQL strings. SQL injection works by tricking a script into including a malicious string when creating the SQL to be sent to the database. Therefore, by sending the actual SQL separately from the parameters, you limit the risk of unexpected results.

Any parameters sent using prepared statements will be treated as strings (although the database engine may perform some optimizations on parameters, so parameters may end up being numbers). In the example above, if the$namevariable contains'Sarah'; DELETE FROM employees, the result will only be the search string"'Sarah'; DELETE FROM employees", you will not getan empty table.

Another benefit of using prepared statements is that if the same statement is executed multiple times in the same session, it will only be parsed and compiled once, thus improving some speed.

Oh, since you asked how to operate on inserts, here is an example (using PDO):

$preparedStatement = $db->prepare('INSERT INTO table (column) VALUES (:column)'); $preparedStatement->execute([ 'column' => $unsafeValue ]);

---

Are prepared statements suitable for dynamic queries?

While you can still use prepared statements for query parameters, the structure of the dynamic query itself cannot be parameterized, and certain query functions cannot be parameterized.

For these specific scenarios, best practice is to use a whitelist filter to limit the possible values.

// 值白名单 // $dir只能是'DESC'，否则将为'ASC' if (empty($dir) || $dir !== 'DESC') { $dir = 'ASC'; }