How can I escape single quotes when selecting data from MySQL through a php variable

Question

I am trying to fetch some data from MySQL which contain single quotes through dependent select in Ajax but it wasn't returning any results.

I used

$mysqli->real_escape_string

That doesn't help either.

In my database table, I have values which are like this:

OTUN 'A', JAGUN B'. Using dependent select menu, I need to fetch places under each of them.

My query:

$ward_code = $mysqli->real_escape_string($_REQUEST['ward_code']);
    $sql = "select pu_name, concat(pu_code,' - ',pu_name) pu_name from polling_unit where REPLACE(pu_ward_name,'\\\\','')='".$ward_code."' 
     AND pu_name NOT IN (SELECT pu_name FROM post_election_info ) order by CAST(pu_code AS UNSIGNED)";

Please use prepared statements and bind variables and this will solve your problem and a few others. — Nigel Ren, Feb 12 '19 at 22:03
And what is this all about `REPLACE(pu_ward_name,'\\\\','')`??? Why would you have \\\\ in the database? — AbraCadaver, Feb 12 '19 at 22:04

score 1 · Accepted Answer · answered Feb 12 '19 at 22:54

As Nigel Ren commented above, you should learn to use query parameters.

Something like this:

$sql = "SELECT pu_name, CONCAT(pu_code,' - ',pu_name) pu_name 
  FROM polling_unit 
  WHERE REPLACE(pu_ward_name,'\\\\','') = ? 
    AND pu_name NOT IN (SELECT pu_name FROM post_election_info) 
  ORDER BY CAST(pu_code AS UNSIGNED)";

$stmt = $mysqli->prepare($sql);

$stmt->bind_param("s", $_REQUEST['ward_code']);

$stmt->execute();

Don't put quotes around the ? parameter placeholder. It doesn't need them, it will always be treated as a single scalar value.

Don't do any escaping on $_REQUEST['ward_code']. It doesn't need it when you use query parameters, it will always be treated as a single scalar value. So it can't "break out" of any quoted string, even if it contains a literal single-quote.

Using query parameters is a better solution than worrying about whether you're escaping correctly or not. It's better than giving yourself eyestrain looking at all the quotes-within-quotes and wondering if you balanced them all correctly.

Query parameters are easier to code, and safer for your code quality and security.

How can I escape single quotes when selecting data from MySQL through a php variable

1 Answers1