Re: Use placeholders. For SECURITY!

[bradcathey]

I'm sorry I missed this thread when I needed it, which is now. I hope someone finds this reply...er, question when viewing Newest Nodes. I quote from the original post:

"You have code that interpolates form data directly into the string."

Do you still need placeholders for security if you are untainting all user form input?

CGI:

use Validate;
use strict;
use CGI qw(:standard);
    
    my $firstname = Validate->alphanum( param( 'firstname' ) );
    print "Missing or invalid first name\n" unless $firstname;
    
my $stmt = "INSERT INTO tablename VALUES ('$firstname')";
excute...

Module:

package Validate;

sub alphanum {
    my ($class, $value) = @_;
    return unless $value =~ /^([A-Za-z0-9 -]*)$/;
    return "$1";
}
1;

[tilly]

It is a judgement call, but I would call it still adviseable.

Your validation will not let through anything that can trigger a successful SQL injection attack. And you are therefore safe in not using placeholders. But other fields with different validations are not safe to use because they allow quote marks. If you are in the habit of skipping placeholders when not needed, then you are more likely to omit them when you do need them. Worse still, even if you don't, people who follow you are more likely to not understand the difference and get it wrong.

Further there is a maintainance issue. Suppose that you did the above for lastname. And then a customer named O'Connell complains about your application not allowing him to use his name. Well whoever gets the change request makes the obvious fix, and it is so simple that they don't really test it. (Yeah, I know..this kind of stuff happens in real life though. Bear with me.) And now your perfectly safe code has become unsafe instead. Why risk it?

Of course this is all in addition to major performance gains with some databases. Yeah, I know and have given the optimization rant plenty of times. But an issue that routinely causes a single point of failure in key systems to fail is worth being aware of.

[bradcathey]

Okay, tilly, you are making me a believer. Armed with Paul DeBois' book MySQL and Perl for the Web and your posts, I'm starting to get it.

The other thing I've been doing is escaping the quotes and apostrophes in strings (like "O'Connell") with $text =~ s/('|")/\\$1/g and it looks like placeholders takes care of all that for me. 1 stone, 2 birds. Thanks.