Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

Re^3: Preventing SQL injection attacks: Placeholders are enough for MySQL, Postgresql and SQLite

by siracusa (Friar)
on Jan 10, 2008 at 14:10 UTC ( [id://661618]=note: print w/replies, xml ) Need Help??


in reply to Re^2: Preventing SQL injection attacks: Placeholders are enough for MySQL, Postgresql and SQLite
in thread Preventing SQL injection attacks: Placeholders are enough for MySQL, Postgresql and SQLite

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:

$sth = $dbh->prepare('SELECT * FROM t1 WHERE somedate = ?'); $sth->execute('CURRENT'); # Boom: error!

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 :)

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://661618]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others contemplating the Monastery: (2)
As of 2024-03-19 03:52 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found