Thread: ISO guidelines/strategies to guard injection attacks
I have a Perl CGI script (using DBD::Pg) that interfaces with a server-side Pg database. I'm looking for general guidelines/tools/strategies that will help me guard against SQL injection attacks.
Any pointers/suggestions would be much appreciated.
~K
On 1/19/2010 3:23 PM, Kynn Jones wrote:
> I have a Perl CGI script (using DBD::Pg) that interfaces with a
> server-side Pg database. I'm looking for general
> guidelines/tools/strategies that will help me guard against SQL
> injection attacks.
>
> Any pointers/suggestions would be much appreciated.
>
> ~K
>
prepare your queries:
my $q = $db->prepare('select something from table where key = $1');
$q->execute(42);
and..
$db->do('update table set field = $1 where key = $2', undef, 'key', 42);
(*guessed at the do(). I think there is an undef in there, or something*)
-Andy
On 1/19/2010 3:39 PM, Andy Colson wrote:
> On 1/19/2010 3:23 PM, Kynn Jones wrote:
>> I have a Perl CGI script (using DBD::Pg) that interfaces with a
>> server-side Pg database. I'm looking for general
>> guidelines/tools/strategies that will help me guard against SQL
>> injection attacks.
>>
>> Any pointers/suggestions would be much appreciated.
>>
>> ~K
>>
>
> prepare your queries:
>
> my $q = $db->prepare('select something from table where key = $1');
> $q->execute(42);
>
> and..
> $db->do('update table set field = $1 where key = $2', undef, 'key', 42);
>
> (*guessed at the do(). I think there is an undef in there, or something*)
>
> -Andy
>
Also, add to that, in general, use Taint Mode. Perl wont trust data
until its been sanitized... and neither should you.
I have a little helper function:
sub untaint
{
$_[0] =~ /(\w+)/;
return $1;
};
Then later on:
my $xpin = untaint($web->param('pin'));
This makes sure the pin param only contains word characters (so no
dashes, slashes, quotes, or any other crap).
-Andy
On Tue, Jan 19, 2010 at 4:49 PM, Andy Colson <andy@squeakycode.net> wrote:
Also, add to that, in general, use Taint Mode. Perl wont trust data until its been sanitized... and neither should you.On 1/19/2010 3:39 PM, Andy Colson wrote:On 1/19/2010 3:23 PM, Kynn Jones wrote:I have a Perl CGI script (using DBD::Pg) that interfaces with a
server-side Pg database. I'm looking for general
guidelines/tools/strategies that will help me guard against SQL
injection attacks.
Any pointers/suggestions would be much appreciated.
~K
prepare your queries:
my $q = $db->prepare('select something from table where key = $1');
$q->execute(42);
and..
$db->do('update table set field = $1 where key = $2', undef, 'key', 42);
(*guessed at the do(). I think there is an undef in there, or something*)
-Andy
I can't get this to work in any way. At the end of this email, I post a complete script that runs fine under Taint Mode, even though it DBI is being passed tainted variables in various places.
Do I need to do anything else to force a failure with tainted data?
Demo script below; to run it, it requires four command-line arguments: the name of a database, the name of a table in that database, the name of an integer-type column in that table, and some integer. E.g., a run may look like this:
$ perl -T demo_script.pl mydb mytable mycolum 42
1
1
1
1
1
1
NB: you will need to modify the user and password parameters in the call to DBI->connect.
The important thing to note is that the connect, prepare, and execute methods all receive tainted arguments, but run without any problem. Furthermore, the subsequent fetchall_arrayref also runs without any problem.
Hence, at least in this example, -T was no protection against SQL injection attacks. Note, in particular, that the way that the $sql variable is initialized is an ideal opportunity for an SQL injection attack.
#!/usr/bin/perl
use strict;
use warnings FATAL => 'all';
use DBI;
my $dbname = shift;
my $tablename = shift;
my $colname = shift;
my $id = shift;
my $sql = qq(SELECT * FROM "$tablename" WHERE "$colname" = \$1;);
my $connection_string = "dbi:Pg:dbname=$dbname";
# when this script is run under -T, the output from all the following
# print statements is 1; if the script is *not* run under -T, then
# they are all 0.
print +(is_tainted($dbname) ? 1 : 0), "\n";
print +(is_tainted($tablename) ? 1 : 0), "\n";
print +(is_tainted($colname) ? 1 : 0), "\n";
print +(is_tainted($id) ? 1 : 0), "\n";
print +(is_tainted($connection_string) ? 1 : 0), "\n";
print +(is_tainted($sql) ? 1 : 0), "\n";
my $dbh = DBI->connect($connection_string,
"kynn", undef,
+{
RaiseError => 1,
PrintError => 0,
PrintWarn => 0,
});
my $sth = $dbh->prepare($sql);
$sth->execute($id);
my $fetched = $sth->fetchall_arrayref;
sub is_tainted {
# this sub is adapted from Programming Perl, 3rd ed., p. 561
my $arg = shift;
my $empty = do {
no warnings 'uninitialized';
substr($arg, 0, 0);
};
local $@;
eval { eval "# $empty" };
return length($@) != 0;
}
~K
On 1/21/2010 3:53 PM, Kynn Jones wrote:
> On Tue, Jan 19, 2010 at 4:49 PM, Andy Colson <andy@squeakycode.net
> <mailto:andy@squeakycode.net>> wrote:
>
> On 1/19/2010 3:39 PM, Andy Colson wrote:
>
> On 1/19/2010 3:23 PM, Kynn Jones wrote:
>
> I have a Perl CGI script (using DBD::Pg) that interfaces with a
> server-side Pg database. I'm looking for general
> guidelines/tools/strategies that will help me guard against SQL
> injection attacks.
>
> Any pointers/suggestions would be much appreciated.
>
> ~K
>
>
> prepare your queries:
>
> my $q = $db->prepare('select something from table where key = $1');
> $q->execute(42);
>
> and..
> $db->do('update table set field = $1 where key = $2', undef,
> 'key', 42);
>
> (*guessed at the do(). I think there is an undef in there, or
> something*)
>
> -Andy
>
>
> Also, add to that, in general, use Taint Mode. Perl wont trust data
> until its been sanitized... and neither should you.
>
>
>
>
> I can't get this to work in any way. At the end of this email, I post a
> complete script that runs fine under Taint Mode, even though it DBI is
> being passed tainted variables in various places.
>
> Do I need to do anything else to force a failure with tainted data?
>
> Demo script below; to run it, it requires four command-line arguments:
> the name of a database, the name of a table in that database, the name
> of an integer-type column in that table, and some integer. E.g., a run
> may look like this:
True, by default DBI ignores taint, you'll need to enable it:
my $dbh = DBI->connect($connection_string,
"kynn", undef,
+{
Taint => 1,
RaiseError => 1,
PrintError => 0,
PrintWarn => 0,
});
-Andy