Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

Re: Preventing malicious T-SQL injection attacks

by davorg (Chancellor)
on Mar 05, 2007 at 12:54 UTC ( #603204=note: print w/ replies, xml ) Need Help??


in reply to Preventing malicious T-SQL injection attacks

I second the idea of whitelisting the acceptable values for $SPROC and also using placeholders to insert the elements of @CHOICE into the SQL.

Combining the two ideas, might give something like this:

# %procs contains the names of the valid stored procs # together with the number of parameters each requires my %procs = ( proc1 => 2, proc2 => 0, proc3 => 1, # ... ); unless (exists $procs{$SPROC}) { die "Unknown stored proc: $SPROC\n"; } my $sql = "EXEC $SPROC ". join ', ', ('?') x $procs{$SPROC}; my $sth = $dbh->prepare($sql); $sth->execute(@CHOICE);

This code also has the advantage of dieing if the number of elements in @CHOICE doesn't match the expected number of parameters.


Comment on Re: Preventing malicious T-SQL injection attacks
Download Code
Re^2: Preventing malicious T-SQL injection attacks
by Win (Novice) on Mar 05, 2007 at 14:30 UTC
    Thanks for your post which raises useful points. I have three questions. Why is the hash labels ordered 2,0,1 (I'm very easily confused) ? Also, why do we have sth->execute(@CHOICE);. And, why does the EXEC die if there is not the expected number of elements in the array. Is that because of the prepare statement?
      Why is the hash labels ordered 2,0,1 ?

      I thought the comments above the hash explained that. "%procs contains the names of the valid stored procs together with the number of parameters each requires". The key of each hash entry is the name of a valid stored proc. The value associated with the key is the number of parameters that each stored proc requires. The actual numbers that I used (2, 0, 1) were just sample numbers that I made up at random.

      why do we have sth->execute(@CHOICE);

      That is how you put values into the placeholders in an SQL statement. So if you have an SQL statement that is something like select foo from bar where baz = ? then you pass the value for baz as a parameter to the execute function. If you have more than one placeholder (as we do in this case) then we can pass a list (or, in this example, an array that is converted to a list) instead.

      Of course, you could have got all this from the DBI documentation.

      Why does the EXEC die if there is not the expected number of elements in the array

      If you have placeholders in your SQL statement, then execute must be passed enough parameters to match all of the placeholders. If there are too many or too few parameters then execute will throw a fatal error.

        Can I also question the need to specify the number of variables that each SPROC can take. Because a SPROC won't allow an execution if an incorrect number of variables is specified.
Re^2: Preventing malicious T-SQL injection attacks
by Win (Novice) on Mar 05, 2007 at 18:19 UTC
    I've tested this bit:
    my $sql = "EXEC $SPROC ". join ', ', ('?') x $procs{$SPROC};
    It doesn't work. It produced a load of '?' in a row. and removing the quotes does not fix the problem. Also, I am having problems adapting the code like follows:
    my @procs = qw/recept/; unless (exists $procs{$SPROC}) { die "Unknown stored proc: $SPROC\n"; }
    Clearly there is a difference between hashes and arrays when it comes to using the exist function.

      I've tested this bit:

      my $sql = "EXEC $SPROC ". join ', ', ('?') x $procs{$SPROC};

      It doesn't work. It produced a load of '?' in a row.

      Erm... yes. That's what it is supposed to do. It produces an SQL statement with the correct number of placeholders in it (a placeholder is marked with a question mark).

      What were you expecting it to produce?

      Also, I am having problems adapting the code like follows:

      my @procs = qw/recept/; unless (exists $procs{$SPROC}) { die "Unknown stored proc: $SPROC\n"; }

      Clearly there is a difference between hashes and arrays when it comes to using the exist function.

      Clearly :-)

      For example. hashes are indexed with strings and arrays are indexed with integers. So trying to see if a string key exists in an array is always going to be doomed to failure.

      But actually, that's not what you're doing is it? You're setting up an array and then looking for a key in a non-existant hash.

      Has someone recommended that you use "strict" and "diagnostics" in your code? Because that would have explained what your problem is here.

        Now I am really confused because I have no idea how I can make use of that series of question marks.
Re^2: Preventing malicious T-SQL injection attacks
by Win (Novice) on Mar 05, 2007 at 18:37 UTC
    What is the best way of checking that the looked up value in the hash is the same as $elements_in_array for the key $SPROC?
    my %procs = ( reception => 29 ); unless (exists $procs{$SPROC}) { die "Unknown stored proc: $SPROC\n"; } $Command = join(' ', 'EXEC', $SPROC, join(', ', @CHOICE[1 .. $elements_in_array])) . '';

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (13)
As of 2014-07-23 12:38 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (142 votes), past polls