An injection attack results in a valid SQL statement (or multiple valid SQL statements). You can't check a query for an injection attack. Fortunately, there are two ways of sidestepping the problem.

Use bind to specify arguments instead of building the query string from user input.

Alternatively, use $dbh->quote to escape user data when building a query string from user input.

Both of these functions are documented in DBI.

By the way, the name of the language is "Perl", not "PERL".

Hi monks. Ths situation is that there are sql statements
everywhere (hundres, scattered around) in the code and I am hoping to fix it with
minimal invasive changes. The type of sql statements
varies but all centered around query "Select". Is there a
way to sink all the queries to a class or subroutine do
the DBI bind and quote as suggested then do prepare. But the class/subroutine is
smart enough to figure out the how many parameters to bind? Any code sample? Thanks

Maybe you could try turning on "taint mode" by putting "-T" on the shebang line of the script, or simply running the script from the command line as follows:

 perl -T your_script [args...]
[download]

(Update: just noticed the later reply from tantarbobus -- heed that as well.)

That might be overkill, because it will cause perl to complain (and the script to die) if any variable derived from outside the script is used to affect anything else outside the script. This should include passing tainted variables to DBI calls, along with a wide variety of other things. See perlsec for more details about taint-checking.

If you go that route, you'll probably end up fixing a lot of other things besides potential SQL injection attacks. And maybe this would be a Good Thing -- if such attacks are a serious concern for you, then there might be other things to worry about as well. It'll end up being "invasive", but better that you should be the invader than someone else. ;)

You might want to do some triage on the script, to assess how much work needs to be done: just grep through the code for calls to the DBI methods that pass SQL text (prepare, do, select.*), to see how many different variables are being passed to these methods. (If all these calls involve string constants, you're done -- no room for injection attacks in that case.) Then you have to track down where and how values are being assigned to those variables.

If you are seeing a lot of situations like this:

$sql = "select * from some_table where some_col = $target";
$sth = $dbh->prepare( $sql );
$sth->execute;
...
[download]

You'll want to change those to:

$sql = "select * from some_table where some_col = ?";
$sth = $dbh->prepare( $sql );
$sth->execute( $target );
[download]

But if variables are being used like this:

$sql = "select $colum from $table";
[download]

and those variables are set by input from untrusted sources, then the only real protection is to untaint those values.

Probably the best way is to set up a hash of column names and/or table names as needed (or a hash of whole query strings), then check each input to see if its value exists as a hash key. If so, use the hash value (which originates in your own code and so can be trusted) in order to form the query -- e.g.:

my $cols = ( foo => 'foo', bar => 'bar', baz => 'baz' );
my $tbls = ( parts => 'parts', table2 => 'table2' );

my $inp_col = <USER>;  # get data from untrusted sources
my $inp_tbl = $ENV{TABLE};

if ( exists( $cols{$inp_col} ) and exists( $tbls{$inp_tbl} ) {
   my $sql = "select $cols{$inp_col} from $tbls{$inp_tbl}";
   # now it's safe to run the query...
}
[download]

(update: added the necessary quotes around hash value assignments in this code snippet.)

DBI comes with something to helps you with making sure that you code is clean wrt sql injection attacks. When you connect to the database you can add a  TaintIN => 1 to the dbi connection attribute hash. Then if you run your code in taint mode, DBI will barf when you try to use a tainted value.