How to include a PHP variable in a MySQL statement

Question

I'm trying to insert values ​​into a table of contents. It works fine if there are no PHP variables in VALUES. However, when I put the variable $type in VALUES, it doesn't work. What did i do wrong?

$type = 'testing';
mysql_query("INSERT INTO contents (type, reporter, description) 
     VALUES($type, 'john', 'whatever')");

Answer 1

To avoid SQL injection, the insert statement will be:

$type = 'testing';
$name = 'john';
$description = 'whatever';

$con = new mysqli($user, $pass, $db);
$stmt = $con->prepare("INSERT INTO contents (type, reporter, description) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $type , $name, $description);
$stmt->execute();

Answer 2

The rules for adding PHP variables in any MySQL statement are simple and straightforward:

1. Use prepared statements

This rule applies to 99% of queries, and it also applies to your query. Any variable that represents a SQL data literal (or simply, a SQL string or number) must be added via a prepared statement. Without exception.

This method consists of four basic steps:

• In your SQL statement, replace all variables with placeholders
• Prepare the obtained query
Bind variables to placeholders
Execute query

Here's how to achieve this in all popular PHP database drivers:

Use

mysqliAdd data literal

Current PHP versions allow you to prepare/bind/execute in a single call:

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

If you have an older version of PHP, you must do prepare/bind/execute explicitly:

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

This code is a bit complex, but a detailed explanation of all these operators can be found in my article

How to run an INSERT query using Mysqli, as well as a solution that can greatly simplify the process.

For SELECT queries, you can use the same method as above:

$reporter = "John O'Hara";
$result = $mysqli->execute_query("SELECT * FROM users WHERE name=?", [$reporter]);
$row = $result->fetch_assoc(); // 或者 while (...)

However, if you have an older version of PHP, you will need to do the prepare/bind/execute routine, and also call the

get_result() method in order to get a familiar from it mysqli_result, from which data can be extracted in the usual way:

$reporter = "John O'Hara";
$stmt = $mysqli->prepare("SELECT * FROM users WHERE name=?");
$stmt->bind_param("s", $reporter);
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc(); // 或者 while (...)

Use PDO to add data literals

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

In PDO, we can merge the binding and execution parts together, which is very convenient. PDO also supports named placeholders, which some people find very convenient.

2. Use whitelist filtering

Any other query parts, such as SQL keywords, table or field names, or operators, must be filtered through a

whitelist .

Sometimes we have to add a variable that represents another part of the query, such as a keyword or identifier (database, table or field name). This is a rare situation, but it's best to be prepared.

In this case, your variable must be checked against the list of values ​​

explicitly written in your script. This is explained in detail in my other articleAdd field names in the ORDER BY clause based on user selection:

This is an example:

$orderby = $_GET['orderby'] ?: "name"; // 设置默认值
$allowed = ["name","price","qty"]; // 允许的字段名的白名单
$key = array_search($orderby, $allowed, true); // 看看是否有这个名字
if ($key === false) { 
    throw new InvalidArgumentException("无效的字段名"); 
}

$direction = $_GET['direction'] ?: "ASC";
$allowed = ["ASC","DESC"];
$key = array_search($direction, $allowed, true);
if ($key === false) { 
    throw new InvalidArgumentException("无效的ORDER BY方向"); 
}

After code like this, the

$direction and $orderby variables can be safely put into the SQL query because they will either be equal to one of the allowed variants or they will throw An error occurred.

The last thing to mention is that identifiers must also be formatted according to the specific database syntax. For MySQL, the identifier should be surrounded by

backtick characters. So the final query string for our ORDER BY example should be:

$query = "SELECT * FROM `table` ORDER BY `$orderby` $direction";