markjugg has asked for the wisdom of the Perl Monks concerning the following question:
SQL::Interp is an excellent tool to help build SQL. It can be especially pleasant to use from DBIx::Simple. Combining the two, you can do this:
$result = $db->iquery("SELECT * FROM foo WHERE a = ",\$b)It's easy to read, and $b will automatically be translated into a bind variable. My concern is that on a number of occasions I have seen well meaning programmers do this instead:
$result = $db->iquery("SELECT * FROM foo WHERE a = ",$b)The difference is the backslash before the $b. The rub is that the code produces the same result either way, but the second format has possibly introduced an opportunity for a SQL injection attack. What suggestions do you have for how SQL::Interp might detect such cases, so that it can warn or die? Here are some brainstorms I've thought of, possibly activated only when a "strict mode" is enabled.
- Scalars are no longer allowed at all. Instead, you would use something like: "sql('SELECT * from foo WHERE a = '),\$b"
- Assume any two strings in a row is a mistake...you could always concatenate them instead.
- Parse the SQL sufficiently to understand where bind variables *should* be and then make sure they are used there.
|
---|
Replies are listed 'Best First'. | |
---|---|
Re: Improving the security of SQL::Interp
by Porculus (Hermit) on Jan 06, 2010 at 22:10 UTC | |
Re: Improving the security of SQL::Interp
by markjugg (Curate) on May 03, 2011 at 17:31 UTC |
Back to
Seekers of Perl Wisdom