J.J. wrote:
> Hey,
>
> 2-parter here, both involving quote()...
>
> (System: Apache 1.3.27, MySQL 3.23.55, Perl 5.8.0)
>
>
> 1) Does quote() not recognize hashes?
>
> I had a line:
> $emailq = $dbh->quote($in{'email'});
>
> And through testing found it wasn't working.
What was it doing? Examples of input and output data?
It doesn't look like you're passing it a hash anyway, you're just passing
it a scalar that is an element of a hash.
> However:
> $email = $in{'email'};
> $emailq = $dbh->quote($email);
>
> Also works:
> $emailq = $dbh->quote("$in{'email'}");
> Putting quotes around the hash.
>
> Is there a reason why the first line doesn't work? Was quite
> surprised to finally figure this out, since most quote() commands go
> around regular string variables. It was working on those, so I had no
> clue it wouldn't work correctly on a hash.
>
> 2) SQL Injection - I've read a few pages about this, and I'm going
> through all my code to ensure this (and other security issues) are
> dealt with.
>
> The pages I read that were specific to DBI all seemed to be very
> reliant on quote() working correctly.
>
> Is quote() really enough to deal with SQL injection though? I'm also:
The best way to avoid SQL injection is to use placeholders, and never have
data mixed in with your SQL in the first place.
On databases that have native placeholder support (e.g. Oracle), the data
gets bound separately, and it makes your SQL statements reusable (as you can
bind different values to the placeholders and it doesn't need to reparse the
SQL, just reexecute it).
On databases that don't natively support placeholders (e.g. MySQL), DBI
emulates them, doing the quote()'ing behind the scenes for you, quoting in
whatever way is appropriate for the database.
i.e. don't do:
my $sth = $dbh->prepare('insert into tab (c1) values (' .
$dbh->quote($user_input) . ')');
$sth->execute();
Instead do:
my $sth = $dbh->prepare('insert into tab (c1) values (?)');
$sth->execute($user_input);
(Or use bind_param to bind the data).
Note that there's no quotes around the ? in the statement even if it's a
character type; '?' would be a literal ? character, whereas a bare ? is a
placeholder for any type.
> a. ensuring numbers are actually numbers
> b. checking input against maximum allowed lengths for that input
This is a sensible thing to do anyway, particularly on MySQL with its habit
of silently converting invalid input. If you're using placeholders, not
checking this shouldn't contribute to SQL injection attacks though.
--
Andy Hassall ([EMAIL PROTECTED]) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
