如何在 MySQL 语句中包含 PHP 变量 [英] How to include a PHP variable inside a MySQL statement

问题描述

我正在尝试在内容表中插入值.如果我在 VALUES 中没有 PHP 变量，它就可以正常工作.当我将变量 $type 放在 VALUES 中时，这不起作用.我做错了什么?

$type = '测试';mysql_query("插入内容(类型、报告者、描述)VALUES($type, 'john', 'whatever')");

解决方案

在任何 MySQL 语句中添加 PHP 变量的规则很简单:

1. 任何表示SQL 数据字面量的变量(或者，简单地说 - SQL 字符串或数字)都必须通过准备好的语句添加.没有例外.
2. 任何其他查询部分，例如 SQL 关键字、表或字段名称或运算符 - 必须通过白名单过滤.

因此，由于您的示例仅涉及数据文字，因此必须通过占位符(也称为参数)添加所有变量.这样做:

• 在您的 SQL 语句中，将所有变量替换为 placeholders
• 准备结果查询
• 将变量绑定到占位符
• 执行查询

这里是如何使用所有流行的 PHP 数据库驱动程序来做到这一点:

使用 mysql ext 添加数据字面量

这样的驱动不存在.>

使用mysqli

添加数据文字

$type = '测试';$reporter = "John O'Hara";$query = "INSERT INTO 内容(类型、报告者、描述)VALUES(?, ?, 'whatever')";$stmt = $mysqli->prepare($query);$stmt->bind_param("ss", $type, $reporter);$stmt->execute();

代码有点复杂但是所有这些操作符的详细解释可以在我的文章中找到，如何运行使用 Mysqli 的 INSERT 查询，以及显着简化流程的解决方案.

对于 SELECT 查询，您只需要添加对 get_result() 方法的调用即可获得熟悉的 mysqli_result，您可以通过该方法以通常的方式获取数据:

$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 (...)

使用 PDO 添加数据文字

$type = '测试';$reporter = "John O'Hara";$query = "INSERT INTO 内容(类型、报告者、描述)VALUES(?, ?, 'whatever')";$stmt = $pdo->prepare($query);$stmt->execute([$type, $reporter]);

在PDO中，我们可以将绑定和执行部分结合起来，非常方便.PDO 还支持命名占位符，有些人觉得这非常方便.

添加关键字或标识符

有时我们必须添加一个变量来表示查询的另一部分，例如关键字或标识符(数据库、表或字段名称).这种情况很少见，但最好做好准备.

在这种情况下，您的变量必须根据明确写入脚本的值列表进行检查.这在我的另一篇文章 根据用户的选择在 ORDER BY 子句中添加字段名称 中有解释:

<块引用>

不幸的是，PDO 没有标识符(表和字段名称)的占位符，因此开发人员必须手动将它们过滤掉.这种过滤器通常被称为白名单".(我们只列出允许的值)而不是黑名单"；我们列出了不允许的值.

因此我们必须明确列出 PHP 代码中所有可能的变体，然后从中进行选择.

这是一个例子:

$orderby = $_GET['orderby'] ?: "name";//设置默认值$allowed = [名称"、价格"、数量"];//允许的字段名称的白名单$key = array_search($orderby, $allowed, true);//看看我们有没有这样的名字如果($key === false){throw new InvalidArgumentException(无效字段名称");}

<块引用>

方向应该使用完全相同的方法，

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

在这样的代码之后，$direction 和 $orderby 变量都可以安全地放入 SQL 查询中，因为它们要么等于允许的变体之一，要么会抛出错误.

关于标识符的最后一件事，它们也必须根据特定的数据库语法进行格式化.对于 MySQL，它应该是标识符周围的 backtick 字符.因此，我们的订单示例的最终查询字符串将是

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

I'm trying to insert values in the contents table. It works fine if I do not have a PHP variable inside VALUES. When I put the variable $type inside VALUES then this doesn't work. What am I doing wrong?

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

解决方案

The rules of adding a PHP variable inside of any MySQL statement are plain and simple:

1. Any variable that represents an SQL data literal, (or, to put it simply - an SQL string, or a number) MUST be added through a prepared statement. No exceptions.
2. Any other query part, such as an SQL keyword, a table or a field name, or an operator - must be filtered through a white list.

So as your example only involves data literals, then all variables must be added through placeholders (also called parameters). To do so:

• In your SQL statement, replace all variables with placeholders
• prepare the resulting query
• bind variables to placeholders
• execute the query

And here is how to do it with all popular PHP database drivers:

Adding data literals using mysql ext

Such a driver doesn't exist.

Adding data literals using mysqli

$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();

The code is a bit complicated but the 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 eases the process dramatically.

For a SELECT query you will need to add just a call to get_result() method to get a familiar mysqli_result from which you can fetch the data 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(); // or while (...)

Adding data literals using PDO

$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 have the bind and execute parts combined, which is very convenient. PDO also supports named placeholders which some find extremely convenient.

Adding keywords or identifiers

Sometimes we have to add a variable that represents another part of a query, such as a keyword or an identifier (a database, table or a field name). It's a rare case but it's better to be prepared.

In this case, your variable must be checked against a list of values explicitly written in your script. This is explained in my other article, Adding a field name in the ORDER BY clause based on the user's choice:

Unfortunately, PDO has no placeholder for identifiers (table and field names), therefore a developer must filter them out manually. Such a filter is often called a "white list" (where we only list allowed values) as opposed to a "black-list" where we list disallowed values.

So we have to explicitly list all possible variants in the PHP code and then choose from them.

Here is an example:

$orderby = $_GET['orderby'] ?: "name"; // set the default value
$allowed = ["name","price","qty"]; // the white list of allowed field names
$key = array_search($orderby, $allowed, true); // see if we have such a name
if ($key === false) { 
    throw new InvalidArgumentException("Invalid field name"); 
}

Exactly the same approach should be used for the direction,

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

After such a code, both $direction and $orderby variables can be safely put in the SQL query, as they are either equal to one of the allowed variants or there will be an error thrown.

The last thing to mention about identifiers, they must be also formatted according to the particular database syntax. For MySQL it should be backtick characters around the identifier. So the final query string for our order by example would be

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

这篇关于如何在 MySQL 语句中包含 PHP 变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！