0

I am having a problem retrieving a data from mysql that has an apostrophe in it. I don't have problem in saving a data that has an apostrophe since that I am using mysqli_real_escape_string when inserting the data to the database but I get an error when retrieving it. My sample code in PHP for retrieving the data is mysqli_query($con,"SELECT column1data FROM Table1 WHERE column2data LIKE '".$data."' ")or die(mysqli_error($con)); and the $data is the one that has an apostrophe in it.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Mark Vincent Manjac
  • 507
  • 1
  • 6
  • 27
  • Use parameters. It will make your life so much easier. Or escape the string as in insert. But rather parameters. – Sami Kuhmonen Oct 08 '15 at 06:14
  • 1
    See http://stackoverflow.com/questions/9596652/how-to-escape-apostrophe-in-mysql and http://stackoverflow.com/questions/4659879/mysql-php-with-special-characters-like-apostrophe-and-quotation-mark and http://stackoverflow.com/questions/8651170/apostrophe-during-insert-mysql – Thomas Oct 08 '15 at 06:15

2 Answers2

4

Still use mysqli_real_escape_string() when binding variables, especially when it is a string, to your query. Not just for inserting data.

$data = mysqli_real_escape_string($data);

You should also look at prepared statement. No need to sanitize the variable before binding it your query, prepared statement will do it for you.

$data = "%{$data}%"; /* CHANGE THIS IF YOU DON'T WANT TO USE % */

$stmt = $con->prepare("SELECT column1data FROM Table1 WHERE column2data LIKE ?")){ /* PREPARE YOUR STATEMENT */
  $stmt->bind_param("s", $data); /* BIND THE DATA */
  $stmt->execute(); /* EXECUTE THE QUERY */
  $stmt->bind_result($column1data); /* BIND THE RESULT TO THIS VARIABLE */
  $stmt->fetch(); /* FETCH THE RESULT */
  echo $column1data; /* ECHO THE RESULT */
  $stmt->close(); /* CLOSE THE STATEMENT */
}
Logan Wayne
  • 6,001
  • 16
  • 31
  • 49
1

You should use mysqli_real_escape_string() every time that a string is being placed into your SQL code. Use mysqli_real_escape_string($data) rather than just $data.

Nerdwood
  • 3,947
  • 1
  • 21
  • 20