-----Original Message-----
From: wikitech-l-bounces(a)lists.wikimedia.org
[mailto:wikitech-l-bounces@lists.wikimedia.org] On Behalf Of
Max Semenik
Sent: 19 October 2009 21:42
To: Wikimedia developers
Subject: [Wikitech-l] Advice needed
As you may or may not know, most queries involving LIKE
clause are broken on SQLite backend.[1] As a measure to fix
it, I'm planning to replace all LIKEs with a function call
that will provide the needed abstraction.
However, I would like it to be convenient to use and provide
automatic protection against SQL injection, so instead of
something like
$sql = 'SELECT * FROM table WHERE field' .
$db->like($db->escapeLike($text) . '%')
I'd rather prefer Mr.Z-man's idea of
$sql = 'SELECT * FROM table WHERE field' . $db->like($text,
MATCH_STRING )
The example patch is at [2], but there is a problem: due to
PHP's duck typing, you can have tough times in telling a
string to be encoded from a constant that indicates '%' or
'_' placeholders. There are a few possible solutions:
* Even comparing with === can't provide enough guarantee for integer
constants.
* We could use tricky float constants such like
3253427569845.236156471,
as suggested by Aryeh Gregor, but it looks rather hackish.
* Alternatively, there could be something like Database::asterisk()
that would return unique objects.
Can there be a better way of doing that? And which variant of
constant names would you prefer: Mr.Z-man's original
LIKE_UNDERSCORE/LIKE_PERCENT, MATCH_CHAR/MATCH_STRING
proposed by me, or something else?
Please opine.
--
[1]
https://bugzilla.wikimedia.org/show_bug.cgi?id=20275
[2]
I'd personally go with 3 functions, assuming don't need the full
flexibility of LIKE
startsWith($prefix) => LIKE '$prefix%'
endsWith($suffix) => LIKE '%$suffix'
contains($infix) => LIKE '%$infix%'
Looking at the grep results searching for LIKE seems like they would
cover it.
Jared