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;

To see what's happening, 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和带变量的"WHERE"的使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！