|Problems? Is your data what you think it is?|
DBIx::Class field name (identifier) injectionsby duelafn (Vicar)
|on May 26, 2011 at 16:32 UTC||Need Help??|
duelafn has asked for the
wisdom of the Perl Monks concerning the following question:
In DBIx::Class case insensitve substring search., chrestomanci encounters a problem using LOWER(me.name) as a field name while using the quote_char (or perhaps newer quote_names) option. This inspired me to dig into escaping of identifiers (in particular, protection against injection) in DBIx::Class.
Consider a simple table "hash" with two text columns, "key" and "value". Simple code to set up an SQLite table and populate it with three rows below.
NOTE: unlinks and creates file called "test.sqlite" in current directory.
We can see that, by default, DBIx::Class does not protect against SQL injection in identifiers:
SQL::Abstract and DBIx::Class have a quote_char option (newer DBIx::Class recommends quote_names instead). These options will cause identifiers to be quoted. The intention is to protect against column names which may be keywords in SQL.
The quote_*, however, do not provide injection protection (at least to my standards) as they simply perform the naïve quoting.
Proper quoting for injection protection can only (as far as I can tell) be performed via quote_identifier on the DBI storage object
Is it true that there is no fully automated injection protection for identifiers in SQL::Abstract / DBIx::Class? Would it be reasonable to post a bug / wishlist item against SQL::Abstract and DBIx::Class requeesting a quote_identifiers option or some such (to be used like quote_names) that performs the more robust quoting? Am I dumb and just simply missing something?