Rather than escape values yourself, using SQL placeholders lets your database driver escape the values for you. It's a lot easier and safer. If you were to post some example database code, someone would surely show you how to use placeholders instead.

You are right, and in the long-run, that's what will probably be done, but that's a lot of work and testing. This is to "get us by" in the meantime.

Not to sound like a grumpy old man, but "gets us by" is exactly how insecure systems are created and forgotten. If your goal as stated is to 'prevent SQL injection' then there is only one answer: use place holders.

my %params = map { $_ => $cgi->param($_) } $cgi->param;
[download]

Update: to allow for params with multiple values:

my %params;
for my $name ($cgi->param) {
    my @values = $cgi->param($name);
    $params{$name} = @values > 1 ? \@values : $values[0];
}
[download]

Thank you... Interestingly, when I use this code:

use warnings;
use strict;
use CGI;

use Data::Dumper;
print "Content-type: text/html\n\n";

my $cgi = CGI->new();
my %params = map { $_ => $cgi->param($_) || '' } $cgi->param;

print Dumper \%params;
[download]

The output is when I pass test.pl?a=\'b is:

$VAR1 = { 'a' => '\\\'b' };
[download]

... so it looks like something in the stack is already taking care of the escaping for me. Am I worrying about nothing?

Data::Dumper is doing that - try this:

for my $param (keys %params) {
    print "$param: $params{$param}<br>"
}
[download]

Data::Dumper does perl-escaping (defaults have some caveats)

Data::Dump::pp() does better perl-escaping by default

Neither ddumper does HTML-escaping

You can alway do  my $cgi = CGI->new; print $cgi->header, $cgi->Dump ; to see whats inside $query

s/_/\_/g


s/_/\_/g does nothing. You probably want  s/_/\\_/g

>perl -wMstrict -le
"$_ = 'a_b__c___';
 print qq{before: '$_'};
 ;;
 s/_/\_/g;
 print qq{after:  '$_'};
"
before: 'a_b__c___'
after:  'a_b__c___'
[download]

But please allow me to add my voice to the chorus imploring you to Just Use Placeholders!.

I've distilled all this advice this as best I can.

============
something.lib
============
sub safer {
    my $hash = shift;
    my %safer;
    while (my ($k, $v) = each %$hash) {
        s/\\//g         for $k, $v;
        s/0x00//g       for $k, $v;
        s/0x08//g       for $k, $v;
        s/0x09//g       for $k, $v;
        s/0x0a/\n/g     for $k, $v;
        s/0x0d/\r/g     for $k, $v;
        s/"/\\"/g       for $k, $v;
        s/%/\\%/g       for $k, $v;
        s/'/\\'/g       for $k, $v;
        s/_/\_/g        for $k, $v;

        $safer{$k} = $v;
    }
    return %safer;
}

================
something.cgi...
================
use warnings;
use strict;
use CGI;
use CGI::Carp;

print "Content-type: text/html\n\n";

# marker
my $cgi = CGI->new();

$cgi->param;
my %params;

for my $name ($cgi->param) {
    my @values = $cgi->param($name);
    $params{$name} = @values > 1 ? \@values : $values[0];
}

%params=safer(\%params);
# marker

for my $param (keys %params) {
    print "$param: $params{$param}<br>"
}
[download]

The stuff between the two markers will replace the existing

sub main
{
        my $cgi = CGI->new();
        my %params = $cgi->Vars();
[download]

... in the existing scripts. This is running under mod-perl (w/ regcooker). Are there any "gotchas" I should be aware of here? Thanks to all you monks for all your help!

A problem is with CGI->Vars , you never want to use CGI->Vars, CGI->Vars is for perl4, Vars mangles (encodes, serializes, packs, implodes) the data, its backwards compatibility for some 1993 stuff

You want "escapeHTML" from CGI.pm

#!/usr/bin/perl
use warnings;
use strict;

use Data::Dumper;

sub safer {
    my $hash = shift;
    my %safer;
    while (my ($k, $v) = each %$hash) {
        s/'/\'/g for $k, $v;
        $safer{$k} = $v;
    }
    return %safer
}


my %params = ('a b'   => 'c d',
              "a'b"   => "c'd",
              'a"b'   => 'c"d',
              'a`b'   => 'c`d',
              "a\\'b" => "a\\'b",
             );

my %safer = safer(\%params);

print Dumper \%safer;
[download]

Thank you! Is it necessary to have

my %safer = safer(\%params);
[download]

or would

safe(\%params);
print Dumper \%params;
[download]

be equally good?

It might be possible if you changed the definition of the function. You can always do

%params = safer(\%params);
[download]

لսႽ† ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ

Have you tried?

That is not a guaranteed method to prevent SQL injection. If instead you use placeholders and bind arguments you will catch the injections.

I know it's not perfect -- just looking for something untikl we get a more permanent soultion in place like what you suggested.

This exactly the wrong thing to do. You gain false sense of security and end up with invalid data in the database and displayed. We all remember PHP-based sites displaying thing's like d\'Artagnan ...

Every minute spent quoting variables by hand and working around the endless stream of problems it will inevitably lead to, is a complete waste.

Just rewrite the queries to use placeholders, because sooner or later that's what you will end up having to do. Not only does it solve all problems related to SQL injection and special characters, it also makes your code easier to read and maintain. With most databases it will even improve performance.

And no, it's not difficult either. Just replace this

my $sth = $dbh->prepare("
  SELECT * FROM foo WHERE bar=$qstring1 AND baz=$qstring2
"); 
$sth->execute();
[download]

with this

my $sth = $dbh->prepare("
  SELECT * FROM foo WHERE bar=? AND baz=?
"); 
$sth->execute($string1, $string2);
[download]

...it's really that simple!

Now, if you really REALLY must quote each variable because you have to build the queries on the fly, don't do the quoting yourself using regular expressions. Instead, use the DBD "quote" method conveniently provided for you. The following code works for simple cases:

It produces a copy of the original hash, with all values properly quoted for safe use by the database you happen to be working with.

Use the quote method of your database handle:

my $escapeId = $dbh->quote( $params{id} )