-----Original Message----- From: wikitech-l-bounces@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]
https://bugzilla.wikimedia.org/attachment.cgi?id=6531&action=diff
-- Max Semenik ([[User:MaxSem]])
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