[Omaha.pm] SQL Attack exception

[Jay Hannah]

On Jul 29, 2005, at 3:54 PM, Andy Lester wrote:
> On Fri, Jul 29, 2005 at 03:41:48PM -0500, Kenneth Thompson wrote:
>>   foreach my $param ($q->param()) {
>>      # Strip out all wacky characters to prevent SQL injections
>>      #
>>      next ($IgnoreParms{$param}); #ignored - bail now
>>      my $value = $q->param($param); #Not ignored.. clean me up Scotty
>>      $value =~ s/[`;'"\\]//g;
>>      $q->delete($param);	
>
> Please don't do this.  Please use bind variables.
>
> my $sth = $dbh->prepare( "select * from users where foo=? and bar=?" );
> $sth->execute( $foo, $bar );
>
> The $foo matches up to the first ?, and $bar to the second.  Then it
> doesn't matter WHAT you pass in as $foo or $bar because it's not
> interpolated into the SQL, and cannot possibly be executed.

Indeed, sir. We use both.

- The code Kenn posted isn't production code base, he's just playing 
with some stuff.

- We commonly use $q->param filter junk to try to stop exploits for 
more than just DBI calls. Our Internet systems are NEVER exposed to any 
of these things, but: In some Intranet-only scripts we might access 
files on the server based on user input and/or (heaven forbid) execute 
things on the server using user input as arguments (like a CGI that 
goes and grep's files for you based on what you're searching for).

The gist is to try to filter all $q->param's first thing before 
anything else is ever done. Additionally I think we're using some CGI 
"taint" stuff too.

Another common filter:

    s/[^ -~]//g;

to strip all binary/non-printables out of user input.

- Bind variables work great here:

    DBI -> DBD::Informix -> esqlC -> Informix RDBMS

but don't work here:

    DBI -> DBD::Sybase -> freetds -> MS-SQL RDBMS

because freetds (MS-SQL?) doesn't support bind variables last time I 
looked. Regrettable, especially since it would be much faster not to 
have to prepare() statements over and over and over again every time 
one query variable changes...

Thanks for the tip!

j