http://www.perlmonks.org?node_id=96482

pileswasp has asked for the wisdom of the Perl Monks concerning the following question:

As I understand it the DBI gives a bit of a syntax check in the prepare() method so any semi colons or whatnot in odd places is going to cause an error, but is there any way while doing something like: 

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

[download]

that someone could pass in unfortunate extra bits in $baz like, for instance, '; DROP TABLE big_important_one' on the back of that parameter (in the same way as with opening files you can pass '; rm -rf *')?

Replies are listed 'Best First'.
Re: DBI Parameter Security
by lhoward (Vicar) on Jul 13, 2001 at 21:23 UTC
    When you use DB placeholders you're protected from that kind of sillyness. All that will happen with your example query is that it will look for rows where the bar col contains ; DROP TABLE big_important_one. Its only when quoting by hand in a query that you have to worry about that kind of attack. DB calls using placeholders are one of the few places that you can generally trust that maliciously malformed user data won't be able to cause harm.

    Note: if your DB does not support placeholders, and the DBD simulates them for you; you could still get into trouble if its not done properly. However, I don't know of any DBD that does this...

[reply]
      Thanks for the reply. That's what I expected for that particular example, but - excusing the naiive example above as only a 'for instance' - does anyone know of a way that, without extra checking on the part of the developer, such a statement could be passed into the d/b.
[reply]
        As long as you're using placeholders (and the DBI layer doesn't shortcut them), tehre is no way that data will leak through as commands to the database.

        Turning on DBI tracing you can see exactly what is happening 

        <- prepare('select * from foo where bar=?' CODE)= DBI::st=HASH(0x81a25
+74) at testph.pl line 11.
<- execute('plugh' CODE)= 1 at testph.pl line 12.

        [download]
        You can from the trace see how the commands are passed to the database seprately from the data (using mySQL in this example). The DB is smart enough to not treat data as executable sql code.
[reply]
[d/l]
SQL Injection Attacks
by pileswasp (Monk) on Feb 03, 2003 at 13:00 UTC
    (Just as a follow up in case anyone ever comes back here)

    Turns out that was the phrase I was grasping for.

    There's an interesting article here:
      http://online.securityfocus.com/infocus/1644
    and some white papers available here:
      http://www.nextgenss.com/research/papers.html
    in case anyone's interested (swiped from the DBI mailing list).

    While it's a problem if you're going to be building your SQL into a string yourself ("SELECT $idiot FROM $fool WHERE $twit"), using the DBI's placeholders seems to be a damn fine step towards protecting your scripts against this kind of thing.

[reply]
[d/l]
Re: DBI Parameter Security
by MZSanford (Curate) on Jul 13, 2001 at 21:30 UTC
    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
[reply]
[d/l]
[select]
      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]
      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]

Back to Seekers of Perl Wisdom