Hacker News Reader

Comment by laumars | Hacker News Reader

I love how he is building his own SQL string and then blaming Perl's DBI->quote for any vulnerabilities that arise. Anyone who writes SQL like that is writing bad code from the offset (regardless of the programming language nor it's DB/web frameworks). Parametrised queries and ORMs exist to prevent the kind of SQL injection attacks he's demonstrating and Perl's various DBD modules already support parametrised queries (in fact most Perl DBI guides will walk you through using them!).

This is pretty much "working with databases 101" and if he can't get even that much right then he should not be stood in front of people lecturing about the evils of any language nor it's framework.

Sadly though, these days proper security research is less important than looking cool in front of a small crowd. After all, who needs to have any knowledge about your subject if you can curse a little and display a few slides of some tired old internet memes. :/

View on HN

? Where's the string building in this code?

    my $otheruser = Bugzilla::User->create({
        login_name => $login_name, 
        realname   => $cgi->param('realname'), 
        cryptpassword => $password});

I was referring to the part where he did the following:

    'select * from users where username='.$dbh->quote($cgi->param('user'));

He was discussing the escaping of values inside SQL (https://www.youtube.com/watch?feature=player_detailpage&v=gw...) where he talks about how DBI->quote doesn't always escape values as expected.

I'd say that the problem has more to do with SQL and respective APIs than anything else. The fact that SQL injection happens so freaking often despite the fact that it's pretty simple to avoid should speak volumes.

How would you solve this problem?

Parameterized queries are already a thing and ORMs are all the rage ;)

I prefer ORMs, but they're a lot of overhead to get set-up so I don't blame others for using strings where appropriate... and every parametrized query API I've ever used has been a tremendous PITA which is why people throw up their hands and say "screw it, I'll just escape the quotes!". And of course, the database API could provide a good method to do that but nobody ever does because it's impossible to do it securely (and that's definitely a fault in SQL).

Have you used parameterized queries in Perl? It's the same DBI API and only about 3 or 4 additional lines. Plus you can easily abstract that away into a little helper function if you desired.

I'm on my phone at the moment but I'd be happy to post some example code later if you want it?

I think

    $sth = $dbh->prepare("SELECT document FROM table WHERE tag=? AND security_level=?");
    $sth->execute(foo(), $user_level);

breaks as well, if foo() unexpectedly returns a list.

Breaks as in "SQL injection is possible" or breaks as in "invalid number of parameters"?

If the list contains 2 elements, it will overwrite the second parameter, so the total number of arguments is still the same.

Ah yes, but that's a slightly different feature to the one demonstrated by the author (https://www.youtube.com/watch?feature=player_detailpage&v=gw...). However that's not to say that the vulnerability you raised isn't also a serious one developers need to be mindful of.

The following won't break (at least not in the bad way), I don't think:

    $sth = $dbh->prepare("SELECT document FROM table WHERE tag=? AND security_level=?");

    $sth->bind_param(1,foo());

    $sth->bind_param(2, $user_level);

    $sth->execute();

Indeed it looks like it can, but this is still a non-issue if you understand context (a key feature of Perl) and know what foo does

Reply on news.ycombinator.com