0

In the database I have a string like this: Caisse primaire d'assurance maladie

Now when I try to look for it in my PHP script, it returns no items.

$array=array("Caisse primaire d'assurance maladie","Mairies","Hotels");
$in_list = "'".implode("','",$array)."'";

$stmt = $this->db->prepare('SELECT libelle,activite,adresse,tel,lat,lng FROM etablissements where type IN ('.$in_list.')');
$stmt->execute();
$stmt->bind_result($libelle,$activite,$adresse,$tel,$lat,$lng);//the $stmt is empty

Now, if i try to look for Mairies only or Hotels only it would return me a result. That mean that the apostrophe is preventing my prepared query from running correctly. How can I fix this?

lonesomeday
  • 233,373
  • 50
  • 316
  • 318
Luca
  • 20,399
  • 18
  • 49
  • 70
  • You can try replace ' with '' (two single quote) and try.. – Thit Lwin Oo Feb 10 '12 at 09:35
  • 2
    Yes, the apostrophe is causing a syntax error in the query. That's what prepared statements are supposed to prevent. Unfortunately you're not using prepared statements, you're just using regular old, SQL injection prone, syntax breaking string concatenation. What MySQL extension in particular are you using? MySQLi? PDO? – deceze Feb 10 '12 at 09:36
  • show us $in_list content – soju Feb 10 '12 at 09:38
  • 1
    Then please see here for correct usage of MySQLi with prepared statements and `WHERE IN`: [I have an array of integers, how do I use each one in a mysql query (in php)?](http://stackoverflow.com/questions/330268/i-have-an-array-of-integers-how-do-i-use-each-one-in-a-mysql-query-in-php) – deceze Feb 10 '12 at 09:39
  • Caisse primaire d'assurance maladie => Caisse primaire d''assurance maladie – Thit Lwin Oo Feb 10 '12 at 09:41

3 Answers3

1

Use mysqli_real_escape_string function to escape your query first.

Michał Powaga
  • 22,561
  • 8
  • 51
  • 62
Alex Chorry
  • 334
  • 4
  • 9
1

The string "Caisse primaire d'assurance maladie" has a single quotes in it.Maybe this single quotes ends your sql string unexpectedly.
I suggest you to print your sql statement first to check if it is correct.:)

darkbluever
  • 21
  • 3
1

How can i fix this.

By using a real prepared query, not just mocking it.

Or by escaping your strings.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345