如何使用MySQLi查询其中包含撇号的列

Question

I'm using a php MySQLi class to be more secure that MySQL, but I have a problem.

I have a column of data which contains apostrophes in it. I am comparing the column against a variable that has NO apostrophes, it's a clean string, from my url. In my old code I could easily just do a Replace(category_name, '''', '') in the SQL statement, and apostrophes wouldn't become a factor.

If I throw this into phpmyadmin, it works:

SELECT DISTINCT merchant_category
FROM products
WHERE Replace( category_name, '''', '' ) = 'childrens accessories'
ORDER BY merchant_category

But with MySQLi this is a real problem, as it won't parse them:

        $params = array();
    $params[0] = "Replace(category_name, '''', '')";
    $params[1] = $this->db->escape($this->cleanDBValue(requestQS("cat1")));

    //print_r($params);

    $rs = $this->db->rawQuery("SELECT DISTINCT merchant_category FROM products WHERE ? = ? ORDER BY merchant_category ", $params);  

The data I'm trying to match with the query is:

children's accessories

And I'm not getting any results. How can I get round this?

Answer 1

There are several flaws in your code.

1. You can bind data literals only. Not identifiers. Not Mysql functions. Not various SQL parts. But data literals only.
2. The way you did it with your old code is awful. Instead of properly escaping your input data, you "escaped" data that is already in the database.

So, you have to get rid of both these things and make your query as simple as

$param = str_replace('-',' ',$_GET['cat1']); //yes, I don't trust your functions
$sql = "SELECT DISTINCT merchant_category FROM products 
        WHERE category_name = ? ORDER BY merchant_category";
$rs = $this->db->rawQuery($sql, array($param)); 

if it won't work - debug it. But you have to make it work this way only. Without replaces and stuff.

Update:
I got it.
You're trying to match URL slugs against category names.
That's wrong way.
Add another field to your table, contains exact slug from the URL.
And then match this one.
Otherwise you'll be in constant trouble.