PHP SQL 和 'WHERE' 的使用带有变量 [英] PHP SQL and use of 'WHERE' with a variable

问题描述

当我使用下面的代码时，我从我的数据库中得到了正确的结果.

When I used the code below I get the correct result from my database.

$jobnumber= 'Agen912-493';
$sql = 
"
SELECT *
FROM jobcards
WHERE jobnumber='$jobnumber'
";
$result = mysqli_query($conn, $sql);

但是当我交换时

$jobnumber= 'Agen912-493';

为了

$jobnumber= $_GET["jobnumber"];

我从数据库中得到无结果".但是我确定地知道$_GET["jobnumber"]; 返回完全相同的 Agen912-493 因为我在将它设置为等于 后回显了 $jobnumber>$_GET["jobnumber"]; 并返回 Agen912-493.

I get 'no results' from the database. However I know for certain that $_GET["jobnumber"]; is returning the exact same Agen912-493 because I have echoed $jobnumber after setting it equal to $_GET["jobnumber"]; and it returns Agen912-493.

经过两个小时的挠头，我完全无法理解这里出了什么问题.我想要做的就是在 WHERE 条件中使用 GET[] 调用的结果(我已经检查返回正确的字符串).

After two hours of head scratching I am at a complete loss to understand whats going wrong here. Simply all I want to do is use the result of a GET[] call (that I have checked is returning the correct string) in the WHERE condition.

有人能解释一下这里发生了什么吗?

Can anyone shed any light on what is going on here please?

推荐答案

您的代码容易受到 SQL 注入攻击.更好的模式是使用带绑定占位符的准备好的语句.

Your code is vulnerable to SQL Injection. A better pattern is to use prepared statement with bind placeholder.

(我误读了 OP 发布的内容.我的错.)

( I misread what OP posted. My bad.)

<打击>您所观察到的行为最可能的解释是缺少单引号.这要么导致错误(或者，MySQL 在数字上下文中慷慨地解释文字值，并计算为零.

The most likely explanation for the behavior you are observing is missing single quotes. That's either causing an error (or, MySQL is generously interpreting the literal value in a numeric context, and evaluating to zero.

要查看发生了什么，echo $sql;

考虑不同之处:单引号使其成为字符串文字:

Consider the difference: single quotes make it a string literal:

  ... FROM t WHERE t.mycol = 'Agen912-493'
                             ^           ^

如果没有单引号，MySQL 会认为它是一个标识符，并将破折号视为减法操作:

Without single quotes, MySQL is going to think it's an identifier, and see the dash character as a subtraction operation:

   ... FROM t WHERE t.mycol = Agen912-493 

我希望 MySQL 正在查找列名 Agen912，然后从该值中减去 493.

I expect MySQL is looking for a column name Agen912 and then subtracting 493 from that value.

最有可能的是，MySQL 正在抛出错误.和

Most likely, MySQL is throwing an error. And

您的代码没有检查 mysqli_query 的返回.它把它虚拟的小指放在它虚拟的嘴角上，邪恶博士式的，然后说:我只是假设一切都会按计划进行.什么?"

Your code isn't checking the return from mysqli_query. It's putting its virtual pinky finger to the corner of its virtual mouth, Dr.Evil-style, and going "I just assume it will all go to plan. What?"

启用错误报告并检查 mysqli_query 的返回.如果出现错误，它将返回 FALSE 而不是结果集.

Enable error reporting and check the return from mysqli_query. If there's an error, it will return FALSE rather than a result set.

更好的模式是使用带有绑定占位符的准备好的语句:

A better pattern is to use a prepared statement with a bind placeholder:

$sql = "SELECT * FROM jobcards WHERE jobnumber = ?";
$sth = mysqli_prepare($sql);
if (!$sth) {
   // error in prepare
}
else {
   mysqli_stmt_bind_param($sth, 's', $jobnumber);
   mysqli_stmt_execute($sth);
   ...  
}

这篇关于PHP SQL 和 &amp;#39;WHERE&amp;#39; 的使用带有变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！