DBI and stored procedures

[Anonymous Monk]

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?

[space_monk]

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

[Anonymous Monk]

use placeholders or $dbh->quote as usual, http://bobby-tables.com/

[Don Coyote]

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

[mbethke]

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 :)

[Don Coyote]

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.

[dsheroh]

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.