SQL Syntax Error When Using LIKE in an ADOdb Prepared Statement

I’m trying to search in a column with LIKE ‘%string%’ using ADODb prepared statement (using MySQLi PHP driver) with this:

$q_record = $DB->Prepare("
  SELECT author, title
  FROM books
  WHERE (author LIKE '%?%' OR title LIKE '%?%')
  ORDER BY submit_date
  ");

$record = $DB->GetAll($q_record, array( $q, $q ) ) or die($DB->ErrorMsg());

Error message is like

“You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘nano’%’ OR title LIKE ‘%’nano’%’) ORDER BY submit_date DESC ) ‘ at line 4”

Where “nano” is the search keyword.

I have suspicion that I have to escape ‘?’ but I don’t know how to do that.

Answer

Plain and simple:

$q_record = $DB->Prepare("
  SELECT author, title
  FROM books
  WHERE (author LIKE CONCAT('%', ?, '%') OR title LIKE CONCAT('%', ?, '%'))
  ORDER BY submit_date
");

In other words, the parameter placeholder ? does not work like string interpolation. Use it like you would use an SQL variable.

Advertisement

Answer