Re: Securing DB transactions with user form input

[graff]

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)

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.