PHP构建的字符串中的MySQL语法错误

Question

I have some code which generates a MySQL query string called $query:

$query = "select * from Surveys where surveylayoutid='$surveyid' and customerid='" . $_SESSION['login_customerid'] . "' and (";
$clue = $_POST['postcode'];
$onwhat="Postcode";
$query .= $onwhat . " like '%$clue%') order by id desc";
$result = mysql_query($query, $connection) or die(mysql_error());

This returns something like:

select * from Surveys where surveylayoutid='12' and customerid='1' and (Postcode like '%dn%') order by id desc

which works fine. I've then altered the code because I want to search on more fields so it now reads:

$remap = array("Postcode", "Street", "HouseNum", "District", "Town");
$query = "select * from Surveys where surveylayoutid='$surveyid' and customerid='" . $_SESSION['login_customerid'] . "' and (";
for ($i=0; $i<=4; $i++) {
 if ($_POST[strtolower($remap[$i])]!="") {
  $clue = $_POST[strtolower($remap[$i])];
  $query .= $remap[$i] . " like '%$clue%') order by id desc";
  break;
 }
}

This also returns:

select * from Surveys where surveylayoutid='12' and customerid='1' and (Postcode like '%dn%') order by id desc

which on the face of it is identical but it generates this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'like '%dn%' order by id desc' at line 1

In both cases $query contains the same "text" but for some reason isn't treated as a valid MySQL query in the updated code, can anyone tell me why?

Answer 1

One possible problem could be the interpretation of the content here. If you use:

  $query .= $remap[$i] . " like '%$clue%') order by id desc";

All that is inside "" gets to be interpreted. Thus there could be unwanted side effects that you don't see at first glance and can explain what is happening. To avoid this it would have to be changed to:

$query .= $remap[$i] . ' like ' . "'" . '%' . $clue . '%' . "') order by id desc";

Even though more clunky in terms of how big it is, it makes sure that $lue and also the % are not interpreted as all in between ' ' is not interpreted.