MySQL syntax error in string built by PHP

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?

careful, your code is vulnerable to SQL injection. if you do not know what this is, or what to do about it, read this : bobby-tables.com — Timothy Groote
– Timothy Groote, Commented Oct 21, 2015 at 8:40
i can hardly believe that SQL would trip over the exact same query it can otherwise sucessfully execute. are you sure those are the exact outputs of both methods? — Timothy Groote
– Timothy Groote, Commented Oct 21, 2015 at 8:46
Yes I copied the output and pasted it into notepad to compare the query strings in both instances and they are identical — Andy Groom
– Andy Groom, Commented Oct 21, 2015 at 8:48
the ' at '% are the correct ' ? (only want to make sure as its always hard to sicern there). one additional question there though: surveylayoutid and customerid both ahve ' ' in their checks are those string values? if not then the ' ' are not needed and should be removed. — Thomas
– Thomas, Commented Oct 21, 2015 at 8:54

Thomas · Accepted Answer · 2015-10-21 09:43:17Z

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.

answered Oct 21, 2015 at 9:43

Thomas

3,0144 gold badges41 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thomas Over a year ago

Not 100% sure so not adding it as answer but it could be that the %dn is interpreted there. has been a while since I last coded php but if I remember cforrectly %d is to indicate integers so that COULD be the reason why " " has problems there. One additional thing though as far as I'm aware mysql_query is deprecated and you should switch to mysql_i functions

MuthaFury · Accepted Answer · 2015-10-21 09:36:42Z

0

See if this help you solve your problem?

$remap = array(
    "Postcode",
    "Street",
    "HouseNum",
    "District",
    "Town"
);

for ($i = 0; $i <= 4; $i++)
    {
    if ($_POST[strtolower($remap[$i]) ] != "")
        {
        $query = "select * from Surveys where surveylayoutid='12' and customerid='1' and (";
        $clue = $_POST[strtolower($remap[$i]) ];
        $query.= $remap[$i] . " like '%$clue%') order by id desc";
        $query_done[] = $query;
        unset($query);
        $result = mysql_query($query_done[$i], $connection) or die(mysql_error());

        // Display your result here

        }
    }

I tried changing your code abit, and it seems the result is something like this

select * from Surveys where surveylayoutid='12' and customerid='1' and (Postcode like '%Postcode%') order by id descselect * from Surveys where surveylayoutid='12' and customerid='1' and (Street like '%Street%') order by id descselect * from Surveys where surveylayoutid='12' and customerid='1' and (HouseNum like '%HouseNum%') order by id descselect * from Surveys where surveylayoutid='12' and customerid='1' and (District like '%District%') order by id descselect * from Surveys where surveylayoutid='12' and customerid='1' and (Town like '%Town%') order by id desc

edited Oct 21, 2015 at 9:36

answered Oct 21, 2015 at 9:12

MuthaFury

8051 gold badge7 silver badges23 bronze badges

7 Comments

Andy Groom Over a year ago

It returns:

array(1) { [0]=> string(110) "select * from Surveys where surveylayoutid='12' and customerid='1' and (Postcode like '%dn%') order by id desc" } 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 '' at line 1

Andy Groom Over a year ago

Slightly different, it now returns

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 '' at line 1

MuthaFury Over a year ago

I assume, one of the post is empty, causing such error. Let me re-edit again

Andy Groom Over a year ago

If I change the query to: $remap[$I] . "='$clue') instead of $remap[$I] . " like '%$clue%') it works, so is the issue with "like" ?

Thomas Over a year ago

if the original sql worked then that is quite strange. what datatype is postcode? I would guess cahr or varchar that means then that it should be possible to do blahblah like '%myInnerText%' . which mysql version is it?

|

Collectives™ on Stack Overflow

MySQL syntax error in string built by PHP

2 Answers 2

1 Comment

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related