http://qs321.pair.com?node_id=665850

punch_card_don has asked for the wisdom of the Perl Monks concerning the following question:

In researching the title question for DBI applications, I came across this page: SQL Injection Attacks by Example.

Scroll down to Mitigation : Use bound parameters (the PREPARE statement) , where the example in Perl uses placeholders in a prepare statment, like this: 

$sth = $dbh->prepare("SELECT email FROM members WHERE user_id = ?;");
$sth->execute($user_id_from_form);

[download]
and says:

...at no point do the contents of this variable have anything to do with SQL statement parsing. Quotes, semicolons, backslashes, SQL comment notation - none of this has any impact, because it's "just data". There simply is nothing to subvert, so the application is be largely immune to SQL injection attacks.

...enormous security benefits. This is probably the single most important step one can take to secure a web application.

If so, I'm thinking this should just be standard practice for any and all DB transactions that pass user input to an sql statement.

So the question to this post is whether the Monastery agrees with the assertions of ths website.




Forget that fear of gravity,
Get a little savagery in your life.

Replies are listed 'Best First'.
Re: Securing DB transactions with user form input
by moritz (Cardinal) on Feb 03, 2008 at 19:39 UTC
    the question to this post is whether the Monastery agrees with the assertions of ths website.

    Yes!

    At least if you don't count "think of security".

    Update: I thought a bit more about that, and came to the conclusion that place holders are indeed very important for database powered websites. But many systems use plain text files to generate HTML pages from, and in that case I think taint mode is obviously more important.

[reply]
Re: Securing DB transactions with user form input
by perrin (Chancellor) on Feb 03, 2008 at 20:40 UTC
    Always use placeholders! In addition to the security benefits, it has performance benefits on databases that use server-side prepared handles, and allows you to use prepare_cached() more effectively (which gives performance benefits on all databases). Anyone who hasn't read them should check out Tim Bunce's DBI talk slides.
[reply]
Re: Securing DB transactions with user form input
by talexb (Chancellor) on Feb 03, 2008 at 20:44 UTC
[reply]
      I can't think of any reason why placeholders wouldn't be used as a matter of course.

      I have seen some cases where not using placeholders gave the database extra information that would result in more efficient query execution plans.

      For example if you have a table with a gender column, and only 2% of the rows have the value F, it might be helpful (in the decision whether to use a certain index or type of join) to let the database see what gender your query is about. 

      select * from some_table where gender = 'F';

      [download]

      Another example is the page size for paged data.

      Of course, those are edge cases, only affect databases sophisticated enough to make those kind of decisions in the first place (and those databases usually also have means to workaround the issue while still using bind variables), and are most often not related to direct user input anyway.

      In general, I absolutely agree that not using bind variables is a cardinal sin. If you are using direct interpolation into the query string, be prepared to have a very good explanation for it.

[reply]
[d/l]
[select]
Re: Securing DB transactions with user form input
by graff (Chancellor) on Feb 04, 2008 at 00:34 UTC
    There should be no disagreement with those assertions. But there is more that probably needs to be said. Placeholders only work for places where SQL accepts literal string or numeric values. You cannot use "?" in place of a table name, column name, function call, operator ("=","!=",etc), conjunction ("and","or") or other reserved word, or in place of an entire clause.

    (Note that if table "foo" has columns "bar" and "baz", and these columns can sometimes have equal values, the following two cases are not equivalent: 

          my $sth = $dbh->prepare( "select bar from foo where bar=baz" );
      $sth->execute;   # selects rows where 2 columns have same values
# vs:
      my $sth = $dbh->prepare( "select bar from foo where bar=?" );
      $sth->execute( "baz" );   # selects rows where bar='baz' (3-lett
+er literal)

    [download]
    That is, a value passed via execute() to fill a placeholder slot can only be interpreted as data, not as a column name.)

    That should all be obvious to anyone who understands placeholders. Still, it's fairly easy to come up with situations where you might want user input (cgi parameters) to determine some of those things that placeholders can't be used for, and for those situations, taint mode is essential -- but it's also essential to know how (not) to apply it: it is there to stop the program in case you have written it in a way that would allow something dangerous to happen. If you include "-T" but then circumvent it, untainting everything just to keep the program from crashing on taint problems, that's just as bad as not using taint mode at all.

    For the most part, providing this other kind of extra flexibility for queries (where placeholders don't apply) will involve small sets of alternative choices, so it should be easy to make sure that the full sql statements for the complete set of alternatives can be assembled using "trusted" values that are intrinsic to your code. Then, the only thing the user input does is to determine which of the possible sql statements will be used in a given instance.

    That is, values that come directly from the client are checked to see if the user is making a coherent request and if so, to determine which query the user wants (and if not, there should be a suitable default response); the user input never needs to be (and should never be) included directly as part of an sql statement being passed to the database.

[reply]
[d/l]
[select]
Re: Securing DB transactions with user form input
by nikosv (Deacon) on Feb 04, 2008 at 16:55 UTC
    Do placeholders make the use of a regular expression for validating if the data being used in the SQL statement is correctly formed,not necessary? ie to make sure that the parameter passed in is of the correct format like a 2 digit integer value?
[reply]

      That depends on what you're trying to protect against.

      If you're worried about security problems, then using placeholders will be enough.

      If you're worried about data constraints, then you still need to check your parameters. If the database allows a value that you don't want in it, you'll have to validate the user input to ensure that. If the user tries to pass in a value that the database does not allow (a string rather than a number, for example), then the database will throw out an error when you try to put that in. Either way, some kind of validation before the data reaches the database could save you one headache or another.

[reply]
Re: Securing DB transactions with user form input
by Anonymous Monk on Feb 04, 2008 at 17:14 UTC
    "If so, I'm thinking this should just be standard practice for any and all DB transactions that pass user input to an sql statement."

    Perhaps. But consider other languages such as PHP that don't have placeholders. I find that the standard practice is to use id's wherever you can -- such as a drop down boxes of items. Don't pass the value of the drop down -- pass in the id number for that value and translate it back before you issue your query. 

    my $id = $q->param( 'start_date_id' );

my $date = $dates[$id]; # etc. maybe add real validation

my $sth = $dbh->prepare( 'SELECT this FROM that WHERE date = ?' );
$sth->execute( $date );

    [download]
[reply]
[d/l]
[reply]
        I was *this* close to doing just that and rolling my own ... but, as much as I would love to use that code here at work, I just don't think my co-workers would understand. :(

        Thanks for the tip!

[reply]

Back to Seekers of Perl Wisdom