Hey,
When constructing an SQL string, how should the following things be escaped, if at all?
* Field names * Index names
It looks like when doing a select using the Database MW thing, the field names provided do not get escaped at all.
Cheers
-- Jeroen De Dauw http://www.bn2vs.com Don't panic. Don't be evil. ~=[,,_,,]:3 --
On Oct 7, 2013 11:55 AM, "Jeroen De Dauw" jeroendedauw@gmail.com wrote:
Hey,
When constructing an SQL string, how should the following things be escaped, if at all?
- Field names
- Index names
It looks like when doing a select using the Database MW thing, the field names provided do not get escaped at all.
Using DatabaseBase::addIdentifierQuotes. I believe DatabasrBase::makeList does this automatically in some cases.
On Mon, Oct 7, 2013 at 8:54 AM, Jeroen De Dauw jeroendedauw@gmail.comwrote:
Hey,
When constructing an SQL string, how should the following things be escaped, if at all?
- Field names
* Index names
It looks like when doing a select using the Database MW thing, the field names provided do not get escaped at all.
The "field names" are really arbitrary sql at this point, and a few too many places in MW core abuse this last I checked. The presumption is that user-controlled data should never go into the filed name.
If you are writing an enhancement to this, you should wrap them in ` and escape ` characters in the name. DatabaseBase::addIdentifierQuotes wraps, but doesn't escape.
Cheers
-- Jeroen De Dauw http://www.bn2vs.com Don't panic. Don't be evil. ~=[,,_,,]:3 -- _______________________________________________ Wikitech-l mailing list Wikitech-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/wikitech-l
On Mon, Oct 7, 2013 at 12:05 PM, Chris Steipp csteipp@wikimedia.org wrote:
If you are writing an enhancement to this, you should wrap them in ` and escape ` characters in the name. DatabaseBase::addIdentifierQuotes wraps, but doesn't escape.
Backquotes are a MySQLism. The SQL standard uses double-quotes, which MySQL uses for strings along with single-quotes (unless you put ANSI_QUOTES in sql_mode).
It looks like DatabaseBase::addIdentifierQuotes actually does escape, although whether its escaping is sufficient I don't know. It changes 'foo " bar' into '"foo "" bar"'.
DatabaseMysqlBase::addIdentifierQuotes, on the other hand, doesn't escape correctly; it uses the same function for quoting strings and identifiers, despite the different quote marks. So it will change 'foo " bar' into '`foo " bar`', but 'foo ` bar' comes out wrong as '`foo ` bar`'.
On Mon, Oct 7, 2013 at 12:31 PM, Brad Jorsch (Anomie) <bjorsch@wikimedia.org
wrote:
DatabaseMysqlBase::addIdentifierQuotes, on the other hand, doesn't escape correctly; it uses the same function for quoting strings and identifiers, despite the different quote marks. So it will change 'foo " bar' into '`foo " bar`', but 'foo ` bar' comes out wrong as '`foo ` bar`'.
Good point. Didn't seem to find anything on this in Bugzilla so I filed it: https://bugzilla.wikimedia.org/show_bug.cgi?id=55427
*-- * *Tyler Romeo* Stevens Institute of Technology, Class of 2016 Major in Computer Science
wikitech-l@lists.wikimedia.org