Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

DBI and stored procedures

by Anonymous Monk
on Nov 04, 2012 at 11:10 UTC ( #1002184=perlquestion: print w/replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,
do I have to validate all input before it gets into the stored procedure? (you can't have sql injection when using a sp,can you?)
The sp is not using dynamic sql but just uses the input as parameters to a WHERE clause.
For example if a parameter is of string type ie User_Name, should I validate it , ie check it for length and special characters, before it gets to the sp or is that not necessary?

Replies are listed 'Best First'.
Re: DBI and stored procedures
by space_monk (Chaplain) on Nov 04, 2012 at 13:10 UTC

    Yes, well it depends on how your stored procedures handle invalid data and errors, doesn't it?

    For example, if your stored procedure gets a string that is too long, does it fail gracefully, or does it crash? Does your program handle failures from the stored procedure call properly, or does it stop working completely?

    The fact that you are less likely to get SQL Injection doesn't mean that you shouldn't ensure that external input is reasonably sane before it goes too far in your program

Re: DBI and stored procedures
by Anonymous Monk on Nov 04, 2012 at 11:53 UTC
Re: DBI and stored procedures
by Don Coyote (Pilgrim) on Nov 04, 2012 at 13:38 UTC

    As an end user of database at work, I upload pictures and info to a website for the purpose of sales. Aside from the overall truly awful cms I am forced to endure/use, I am numerous times faced with redirect clauses in WHEREs crashes and the like on a frequent basis. It is stupendously annoying. Not to mention inefficient.

    But this is beside the point. It is reasonably straightforward to untaint data, prior to checking for SQL type compatibility.

    my $inputstring =~ s/^(\w+)$//; my $username = $1;

    (\w+) checks string for letters and underscore [0-9A-Za-z_] and stores the match in $1. The ^ and $ characters anchor the match to be from the begininng to the end of the string only, so no non-printable characters before or after. You may need to chomp the '\n' before putting through substition.

    See perlsec for more info on Taint and laundering data, and perlrequick for an intro to regexes

      But this is beside the point. It is reasonably straightforward to untaint data, prior to checking for SQL type compatibility. my $inputstring =~ s/^(\w+)$//; my $username = $1;

      Care must be taken though that all strings are received as UTF-8, otherwise \w matches only ASCII letters and not only Mr. 毛泽东 but also Assunção Verônica Álvares, Renée Bäcker and Kryštůfek Březový would get thrown to the "Go away, 33vu1 haxx0r!" page if they used their proper names spelled properly :)

        This would be the reason then, that when I looked up the \w in the regex tutorials \w is mentioned as matching alphanumerics, underscores and others. That was just a quick look. After going back just now, I can see there is a lot of information peppered throughout the various regex tutorials relating to the encoding.

        In the meantime, I have updated the Ascii \w in my post to include the numerics as well as the alphas. And added some links to perlsec and perlrequick.

Re: DBI and stored procedures
by dsheroh (Prior) on Nov 05, 2012 at 12:01 UTC
    Regardless of SQL injection concerns, you should be validating pretty much all entered data anyhow, except for perhaps freeform "comments" fields and the like.

    Is it a date field? You need to validate that it's a good date, and not 2009-02-29. Is it a credit card number? Validate that it's sixteen digits and the check digit is correct. Etc. Unless these things are being verified in the stored proc, you need to check them in code before sending it to the database.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1002184]
Approved by marto
[choroba]: I loved McVitie's, but stopped buying them when they started using palm oil

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (11)
As of 2017-11-20 13:00 GMT
Find Nodes?
    Voting Booth?
    In order to be able to say "I know Perl", you must have:

    Results (286 votes). Check out past polls.