如何在 PHP 中将变量传递给 MySQL 查询? [英] How do I pass variables into a MySQL Query in PHP?
问题描述
我有以下 MySQL 命令试图获取用户的登录信息:
I have the following MySQL command that tries to get login information for a user:
$requete = "SELECT login, pass FROM membres WHERE login='$login' AND pass='$pass'";
echo ("$requete");
它不起作用.问题是，在执行此代码时，我看到以下输出(而不是执行查询的结果):
It's not working. The problem is that, upon executing this code, I see the following output (rather than the result of executing the query):
SELECT login, pass FROM membres WHERE login='$login' AND pass='$pass'
在 PHP 中将变量传递给 MySQL 查询的正确方法是什么?
What is the correct way to pass variables into a MySQL query in PHP?
推荐答案
在 PHP 中，与许多其他编程语言一样，= 运算符 赋值右侧操作数的值到左侧操作数变量.也就是说，在执行这样的命令后，= 符号左侧命名的变量将包含 = 符号右侧表达式的值.
In PHP, as in many other programming languages, the = operator assigns the value of the right-hand operand to the left-hand operand variable. That is, after such a command is executed, the variable named on the left of the = symbol will contain the value of the expression on the right of the = symbol.
你的右手操作数是一个 双引号字符串字面量，其中 PHP 用它们的值扩展(替换)变量；然而，结果(分配给左边的操作数)只是一个字符序列.
Your right-hand operand is a double-quoted string literal, in which PHP expands (replaces) variables with their values; however, the result (which is assigned to the left-hand operand) is merely a sequence of characters.
PHP 的 echo 构造转换其参数(s) 到字符串，按顺序连接它们并将结果发送到输出缓冲区.
PHP's echo construct converts its parameter(s) to string(s), concatenates them in order and sends the result to the output buffer.
在您的情况下，唯一的参数是另一个双引号字符串文字 "$requete"，其中 PHP 再次使用它们的值扩展变量:这次文字的内容仅仅是变量 $requete，因此引用它只会导致多余的解析开销；并且由于该变量被分配了先前的字符串文字，因此结果字符串(由 echo 输出)等于原始字符串文字.因此，您的两个语句具有相同的效果:
In your case, the sole parameter is another double-quoted string literal "$requete", in which PHP again expands variables with their values: this time the content of the literal is solely the variable $requete, thus quoting it just caused superfluous parsing overhead; and since that variable was assigned the previous string literal, the resulting string (that is output by echo) is equal to the original string literal. Your two statements therefore have the same effect as:
echo "SELECT login, pass FROM membres WHERE login='$login' AND pass='$pass'";
PHP 没有被告知 RDBMS 存在，这是您希望由该 RDBMS 评估的 SQL，或者您希望输出产生的结果集.
PHP has not been informed that an RDBMS exists, that this is SQL which you wish to be evaluated by that RDBMS, or that you wish for the arising resultset to be output.
更糟糕的是，除非您绝对确定$login 和$pass 不包含未转义的' 字符，为 SQL 解析该字符串可能会导致意外行为. 攻击者经常利用这种疏忽来执行 SQL 注入攻击，这可能会危及您的整个数据库.避免这种情况的最安全方法是从不评估 SQL 的变量，而是使用 准备好的语句，您将变量作为参数传递到其中.
Worse, unless you are absolutely certain that $login and $pass do not contain unescaped ' characters, parsing that string for SQL could result in unexpected behaviour. Attackers often exploit this oversight to perform SQL injection attacks, which can compromise your entire database. The safest way to avoid this is never to evaluate variables for SQL, by instead using prepared statements into which you pass your variables as parameters.
我建议您查看 PHP 数据对象，它可以确保安全数据库访问非常简单:
I suggest you look into PHP Data Objects, which make safe database access very easy:
$dbh = new PDO("mysql:dbname=$dbname;charset=utf8", $username, $password);
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$qry = $dbh->prepare('
SELECT login, pass
FROM membres
WHERE login = ? AND pass = ?
');
$qry->execute(array($login, $pass));
while ($row = $qry->fetch(PDO::FETCH_ASSOC)) print_r($row);
不过，有几点需要注意:
However, some things to note:
你只是从匹配常量值的数据库字段中选择，这是毫无意义的:从这样的查询中可以获得的唯一信息是数据库中匹配记录的数量，但资源浪费在返回多余的数据.
You're only selecting from your database fields that match constant values, which is rather pointless: the only information one can obtain from such a query is the number of matching records in the database, but resources are wasted in returning superfluous data.
您可以改为执行 SELECT COUNT(*) FROM membres WHERE ... 以获得相同的信息，而不会浪费任何开销；或者如果您只想发现是否有一个或多个匹配记录，SELECT EXISTS (SELECT * FROM membres WHERE ...) 会更有效.
You could instead do SELECT COUNT(*) FROM membres WHERE ... to obtain the same information without any wasted overhead; or if you only wish to discover whether there is one or more matching records, SELECT EXISTS (SELECT * FROM membres WHERE ...) would be even more efficient.
您似乎正在创建一个登录脚本，以安全的方式完成该脚本并非易事:阅读 The Definitive如果您有兴趣了解更多信息，请参阅基于表单的网站身份验证指南.您可以将一些库和工具加入到您的项目中，从而避免在重新发明轮子上浪费资源(以及其他人通过艰难的方式学到的所有痛苦的错误).
You appear to be creating a login script, which is non-trivial to accomplish in a secure manner: read The Definitive Guide To Forms based Website Authentication if you're interested in finding out more. There are libraries and tools out there which you can drop-in to your project to avoid you wasting resource on reinventing the wheel (and all the many painful mistakes others learnt the hard way).
这篇关于如何在 PHP 中将变量传递给 MySQL 查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
