1

Numerous strange failures of mySQL queries have been dogging me for days. My having failed to find the cause, can anyone suggest where I should look for a solution? This SQL query is a particularly simple example:

SELECT * FROM `applicants` WHERE `first_name` LIKE "%Tim%" ORDER BY `date_created` DESC LIMIT 1

This causes a syntax error in mySQL, at a position near LIKE "%Tim%". However, a copy/paste works in phpmyadmin. I've done a hex check but can see no invisible characters that might cause a problem. The relevant code lines are

$sql = generateSql($metaData);
$stmt = $pdo->prepare($sql);
$stmt->execute();

and in generateSql are:

$fields = ['', 'email', 'first_name', 'last_name', 'postcode', 'location'];
...
$field = $fields[$metaData['sfield']];
return 'SELECT * FROM `applicants` ' .
  'WHERE `' . $field . '` LIKE "%' . $metaData['stext'] . '%" ' .
  'ORDER BY `date_created` DESC LIMIT 1';

I've also re-written the SQL generating code as one line, but it makes no difference.

Other queries generated in the same function don't cause errors (well, not at the moment, they don't).

magnol
  • 344
  • 3
  • 15
  • The following thread will fix it: [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1) – Lawrence Cherone Mar 17 '21 at 18:33
  • If I was looking for ' Bella', say, I'm not sure that I'd want 'Arabella' to be returned. – Strawberry Mar 17 '21 at 20:06

1 Answers1

2

try avoid double quotes arund like string

SELECT * FROM `applicants` 
WHERE `first_name` LIKE concat('%','Tim', '%')
 ORDER BY `date_created` DESC LIMIT 1

or

SELECT * FROM `applicants` 
WHERE `first_name` LIKE '%Tim%'
 ORDER BY `date_created` DESC LIMIT 1
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • The reason for double quotes there was because I'd enclosed the whole sql string in single quotes. I've reversed the quotes throughout and the SQL call works. Now please explain to me why it works! – magnol Mar 17 '21 at 19:00
  • 1
    The double quote are for columns name .. for string you need single quote for this reason .. you should use double quote for sql code string and single quote for inner string.. – ScaisEdge Mar 17 '21 at 19:43
  • I think you're just making this stuff up – Strawberry Mar 17 '21 at 20:07
  • 1
    @Strawberry Well, you think wrong. See [this answer](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql/14123649#14123649) for more details. – IMSoP Mar 17 '21 at 20:15
  • While this could certainly cause errors, testing [here](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=224c7095b5c7a1bc3d1916632cd9bd70) shows a different error message from the one in the question - it recognises the syntax, but complains that the column doesn't exist. That suggests there's at least more to it than just `ANSI_QUOTES` mode. – IMSoP Mar 17 '21 at 20:20
  • Thanks to you both for your most helpful and educational comments. How I've managed to read so much on mySQL and generate so many SQL queries wtihout discovering these issues is deeply troubling. – magnol Mar 18 '21 at 09:51