There's more than one way to do things | |
PerlMonks |
Re^3: Preventing SQL injection attacks: Placeholders are enough for MySQL, Postgresql and SQLiteby siracusa (Friar) |
on Jan 10, 2008 at 14:10 UTC ( [id://661618]=note: print w/replies, xml ) | Need Help?? |
As for the "you have to know exactly which arguments are parsed as SQL" part of the comment, it treats *all* of its arguments as perl values and not SQL values unless you use the options to pass in a raw SQL string, in which case its your responsibility to make sure it doesnt open you up to risks. I can't speak for Rose::DB because I don't use it, but I would be shocked if the author didn't take all this stuff into account too. Your instincts were right :) Rose::DB::Object (note: not Rose::DB, which is a db abstraction layer used by the ORM, Rose::DB::Object) uses bind values everywhere, except in cases where the DBD::* driver requires that values be "inlined." There is no ambiguity in these cases as the values that are allowed to be are specific for each column type/database combination. For example, Informix supports a syntax like this: SELECT * FROM t1 WHERE somedate > CURRENT; where "somedate" is a DATE column type. Unfortunately, DBD::Informix chokes on this:
because it considers the above the equivalent of comparing somedate to the string "CURRENT", as if it were: SELECT * FROM t1 WHERE somedate > 'CURRENT'; Then the database itself throws an error since it refuses to compare a DATE column to a string. (Other databases are more lenient about this kind of thing.) Anyway, the upshot is that, if you want to use CURRENT (or any other "special" server-evaluated value), you must inline it directly into the query passed to DBI. In Rose::DB::Object, such values are called "keywords" and are automatically inlined. So if you add this clause to a Rose::DB::Object::Manager query: somedate => { gt => 'CURRENT' }, it'll be smart enough to realize that a) "somedate" is a DATE column and b) "CURRENT" is a valid keyword for DATE columns in Informix, so it'll inline the value instead of using a bind parameter. Again, these lists of keywords are specific to each column-type/database combination, so the word "CURRENT" would not be inlined if you were connected to a MySQL database, for example. Also note the lack of ambiguity: it's clear that there's only one reasonable meaning of CURRENT when used as a comparison value for a DATE column. IOW, there's never a reason for it not to be inlined in this case, so the auto-inlining is never a hindrance. Finally, you can always explicitly force any value to be taken as a literal and inlined by passing it as a scalar reference: somecol => { gt => \q(iknowwhatimdoing) }, That'll produce SQL like this, with no "?" placeholders, quoting, or bind values: somecol > iknowwhatimdoing Obviously, you'd only do this in very specific cases when you're sure what you're doing is safe :)
In Section
Meditations
|
|