mysql / tokens db class. Use of underscore in field data

[RicardoRix]

Just came across a subtle problem with using this class within the shared code, and in general will apply to other 'LIKE' mysql queries where you are using underscore _ in field data.

I have 4 DB records.
token_key = 'tile_141_0'
token_key = 'tile_141_1'
token_key = 'tile_14_0'
token_key = 'tile_14_1'

"UPDATE tiles SET bonus=40 WHERE token_key LIKE 'tile_14_%'" - trying to change just the last 2 records.

But this will change ALL 4 records and not just the 14's. Because the underscore is a single character wildcard in LIKE statements and essentially needs to be escaped.

"UPDATE tiles SET bonus=40 WHERE token_key LIKE 'tile\_14\_%'"

I copied an example that uses _ underscores a lot. I think it's best to switch to using - instead to save possible problems.

[RicardoRix]

Already 2 problems with trying to use dash - instead of underscore:

In JS, the ajax call.
Unexpected error: Bad argument type: idTile should be a value with only letters, digits, underscore or space (tile-202-0)

and the tokens.php file:

Code: Select all

final function checkKey($key, $like = false) {
        if (preg_match("/^[A-Za-z_0-9${extra}]+$/", $key) == 0) {
            throw new feException("key must be alphanum and underscore non empty string '$key'");
        }
    }

Why are we being told to use _underscore_ here?
This is between a rock and a hard place.

[RicardoRix]

So hacky, mcHack hack if I want to use dashes in my DB tables, which I do:

JS ajaxcall:
idTile .replace(/-/g,'_')

php:
$idTile = str_replace("_", "-", $idTile);

and for the tokens check, add an escaped - to the preg_match:
if (preg_match("/^[A-Za-z_\-0-9${extra}]+$/", $key) == 0)

[grivin]

Hi
In SQL, '_' is a wildcard too, similar to '%'... You need to escape it.

ex:

LIKE 'tile\_14\_%'


HTH,
Vincent

[RicardoRix]

Yes, thx. I already said that.

If you could avoid using _ in field data then you would to avoid this gotcha. Unfortunately there are other features in the framework actively promoting the use of underscore.

Any chance the JS ajax call could allow - dash ??

[Loreroth]

Unfortunately the hyphen symbol is also a special character in sql. Among other uses it can also be used for comments '--' which can be used as a simple sql injection attack. This is likely the reason for the filter in the tokens.php file.

[RicardoRix]

Unfortunately the hyphen symbol is also a special character in sql. Among other uses it can also be used for comments '--' which can be used as a simple sql injection attack. This is likely the reason for the filter in the tokens.php file.

Yes you're right, but that is the double dash -- rather than single dash - which is harmless.

Really I'm looking for any other special character just as-long as it isn't underscore _ or percent %, it doesn't have to be dash, but ajax call restricts these down to underscore and space. Space is no good, that doesn't work in css classes.