Page 1 of 1
mysql / tokens db class. Use of underscore in field data
Posted: 11 May 2020, 22:24
by 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.
Re: mysql / tokens db class. Use of underscore in field data
Posted: 11 May 2020, 22:57
by 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.
Re: mysql / tokens db class. Use of underscore in field data
Posted: 11 May 2020, 23:27
by 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)
Re: mysql / tokens db class. Use of underscore in field data
Posted: 12 May 2020, 20:18
by grivin
Hi
In SQL, '_' is a wildcard too, similar to '%'... You need to escape it.
ex:
LIKE 'tile\_14\_%'
HTH,
Vincent
Re: mysql / tokens db class. Use of underscore in field data
Posted: 12 May 2020, 20:46
by 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 ??
Re: mysql / tokens db class. Use of underscore in field data
Posted: 13 May 2020, 00:14
by 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.
Re: mysql / tokens db class. Use of underscore in field data
Posted: 13 May 2020, 09:25
by RicardoRix
Loreroth wrote: ↑13 May 2020, 00:14
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.