MySQL查询与PHP MySQL查询。

I'm trying to do a MySQL query in PHP with some special characters. It seems to work if I run the query on my database with straight SQL:

我尝试用PHP中的一些特殊字符进行MySQL查询。如果我用SQL语句在我的数据库上运行查询，它似乎是有效的:

SELECT SUM(quantity_ordered)  FROM `shopping_cart` 
   WHERE `cart_number` = 10316027
   AND `size` IN ('5&#188" x 8&#188"','5&#8540;" x 7&#190;"','4&#189;" x 9&#189;"')

The above query returns the expected result and SUM but when I put it in my prepared PHP query it returns no records or SUM.

上面的查询返回预期的结果和SUM，但是当我将它放入我准备好的PHP查询中时，它不会返回任何记录或总和。

I suspect that it has to do with the single quotes around each size but if I remove them I get a MySQL error. A similar query in my PHP with straight numbers and no surrounding quotes works fine.

我怀疑它与每个大小的单引号有关，但是如果我删除它们，我就会得到一个MySQL错误。在我的PHP中有一个类似的查询，它的数字是直的，没有周围的引号可以正常工作。

I've tried different ways of escaping the special characters but I'm wondering if this query will work at all with these types of characters?

我尝试了不同的方法来逃避特殊字符，但是我想知道这个查询是否会对这些类型的字符起作用?

2 个解决方案

#1

You problem is you are probably not escaping the double quotes in your PHP string.

您的问题是，您可能无法避免PHP字符串中的双引号。

try this

试试这个

$qry = "SELECT SUM(quantity_ordered)  FROM `shopping_cart` 
WHERE `cart_number` = 10316027
AND `size` IN ('5&#188\" x 8&#188\"','5&#8540;\" x 7&#190;\"','4&#189;\" x 9&#189;\"')"

#2

Based on your comment about the prepared statement:

基于你对准备好的声明的评论:

SELECT SUM(quantity_ordered) FROM wholesale_shopping_cart WHERE cart_number = :cart_number AND item_number IN ($items)

You should build your $items array with individual bound variables so that it would look like:

您应该使用单个绑定变量来构建$items数组，以便它看起来像:

SELECT SUM(quantity_ordered) FROM wholesale_shopping_cart
WHERE
       cart_number = :cart_number
   AND item_number IN (:val1, :val2, :val3)

Then you can bind your variables and execute the query.

然后，您可以绑定您的变量并执行查询。

If you put your variable directly in your sql statement, you will (probably...) have an sql injection problem and you would need to escape your quotes correctly.

如果您将变量直接放入sql语句中，您将(可能…)有sql注入问题，您需要正确地转义引号。

#1