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
-----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
Max Semenik wrote:
- We could use tricky float constants such like 3253427569845.236156471, as suggested by Aryeh Gregor, but it looks rather hackish.
That would introduce an easter egg, where special user input produces unexpected output. It could even be a DoS vector.
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.
I think the function should be buildLike() instead of like(), following the precedent of buildConcat() and the convention that function names should be verb phrases.
Instead of MATCH_CHAR or LIKE_UNDERSCORE I would go for $dbr->anyChar() and $dbr->anyString():
function anyChar() { return new Database_RawLike( '_' ); } function anyString() { return new Database_RawLike( '%' ); }
Then a DBMS which needs to change these special characters could do so by overriding those functions.
I think the function of MATCH_CHAR is rather non-obvious from its name, especially if you don't know SQL and don't know what LIKE clauses are. It looks like a flag, affecting the whole function, instead of a concatenated item.
-- Tim Starling
wikitech-l@lists.wikimedia.org