> -----Original Message----- > From: wikitech-l-boun...@lists.wikimedia.org > [mailto:wikitech-l-boun...@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 _______________________________________________ Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
