Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot

Re: perl SQL injection prevent module

by szbalint (Friar)
on Feb 03, 2006 at 22:56 UTC ( #527841=note: print w/replies, xml ) Need Help??

in reply to perl SQL injection prevent module

If you use DBI, for your SQL needs in Perl, then you can use one of the features of DBI, placeholders and bind values.

While it won't check that the input you've specified is correct, it can help you avoid a good deal of issues concerning SQL injection and related attacks.

Replies are listed 'Best First'.
Re^2: perl SQL injection prevent module
by edwardt_tril (Sexton) on Feb 04, 2006 at 02:24 UTC
    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...]
      (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; ...
      You'll want to change those to:
      $sql = "select * from some_table where some_col = ?"; $sth = $dbh->prepare( $sql ); $sth->execute( $target );
      But if variables are being used like this:
      $sql = "select $colum from $table";
      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... }
      (update: added the necessary quotes around hash value assignments in this code snippet.)
        Right ... here is the WRONG example in Perl ... CODE: my $count; my $crypt_pass1 =crypt($args{pass},$args{username}); my $sth = $dbh->prepare("SELECT COUNT(id) FROM users WHERE username = '$args{username}' AND password ='$crypt_pass1'"); $sth->execute(); $sth->bind_columns(\$count); $sth->fetch; # If this counter count 1 --> there is a true combination if ($count == 1){ the result is: if you enter the following username (even without pass): xxx' or id ='1'# the counter will count 1 (true) for user with ID 1 ... the password is commented ... and we obtain the following mysql query: SELECT COUNT(id) FROM users WHERE username = 'xxx' or id = '1'# AND password ='$crypt_pass'

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://527841]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (3)
As of 2018-11-20 19:54 GMT
Find Nodes?
    Voting Booth?
    My code is most likely broken because:

    Results (231 votes). Check out past polls.