Advice needed

[Max Semenik]

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]])


_______________________________________________
Wikitech-l mailing list
Wikitech-l@...
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

[Jared Williams]

 

> -----Original Message-----
> From: wikitech-l-bounces@...
> [mailto:wikitech-l-bounces@...] 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@...
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

[Tim Starling-2]

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 mailing list
Wikitech-l@...
https://lists.wikimedia.org/mailman/listinfo/wikitech-l