What your framework never told you about SQL injection protection | Hacker News Reader

What your framework never told you about SQL injection protection

codeyellow.nl

4 pointsPHPdouche5 comments

The PHP world is so adorable. It's like a bunch of little puppies, running into each other and falling over and tumbling around.

As the article mentions, this issue is not limited to PHP. Many ORMs have similar issues. For instance, I recently ran into essentially the same issue with DBIx::Class in Perl. The problem there was further compounded by the fact that many subroutines in Perl behave differently in different contexts, meaning that code like this may not be safe:

$resulset->search({ column1 => some_sub($arg), column2 => $value2, ...});

Many subs may behave in unexpected ways when using list context, such as returning an empty list. You may end up with values, which are normally escaped, becoming keys in the hashref. DBIC only does the simplest of checks on the key values, leaving your code open to an SQL injection.

This sort of issue is unfortunately prevalent. It is not just limited to PHP and dismissing it as such does everyone a disservice.

DBIC maintainer here: did you ever report the problem? Looking through my queues I don't see anything related, but perhaps I missed a report.

I am interested in removing all and any attack vectors, but I can't fix problems without knowing about them. So please do get back to me on this.

Cheers

I'm a little lost here. Why would you give the option to the user to sort by any column in the database? Wouldn't a better idea to give the user a bunch of labels on the displayed columns, then map between those and your predetermined sort orders?

You'd then have to validate the input (horrors!) and then add the appropriate sort order to the query (builder) in your own code.

Thus you present the user with column labels such as "Item", "Description", "Weight", "Packing volume", "List Price", which internally map to something like %sort_orders = { 'Item' => \&item_sort_order, 'Description' => \&description_sort_order, … } with your code doing a lookup along the lines of if exists $sort_orders{$request_sort_label} …

Of course this isn't going to be as simple and elegant as allowing user input to pass directly into your database engine.

But then for every problem there is at least one answer which is simple, elegant, and completely wrong.

Well this is just depressing (although it looks like these have been fixed by now in most of the frameworks mentioned,) as "just use a framework" has been my go to response to people starting new PHP projects because I had assumed they had SQL injection taken care of.

This isn't a PHP specific problem of course but I do wonder if it is a sign that frameworks in general shouldn't come with their own ORMs. Although, having seen PHP running in the wild that doesn't even bother escaping strings into SQL, I have to think that at the very least using a framework makes things safer if not safe, and makes fixing this crap easier.

Reply on news.ycombinator.com