我应该如何在 mysql_query 函数中编写 PHP $_POST 变量？

How should I write PHP $_POST vars in a mysql_query function?

提问by Julian H. Lam

In accessing my database, I have the user fill out a form, and in the target page, the posted values are used in the resulting MySQL query.

在访问我的数据库时，我让用户填写一个表单，在目标页面中，发布的值用于生成的 MySQL 查询。

$query = mysql_query("SELECT pass FROM database WHERE user='$_POST[user]'");

However, for some reason or another, MySQL doesn't like my using a $_POST variable in the command, and it only works if I define (for example) $user = $_POST['user'];, and then put $user directly in the SQL command.

但是，出于某种原因，MySQL 不喜欢我在命令中使用 $_POST 变量，它仅在我定义（例如）$user = $_POST['user'];，然后将 $user 直接放入 SQL 命令时才有效。

On the other hand, I can use $_POST values in INSERT statements where specific column names are not required:

另一方面，我可以在不需要特定列名的 INSERT 语句中使用 $_POST 值：

$query = mysql_query("INSERT INTO database VALUES ('foo', 'bar', '$_POST[user]'");

If I try an INSERT statement where attributes are defined (e.g. user='foo'), then the same problem appears.

如果我尝试定义属性的 INSERT 语句（例如user='foo'），则会出现相同的问题。

What am I doing wrong in my SQL query that causes the command to error out when run, but works with the specific method of formatting an INSERT command?

我在 SQL 查询中做错了什么导致命令在运行时出错，但使用格式化 INSERT 命令的特定方法？

Hopefully, it's not "tough luck, looks like you have to assign all of your posted values". Heh.

希望这不是“运气不好，看起来您必须分配所有发布的值”。呵呵。

回答by Alix Axel

First of, watch out for SQL Injections!

首先，注意 SQL 注入！

Now, to answer your question try doing this instead:

现在，要回答您的问题，请尝试这样做：

$query = mysql_query("SELECT `pass` FROM `database` WHERE `user` LIKE '" . mysql_escape_string($_POST['user']) . "';");

You were doing a couple of things wrong:

你做错了几件事：

• using the =operator instead of LIKEoperator
• not enclosing the value in the SQL query with '
• not enclosing the user index in the $_POSTarray with '

• 使用=运算符代替LIKE运算符
• 不包含在 SQL 查询中的值 '
• 不将用户索引包含在$_POST数组中'

PS: You should use mysql_real_escape_string()instead of mysql_escape_string()!

PS：你应该使用mysql_real_escape_string()而不是mysql_escape_string()！

回答by JAL

You're simply inserting a variable into a string, so it shouldn't matter which command you're putting it into.

您只是将变量插入到字符串中，因此将其放入哪个命令无关紧要。

There are a few issues to point out.

有几个问题需要指出。

One, you might want to use the {} format for array variables. You don't use quotes around the arrray key names in this format.

一，您可能希望对数组变量使用 {} 格式。在这种格式中，您不要在数组键名称周围使用引号。

$query = mysql_query("SELECT pass FROM database WHERE user='{$_POST[user]}'")

Two, you'd never want to make a query like thatbecause you are open to sql injection holes. Consider, what if $_POST['user'] was "cow';drop table database;--"?

第二，您永远不想进行这样的查询，因为您对 sql 注入漏洞持开放态度。考虑一下，如果 $_POST['user'] 是“cow';drop table database;--”呢？

You must either run mysql_real_escape_string on the POST input before putting it into your query, or check out using PHP PDOwith prepared statements.

在将 POST 输入放入查询之前，您必须在 POST 输入上运行 mysql_real_escape_string，或者使用带有准备好的语句的PHP PDO进行检查。

One way to do format your string which provides a bit of structure is to use sprintf.

格式化提供一些结构的字符串的一种方法是使用 sprintf。

$query=mysql_query(sprintf("SELECT pass FROM database WHERE user='%s'",mysql_real_escape_string($_POST['user'])));

回答by Crozin

1. Use PDO- it provides much better API to communicate with DB.
2. If you're using mysql_*()functions always remember to filter(mysql_real_escape_string()) any data that comes from untrusted source (like user)

3. Pay more attention to how your code looks like. Just compare the following listings:

   $query = mysql_query("INSERT INTO database VALUES ('foo', 'bar', " . mysql_real_escape_string($_POST['user']) . ", " . mysql_real_escape_string($_POST['user']) . ", " . mysql_real_escape_string($_POST['user']) . ", " . mysql_real_escape_string($_POST['user']) . ")");
   
   $query = sprinf('INSERT INTO database VALUES ("foo", "bar", "%s", "%s", "%s")',
   mysql_real_escape(...), ...);
   

   Do I have to explain which one is better to read, modify or understand?

1. 使用PDO- 它提供了更好的 API 来与 DB 通信。
2. 如果您正在使用mysql_*()函数，请务必记住过滤（mysql_real_escape_string()）来自不受信任来源（如用户）的任何数据

3. 更加注意您的代码的外观。只需比较以下列表：

   $query = mysql_query("INSERT INTO database VALUES ('foo', 'bar', " . mysql_real_escape_string($_POST['user']) . ", " . mysql_real_escape_string($_POST['user']) . ", " . mysql_real_escape_string($_POST['user']) . ", " . mysql_real_escape_string($_POST['user']) . ")");
   
   $query = sprinf('INSERT INTO database VALUES ("foo", "bar", "%s", "%s", "%s")',
   mysql_real_escape(...), ...);
   

   我是否必须解释哪个更适合阅读、修改或理解？

回答by Marc B

Why not check and see what mysql_error() has to say about it? If your query is invalid, mysql_error() will return a nice blob of text telling you exactly what went wrong.

为什么不检查并查看 mysql_error() 对此有何评论？如果您的查询无效，mysql_error() 将返回一个漂亮的文本块，告诉您究竟出了什么问题。

As for MySQL not liking the POST var if you insert it directly for some runs, but not others, then you should make sure you're using consistent data and setups for each test. If some test are done using a GET, then your POST vars will be empty. If you're using different user names for each test, then see if what's consistent between the ones that fail.

至于 MySQL 不喜欢 POST var，如果您直接将其插入某些运行，而不喜欢其他运行，那么您应该确保为每个测试使用一致的数据和设置。如果某些测试是使用 GET 完成的，那么您的 POST 变量将为空。如果您为每个测试使用不同的用户名，那么查看失败的用户名之间是否一致。

And as mentioned above, read up about SQL injection and how your query is just begging to be subverted by a malicious user.

如上所述，阅读有关 SQL 注入的信息，以及您的查询是如何被恶意用户破坏的。

回答by jtm

Try

尝试

$query = mysql_query("SELECT pass FROM database WHERE user=" . mysql_real_escape_string($_POST['user']));

and

和

$query = mysql_query("INSERT INTO database VALUES ('foo', 'bar', " . mysql_real_escape_string($_POST['user']) . ")");

Its always a good idea to sanitize anything received through $_GET or $_POST

对通过 $_GET 或 $_POST 收到的任何内容进行消毒总是一个好主意