Re: DBI Parameter Security

Because the DBI methods have no way of knowing the diffrence between what you should and shouldn't do (much like open), there is no way for it to dissallow multiple statements in one prepare. On that note, if $baz = "'a'; DROP TABLE blah" is acceptable SQL to the server, it is allowed by prepare.

And, here is a bit of a fix, look at the following :

## Badd
my $sth = $dbh->prepare('SELECT * FROM foo WHERE bar = ?');
$sth->execute($baz)

## Good
my $sth = $dbh->prepare("SELECT * FROM foo WHERE bar = '?'");
$sth->execute($baz)
[download]

The extra quotes would prevent the baz from getting out unless something was passed which was like $baz = "foo'; DROP TABLE blah; SELECT * FROM foo WHERE a = 'B"

In short, there is not definite fix, which goes back to using regexes to validate user entries before touching them to your database/system.

OH, a sarcasm detector, that’s really useful

Comment on Re: DBI Parameter Security Select or Download Code

Replies are listed 'Best First'.
Re: Re: DBI Parameter Security by chipmunk (Parson) on Jul 14, 2001 at 00:03 UTC
The extra quotes will prevent the question mark from being seen as a placeholder at all. Instead, the statement would search for rows where the column bar contains the literal string '?'. Except that you would get an error when you called execute($baz), because the SQL statement is expecting zero bind parameters and you gave it one bind parameter. As lhoward said, using placeholders is perfectly safe, because any data that the user enters will simply be passed to the database as a literal string. This is similar to putting user data in a regex with \Q; `/\Q$user_data/` is perfectly safe because all the metacharacters will be escaped.	[reply] [d/l]
Re: Re: DBI Parameter Security by MZSanford (Curate) on Jul 13, 2001 at 21:33 UTC
per lhoward, it appears i may be wrong ... but, <soapbox>user supplied data should be checked, and should not have DROP rights.</soapbox> OH, a sarcasm detector, that’s really useful	[reply]