如何在 MySQL 语句中包含 PHP 变量

How to include a PHP variable inside a MySQL statement

提问by Pinkie

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 $typeinside VALUESthen this doesn't work. What am I doing wrong?

我正在尝试在内容表中插入值。如果我在 VALUES 中没有 PHP 变量，它工作正常。当我把变量$type放在里面时，VALUES这不起作用。我究竟做错了什么？

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

回答by Your Common Sense

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

在任何 MySQL 语句中添加 PHP 变量的规则简单明了：

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.

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

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
• preparethe resulting query
• bindvariables to placeholders
• executethe query

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

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

以下是如何使用所有流行的 PHP 数据库驱动程序执行此操作：

Adding data literals using mysql_query

使用 mysql_query 添加数据文字

Such a driver doesn't exist.

不存在这样的驱动程序。

Adding data literals using mysqli

使用添加数据文字 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.

代码有点复杂，但可以在我的文章How to run an INSERT query using Mysqli 中找到所有这些运算符的详细说明，以及一个可以显着简化流程的解决方案。

Adding data literals using PDO

使用 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.

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

Adding keywords or identifiers

添加关键字或标识符

But sometimes we have added a variable that represents another part of a query, such as a keyword or an identifier (a database, table or a field name). In this case, your variable must be checked against a list of values explicitlywritten 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:

但有时我们会添加一个变量来表示查询的另一部分，例如关键字或标识符（数据库、表或字段名称）。在这种情况下，必须根据脚本中明确写入的值列表检查变量。这在我的另一篇文章“根据用户的选择在 ORDER BY 子句中添加字段名称”中进行了解释：

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.

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

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

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 $directionand $orderbyvariables 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.

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

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

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

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

回答by Stefan

To avoid SQL injection the insert statement with be

为避免 SQL 注入，插入语句为

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

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

回答by Tel

The best option is prepared statements.Messing around with quotes and escapes is harder work to begin with, and difficult to maintain. Sooner or later you will end up accidentally forgetting to quote something or end up escaping the same string twice, or mess up something like that. Might be years before you find those type of bugs.

最好的选择是准备好的语句。搞乱引号和转义是更难开始的工作，并且难以维护。迟早你会不小心忘记引用某些东西，或者两次转义同一个字符串，或者搞砸类似的事情。可能需要数年时间才能找到这些类型的错误。

http://php.net/manual/en/pdo.prepared-statements.php

http://php.net/manual/en/pdo.prepared-statements.php

回答by Spencer Rose

The text inside $type is substituted directly into the insert string, therefore MySQL gets this:

$type 中的文本被直接替换为插入字符串，因此 MySQL 得到：

... VALUES(testing, 'john', 'whatever')

Notice that there are no quotes around testing, you need to put these in like so:

请注意，测试周围没有引号，您需要像这样放置它们：

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

I also recommend you read up on SQL injection, as this sort of parameter passing is prone to hacking attempts if you do not sanitize the data being used:

我还建议您阅读SQL injection，因为如果您不清理正在使用的数据，这种参数传递很容易受到黑客攻击：

• MySQL - SQL Injection Prevention

• MySQL - SQL 注入预防

回答by Baloomba

That's the easy answer:

这是简单的答案：

$query="SELECT * FROM CountryInfo WHERE Name = '".$name."'";

and you define $namewhatever you want.
And another way, the complex way, is like that:

你可以定义$name任何你想要的。
另一种方式，复杂的方式，是这样的：

$query = " SELECT '" . $GLOBALS['Name'] . "' .* " .
         " FROM CountryInfo " .
         " INNER JOIN District " .
         " ON District.CountryInfoId = CountryInfo.CountryInfoId " .
         " INNER JOIN City " .
         " ON City.DistrictId = District.DistrictId " .
         " INNER JOIN '" . $GLOBALS['Name'] . "' " .
         " ON '" . $GLOBALS['Name'] . "'.CityId = City.CityId " .
         " WHERE CountryInfo.Name = '" . $GLOBALS['CountryName'] .
         "'";

回答by Chris Ghenea

Try this:

尝试这个：

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

You need to put '$type'not just $type

你不仅需要'$type'输入 $type

回答by Harri

If the variables contain user input or other data you can't trust, be sure to escape the data. Like this:

如果变量包含用户输入或其他您不信任的数据，请务必对数据进行转义。像这样：

$query = sprintf("INSERT INTO contents (type) VALUES ('%s')", mysql_real_escape_string($type));
$result = mysql_query($query);

回答by Harri

Here

这里

$type='testing' //it's string

mysql_query("INSERT INTO contents (type, reporter, description) VALUES('$type', 'john', 'whatever')");//at that time u can use it(for string)


$type=12 //it's integer
mysql_query("INSERT INTO contents (type, reporter, description) VALUES($type, 'john', 'whatever')");//at that time u can use $type

回答by villeguy

I know there has been a few answers to this question but I thought I would add that if you follow the following syntax, I have never had an issue with the error again. No question which table you are using and which columns you are appending.

我知道这个问题有几个答案，但我想我会补充一点，如果您遵循以下语法，我再也不会遇到错误问题了。毫无疑问，您正在使用哪个表以及您要附加哪些列。

$query    = "INSERT INTO contents (type, reporter, description) 
         VALUES('".$type."', '".$reporter."', '"$whatever."')";

回答by Astjan Selimaj

You have to write the variable in single or double quotes, then braces and then the variable name (example: $abc) inside.

你必须用单引号或双引号写变量，然后是大括号，然后是变量名（例如：$abc）。

Example:

例子：

SELECT * FROM log WHERE id = '{$id}';