SQL throwing Syntax Error when using LIKE with Parameters

Question

I am using PDO with PHP to submit queries to my SQL server. I need to use the LIKE clause to check a username to ensure that it is allowed (eg; it does not contain any banned words), so I am using this SQL query...

SELECT * FROM `table` WHERE (`name` LIKE %?%);

I am then inserting the paramater with PDO later like this...

$statement->bindParam(1, $username)

When I try to run this, I get this error...

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 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 '%'dibdibs'%)' at line 1' in C:\xampp\htdocs\scripts\sql.php:57 Stack trace: #0 C:\xampp\htdocs\scripts\sql.php(57): PDOStatement->execute() #1 C:\xampp\htdocs\api\users.php(30): dibdibs\pdo->query('SELECT * FROM `...', Array) #2 {main} thrown in C:\xampp\htdocs\scripts\sql.php on line 5

The PDO code works fine for other queries, when I am using = instead of LIKE, but is is throwing the above error when I try to use the LIKE clause.

I have put my full code on Pastebin, if you need to check it. I am using GET to get data, as I am using this with AJAX (which is also working fine), but the username I have tried is 'dibdibs', which works fine in other queries.

Use `name LIKE ?` and then bind something like this `$statement->bindParam(1, "%$username%")` — juergen d, Jul 16 '16 at 17:28
imo, always have the placeholder surrounded by spaces. It make it clear to the programmer and the parser that this is a single token. And makes reading code easier? — Ryan Vincent, Jul 16 '16 at 17:31

score 1 · Accepted Answer · answered Jul 16 '16 at 17:43

Use This

U can use any of these two

$query = $database->prepare('SELECT * FROM table WHERE column LIKE ?');
$query->execute(array('%value%'));

while ($results = $query->fetch())
{
   echo $results['column'];
}

 // without loop 

$query = "SELECT * FROM tbl WHERE address LIKE ?";
$params = array("%$var1%");
$stmt = $handle->prepare($query);
$stmt->execute($params);

SQL throwing Syntax Error when using LIKE with Parameters

1 Answers1