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