查询返回意外结果 [英] query returns unexpected results

问题描述

我觉得此搜索查询无法正常运行.我想确定选择的唯一项目是ksisoldby与$user相同(尽管不区分大小写，因此是ILIKE).用AND s和OR s指定的其他字段是缩小选择范围的可能方法.

I feel like this search query is not working as I imagined it would. I want to be certain that the only items selected are those with ksisoldby identical (though case-insensitive, thus ILIKE) to $user. And the the other fields specified with the ANDs and ORs are possible ways this selection could be narrowed down.

"SELECT 
id,
status,
customer_id,
shipping_address_id,
order_number,
reference,
ship_via_id,
order_date :: date,
due_date :: date,
created,
sales_tax,
freight,
taxable,
nontaxable,
job_name,
order_description,
ship_to_name,
ship_to_address1,
ship_to_address2,
ship_to_city,
ship_to_state,
ship_to_zipcode,
name,
address1,
address2,
city,
state,
zipcode,
act_ship_date,
ksisoldby
FROM sales_orders 
WHERE ksisoldby ILIKE '".$user."'
AND (order_description ilike 
'%".implode("%' AND order_description like '%", $search)."%')
OR (order_number ilike 
'%".implode("%' AND order_number like '%", $search)."%')
OR (name ilike 
'%".implode("%' AND name like '%", $search)."%')
OR (reference ilike 
'%".implode("%' AND name like '%", $search)."%')
ORDER BY order_number DESC";

我是否正确执行了此操作，是我的数据没有正确设置，还是这些AND和/或OR语句覆盖了WHERE子句?感谢您的帮助.

Have I done this correctly, and it is my data that is not set up correctly, or are these AND and/or OR statements overriding the WHERE clause? Thanks for the help.

推荐答案

此处的关键字是

The keyword here is operator precedence. AND binds before OR.

WHERE ksisoldby ILIKE '".$user."'
AND (
     order_description ilike '%".implode("%'
     AND order_description like '%", $search)."%')
  OR order_number ilike '%".implode("%'
     AND order_number like '%", $search)."%'
  OR name ilike '%".implode("%'
     AND name like '%", $search)."%'
  OR reference ilike '%".implode("%'
     AND name like '%", $search)."%'
    )

WHERE子句中的所有项目都被评估为一个布尔结果.如果你

All items in the WHERE clause are evaluated to one boolean result. If you

要确定唯一选择的项目是那些与$ user相同的ksisoldby

want to be certain that the only items selected are those with ksisoldby identical [...] to $user

然后，您必须将OR的标准包装在括号中，否则它们将提供替代方式进行资格认证.

then you have to wrap the OR'ed criteria into parenthesis or they will offer alternative ways to qualify.

另一方面，您可以像我演示的那样删除AND ed对周围的括号.

On the other hand, you can remove the parenthesis around the AND'ed pairs like I demonstrate.

我添加了(在语法上无关紧要的)空白和换行符，以使其更清晰.

I added (syntactically irrelevant) white space and line breaks to make it clearer.

顺便说一句-正如注释中提到的那样-如果您不希望表中的所有列都列出，则列出您想要的列(如您拥有的列)是最佳方法.

BTW - as this came up in the comments - if you don't want all columns from the table, listing the ones you want (like you have it) is the optimal way.

我还考虑使用 准备好的声明 (或带有参数的服务器端函数)，而不是构建一个大型查询字符串来防止SQL注入.您的框架可能提供了一些方法来执行此操作.或者，您也可以手动操作.

I would also consider to use prepared statements (or server-side functions with parameters) instead of building one big query strings to prevent SQL injection. Your framework probably offers some way to do this. Or you can do it manually.

在继续构建查询字符串时，请使用 quote_literal() 清理用户输入.

While you keep building query strings, use quote_literal() to sanitize user input.

这篇关于查询返回意外结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！