php SQL 语法：将变量传递给 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4372408/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL syntax: passing variable to SQL query
提问by Katherine
This is my first program and I have done extensive research trying to answer this question and I can not solve the problem:
这是我的第一个程序，我做了大量研究试图回答这个问题，但我无法解决这个问题：
<?php
include "connect.php";
if (!$connection)
{
die('Could not connect: ' . mysql_error());
}
$submit = $_POST["submit"];
if ($submit=="Submit") {
$date = $_POST["date"];
$name = $_POST["name"];
$activity = $_POST["activity"];
$activity_level = $_POST["activity_level"];
$find_role = ("SELECT sales_role
FROM role
LEFT JOIN USER on user.role_id = role.id
WHERE user.user = '$name'");
$find_activity_points = ("SELECT $activity_$role
FROM $activity
WHERE activity_level = '$activity_level'");
$role = mysql_query($find_role);
$activity_points = mysql_query($find_activity_points);
if ($activity_points !== false) {
}
else {
echo mysql_error ();
die;
}
$convert_activity_points = array();
while ($row = mysql_fetch_array($activity_points, MYSQL_ASSOC)) {
$convert_activity_points[] = $row;
}
$set_points = "UPDATE $name SET $activity='$convert_activity_points' WHERE day='$date'";
mysql_query($set_points);
}
mysql_close($connection);
?>
And this is the error message that I get when I submit form.php to update.php: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM dial WHERE activity_level='70'' at line 1
这是我将 form.php 提交到 update.php 时收到的错误消息：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行的“FROM dial WHERE activity_level='70'”附近使用的正确语法
P.S. I know there are SQL injection vulnerabilities, but I am the only person using this program and I am using it locally on my computer. I'm okay with the vulnerabilities for now.
PS 我知道有 SQL 注入漏洞，但我是唯一一个使用这个程序的人，我在我的计算机上本地使用它。我现在可以接受这些漏洞。
Edit: Changed code (I really appreciate the feedback)
编辑：更改代码（我非常感谢反馈）
$find_role = "SELECT sales_role FROM role LEFT JOIN USER on user.role_id=role.id WHERE user.user='$name'";
$find_activity_points = "SELECT %s_%s FROM $activity WHERE activity_level='%d'";
list($role) = mysql_fetch_array(mysql_query($find_role));
$activity_points = mysql_query(
sprintf($find_activity_points, //the main string
$activity, $role, $activity, $activity_level) //the "arguments"
);
if ($activity_points !== false) {
}
else {
echo mysql_error ();
die;
}
Updates the table with 0 and doesn't display an error msg. Re SQL injection vulnerabilities, see my original PS statement
用 0 更新表并且不显示错误消息。重新SQL注入漏洞，看我原来的PS语句
回答by Jordan S. Jones
On this line:
在这一行：
$find_activity_points = ("SELECT $activity_$role FROM $activity WHERE activity_level='$activity_level'");
I can't see anywhere in your code that you define the value of $roleprior to the execution of the query.
我在您的代码中看不到您$role在执行查询之前定义的值的任何地方。
So, assuming that $activity == 'Eating'your SELECTstatement may look something like this:
因此，假设$activity == 'Eating'您的SELECT语句可能如下所示：
SELECT Eating_$role FROM Eating WHERE activity_level='...'
This may lead to your error as I believe MySQL doesn't allow for $characters in table/column names.
这可能会导致您的错误，因为我相信 MySQL 不允许$在表/列名称中使用字符。
As Babikerhas suggested, you concatenating or using sprintf() can help to resolve some of these various issues.
正如Babiker所建议的那样，您连接或使用 sprintf() 可以帮助解决其中的一些问题。
Edit: Answering your question in the comments.
编辑：在评论中回答您的问题。
As other's have mentioned, change your $find_activity_pointsline to the something similar to the following:
正如其他人所提到的，将您的$find_activity_points线路更改为类似于以下内容：
$find_activity_points = ("SELECT " . $activity . "_" . $role . " FROM " . $activity . " WHERE activity_level='" . $activity_level . "'");
One thing to keep in mind, is when you do SQL statement concatenation ("SELECT " . $activity . ") like in my example, you open yourself up SQL Injectionattacks.
要记住的一件事是，当您像在我的示例中一样执行 SQL 语句连接 ("SELECT" . $activity . ") 时，您会打开自己的SQL 注入攻击。
回答by aaronofleonard
$role isn't defined until later in your program, and even then it's a non-scalar value which will ruin your query as well. You're trying to use the variables before they have values. They don't work that way, once that string is set it will contain the values of those variables at the time it was declared.
$role 直到您的程序稍后才定义，即使这样它也是一个非标量值，它也会破坏您的查询。您试图在变量具有值之前使用它们。它们不是那样工作的，一旦设置了该字符串，它将包含声明时这些变量的值。
You probably want to use sprintf. http://php.net/manual/en/function.sprintf.php
您可能想使用 sprintf。 http://php.net/manual/en/function.sprintf.php
$find_activity_points = "SELECT %s_%s FROM %s WHERE activity_level='%d'";
And then
进而
$activity_points = mysql_query(
sprintf($find_activity_points, //the main string
$activity, $role, $activity, $activity_level) //the "arguments"
);
Of course, you still need to get $roleto be a (scalar) value that can be handled properly within the string. One (somewhat unsafe, but quick) way to do this would be:
当然，您仍然需要$role成为可以在字符串内正确处理的（标量）值。一种（有点不安全，但很快）的方法是：
list($role) = mysql_fetch_array(mysql_query($find_role));
回答by Hoàng Long
At the first glance, I think that "maybe" activity_level data type is not string, but number. So you don't need to place '...' in WHERE activity_level = '$activity_level'
乍一看，我认为“可能”activity_level 数据类型不是字符串，而是数字。所以你不需要在 WHERE activity_level = '$activity_level' 中放置 '...'
Edit: to be sure about what causes problems, I recommend logging, like the example here. Just include the class and log your query in a file (very easy), then examine the query itself.
编辑：为了确定导致问题的原因，我建议记录，就像这里的例子一样。只需包含该类并将您的查询记录在一个文件中（非常简单），然后检查查询本身。
回答by Babiker
Concatenating php strings to mysql strings eg:
将 php 字符串连接到 mysql 字符串，例如：
mysql_query("SELECT * FROM `someTable` WHERE `someValue`='".$someVar'");
- And
$_POST["activity_level"]is a string with'in it.
- 并且
$_POST["activity_level"]是一个字符串'。
Solution:
解决方案：
"SELECT `".$activity_$role."` FROM `".$activity."` WHERE `activity_level`='".mysql_real_escape_string($activity_level)."'"
