James Buckland James Buckland - 1 year ago 85
MySQL Question

Query for sanitized single quotes in MySQL database.

When inserting names into a customer database I used the MySQLi function real_escape_string to sanitize the data. An example entry with a single quote now looks like this:

Baker\'s Pharmacy

However when I try to query for the name using a query such as:

$search = "Baker's Pharmacy";
$searchName = $db->real_escape_string($search);
$query = "SELECT Name FROM Customers WHERE Name = '$searchName'";

I return no matches, what is the correct way to search for santised single quotes?

Any help is greatly appreciated!

Answer Source

There are two wrong assumptions that needs to be cleared up.

  1. Whatever *escape_string function does not sanitize anything. that's just a nasty rumor that PHP folks are better to finally get rid of.
  2. anyway, by using this function, you are formatting your data not for a database but for the SQL query only. All slashes are stripped off by a database and the data gets stored as is.

Instead of "sanitizing" you have to use mysqli prepared statements for both insert and select queries, and you will see not a single problem related to quotes (unless there are magic_quotes or their home-brewed equivalent are hanging around).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download