#46556 [Asn->Bgs]: There is no good method to escape a string for use in a (mySQL) LIKE clause
ID: 46556
Updated by: johannes@php.net
Reported By: taco at procurios dot nl
-Status: Assigned
+Status: Bogus
Bug Type: PDO related
Operating System: linux
PHP Version: 5.2.6
Assigned To: johannes
New Comment:
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.
Previous Comments:
------------------------------------------------------------------------
[2008-12-10 01:32:18] 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', '%')
------------------------------------------------------------------------
[2008-11-24 09:21:15] 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-11-13 15:30:49] lstrojny@php.net
Maybe something for PDOv2 or mysqli?
------------------------------------------------------------------------
[2008-11-12 15:56:53] 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.
------------------------------------------------------------------------
--
Edit this bug report at http://bugs.php.net/?id=46556&edit=1
Thread (6 messages)