How to Safely Embed PHP Variables in MySQL Statements?

embedded PHP variables in mysql statement

Enhance the SQL statement with PHP variables can dynamically generate query. The following is the problem you encountered:

In order to solve this error, please follow the following guide:

<code class="language-php">$type = 'testing';
mysql_query("INSERT INTO contents (type, reporter, description) 
     VALUES($type, 'john', 'whatever')");</code>

<.> 1. Use the pre -processing statement

Any variables that indicate SQL text (string, numbers) in the statement are compulsory. The following is the workflow:

Use place occupies:

The variables are replaced in the SQL query to the placement symbol.

Ready to query:
• Use the method on the database connection object.
Bind variables:
• Use to associate with the variable value. prepare() Execute:
Use
• Perform pre -processing query. bind_param() In mysqli (PHP 8.2):
• In mysqli (the older PHP version): execute()

In PDO:

<code class="language-php">$type = 'testing';
$reporter = "John O'Hara";
$description = 'whatever'; //添加了description变量
$sql = "INSERT INTO contents (type,reporter,description) VALUES (?,?,?)"; //修改了占位符数量
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("sss", $type, $reporter, $description); //修改了参数类型和数量
$stmt->execute();</code>

<.> 2. Implement a whitelist filtering

For query parts such as keywords, identifiers or operators, please use the white list method. Filter these variables through the predefined allowable value list:

<code class="language-php">$type = 'testing';
$reporter = "John O'Hara";
$description = 'whatever'; //添加了description变量
$sql = "INSERT INTO contents (type,reporter,description) VALUES (?,?,?)"; //修改了占位符数量
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("sss", $type, $reporter, $description); //修改了参数类型和数量
$stmt->execute();</code>

Make sure to correctly format the identifier in accordance with the database syntax (for example, the anti -attraction number of MySQL). Then, this variable is safely included in the SQL string:

Filter the pre -processing statement and a whitelist, you can effectively prevent SQL from injecting attacks, and safely integrate PHP variables into your MySQL query. Please note that the

<code class="language-php">$type = 'testing';
$reporter = "John O'Hara";
$description = 'whatever'; //添加了description变量
$sql = "INSERT INTO contents (type,reporter,description) VALUES (?,?,?)"; //修改了占位符数量
$stmt = $pdo->prepare($sql);
$stmt->execute([$type, $reporter, $description]); //修改了参数数量</code>

function has been abandoned. It is recommended to use

or . The code for example has been updated to reflect this and repair the errors in the original example.

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