php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #46556 There is no good method to escape a string for use in a (mySQL) LIKE clause
Submitted: 2008-11-12 15:56 UTC Modified: 2009-02-17 14:51 UTC
From: taco at procurios dot nl Assigned: johannes (profile)
Status: Not a bug Package: PDO related
PHP Version: 5.2.6 OS: linux
Private report: No CVE-ID: None
View Developer Edit
 [2008-11-12 15:56 UTC] taco at procurios dot nl 
Description:
------------
There is no good method to escape a string for use in a (mySQL) LIKE clause. In a query like "SELECT `foo` FROM `bar` WHERE `baz` LIKE '%" . $qux . "%'" the value of $qux should be escaped for both the query itself (like PDO::quote() does) as the LIKE clause (i.e. escaping % and _ characters.

Using PDO the only way to escape a variable is using either PDOStatement::bindParam() or PDO::quote(). The first is not suitable for two reasons:
1. Not every query is a prepared statement
2. There is no way to escape % and _ (escaping them first will result in the \ being escaped: \% becomes \\%)

The latter is not suitable because it will add quotes to the string, so you'll have to get rid of the quotes, escape % and _ and add the result to the query.

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2008-11-13 15:30 UTC] lstrojny@php.net 
Maybe something for PDOv2 or mysqli?
 [2008-11-24 09:21 UTC] taco at procurios dot nl 
There is no need for such a method in mysqli because mysqli_real_escape_string() does not add quotes to the resulting string. Using mysqli you would be able to do this:

$q = "SELECT...WHERE `foo` LIKE '%" . addcslashes($MySQLi->real_escape_string($evilVar), '%_') . "%'";

In PDO this is impossible because of the added quotes.
 [2008-12-10 01:32 UTC] johannes@php.net 
You could do an concat in SQL, not sure whether there's a good way to do this from an API perspective.

SELECT foo FROM bar WHERE baz LIKE CONCAT('%', 'vbebbt', '%')
 [2009-02-17 14:51 UTC] johannes@php.net 
I thought a bit about this and the only clean way is a concat like below.

Your main concern is about prepared statements. With prepared statements the data is sent independently from prepared query string (ok, not 100% true as PDO _might_ use an emulation) so this isn'T possible without changes to the server. 

With the emulation it might be possible but would make the API harder to maintain, the way I've shown before is cleaner.
 
PHP Copyright © 2001-2025 The PHP Group
All rights reserved. 		Last updated: Fri Mar 14 07:01:30 2025 UTC