News for hackers. Stuff that matters.
Re:Up to 12M websites may have been compromised via Drupal v... by UnoriginalGuy on Saturday November 01, 2014 @10:00AM (#8543041)
Why does Drupal use string manipulation to generate SQL statements instead of bind parameters? It is 2014, hasn't this been best practice since at least the late 1990s? Wouldn't all security audits, including automated ones, pick this up?

And before someone makes a "joke" about PHP, they're using the PDO framework which supports bindParam(). Seems like woful incompetence in the Drupal codebase to me.

Exploit in detail: https://www.sektioneins.de/en/advisories/advisory-012014-dru...

Re:Up to 12M websites may have been compromised via Drupal v... by lucio on Saturday November 01, 2014 @12:25PM (#8543594)
They're using bind-params, but instead of using "?" as placeholder, they were using "named" placeholders, and, the placeholder name was constructed using the associative array key (externally received). The mistake was using a external provided value instead of using "?" or generating independent names for placeholders.
Re:Up to 12M websites may have been compromised via Drupal v... by jiggy2011 on Saturday November 01, 2014 @10:27AM (#8543142)
The query in question is using an IN clause which takes a tuple of values.

I don't think bindParam will allow you to bind an array into a tuple , the only way to pass the tuple is by forming it as a string and adding that to the query string.

Re:Up to 12M websites may have been compromised via Drupal v... by michh on Saturday November 01, 2014 @11:00AM (#8543256)
It's awful how poorly IN-clauses are supported (in lots of places, not just PHP) considering how often they're used and how useful they are. I mean basic stuff like being able to use them securely or making programmers write their own checks to prevent SQL-errors on empty lists.
Re:Up to 12M websites may have been compromised via Drupal v... by Maarten88 on Saturday November 01, 2014 @12:37PM (#8543642)
Not many people here will care, but SQL Server with .NET do support binding table valued parameters in lowlevel System.Data (SqlCommand).

I'm using a Micro ORM (Insight Database, https://github.com/jonwagner/Insight.Database) that takes advantage of this. It maps a parameter of type IEnumerable<SomeType> automatically to a TVP. This ORM works brilliantly when you want to get everything out of SQL.

Re:Up to 12M websites may have been compromised via Drupal v... by opendais on Saturday November 01, 2014 @02:22PM (#8544039)
No, it doesn't. PHP can handle it fine and we've undergone multiple attacks and security audits [both daily automated ones and professionals by hand]. :/

The problem here was a mistake someone made, not a fundamental support problem with the language.

This is precisely why people mock PHP developers. :/ So many don't even understand how the language f'n works.

Re:Up to 12M websites may have been compromised via Drupal v... by michh on Saturday November 01, 2014 @02:51PM (#8544143)
I never said it was a problem with the language. I said it was poorly supported in a lot of platforms including PHP and I stand by that.

It has nothing to do with passing security audits or withstanding attacks, there's not a security flauw in the way PHP handles this because PHP or specifically the PDO framework relies on the user to implement this themself. There obviously can't be a security flaw in something which does not exist.

A quick Google search suggests it is not at all obvious to many how to do a parametrised query with an IN-clause using PDO. The highest ranking answer is this SO post: http://stackoverflow.com/a/1586650

Having to iterate the array yourself adding the right amount of placeholders and binding individual values is secure but a lot of boilerplate. Escaping values in PHP and concatting them in the old fashioned way ought to be safe but everyone switched to parametrised queries for a reason: in practice it's often fucked up which leads to security vulnerabilities. The last one, the find_in_set trick, is a clever kludge but a kludge nonetheless.

People shouldn't need to roll their own way to do this because that's where unnecessary mistakes get made.

Re:Up to 12M websites may have been compromised via Drupal v... by opendais on Saturday November 01, 2014 @04:40PM (#8544534)
> A quick Google search suggests it is not at all obvious to many how to do a parametrised query with an IN-clause using PDO.

Alot of programmers can't do FizzBuzz either. If you can't figure out how to iterate and count an array on your own...

I'm sorry but I have 0 sympathy.

Re:Up to 12M websites may have been compromised via Drupal v... by michh on Saturday November 01, 2014 @05:13PM (#8544640)
Just as I have no sympathy for programmers forgetting to always call mysql_real_escape_string and setting their encodings right in the old MySQL driver, it's not difficult to get right, but tons of people didn't and it made the web a worse place for everyone.

Plus, they might be able to figure out how to iterate and count an array but they might also figure out how to use implode instead which is less code and programmers tend to be lazy. And suddenly they've opened their app up to SQL injection because they forgot or are unaware they now need to do escaping despite using prepared statements.

And since their app might contain my data, I care about this and not just think "those idiots brought it upon themselves".

Re:Up to 12M websites may have been compromised via Drupal v... by altcognito on Saturday November 01, 2014 @11:34AM (#8543385)
Java has this issue as well for IN clauses.
Re:Up to 12M websites may have been compromised via Drupal v... by spullara on Saturday November 01, 2014 @01:59PM (#8543931)
JDBC 4, released in Java 6 in 2006 supports setArray. However, that doesn't mean that your database driver supports it safely.
Re:Up to 12M websites may have been compromised via Drupal v... by opendais on Saturday November 01, 2014 @02:20PM (#8544031)
You can write a function for it using ?'s being generated inside your code only. Its perfectly safe to do it that want and allows you to bind parameters.

The only explanation for not doing that is ignorance and/or incompetence. Mistakes happen but to claim its a language problem is incorrect.

http://php.net/manual/en/pdostatement.bindparam.php

<?php /* Execute a prepared statement by binding PHP variables */ $calories = 150; $colour = 'red'; $sth = $dbh->prepare('SELECT name, colour, calories FROM fruit WHERE calories < ? AND colour = ?'); $sth->bindParam(1, $calories, PDO::PARAM_INT); $sth->bindParam(2, $colour, PDO::PARAM_STR, 12); $sth->execute(); ?>

So what you do is you write a function to convert the array to a series of ?'s for the IN clause/tuple and then iterate through the array to bind the parameters.

Re:Up to 12M websites may have been compromised via Drupal v... by __david__ on Saturday November 01, 2014 @05:19PM (#8544656)
> You can write a function for it using ?'s being generated inside your code only. Its perfectly safe to do it that want and allows you to bind parameters.

Of course you can. Everyone here is saying you shouldn't have to. This is the perfect type of thing to move into the db library. That makes it much safer because you don't have to hand-roll the same stupid code in 50,000 places (and possibly fat-finger it once).

Re:Up to 12M websites may have been compromised via Drupal v... by opendais on Monday November 03, 2014 @10:47AM (#8551346)
It belongs in an ORM, not the language.
Re:Up to 12M websites may have been compromised via Drupal v... by jiggy2011 on Saturday November 01, 2014 @02:33PM (#8544072)
I don't think that would work in this case, because there is no way to bind a PHP array to an SQL tuple. Available param types includes string , int , bool but no array or tuple type.

http://php.net/manual/en/pdo.constants.php

You can write "SELECT calories FROM fruit WHERE name IN (? , ?, ?)" and then bind the parameters as strings but this will only work in cases where the length of the tuple is known and fixed. If you need to allow for a variable length tuple then you will need to concatenate the query string yourself.

Re:Up to 12M websites may have been compromised via Drupal v... by SapphireSun on Saturday November 01, 2014 @04:29PM (#8544493)
Just use a placeholder like (??) and flatten everything out. Translate (??) and array(1,2,3) into (?,?,?) with 1, 2, 3 as the binds.
Re:Up to 12M websites may have been compromised via Drupal v... by mappu on Sunday November 02, 2014 @12:56AM (#8545670)
Then what do you do if the passed tuple has zero length?

Dynamically rewriting the SQL isn't going to be tractable in all cases - doing `IN (NULL)` might be a valid value - and throwing an exception is poor form for an actually valid case.

Re:Up to 12M websites may have been compromised via Drupal v... by SapphireSun on Wednesday November 05, 2014 @12:45AM (#8560661)
Check for bad inputs? I'm not saying this is a cure all, but it's a lot better than not being able to use arrays at all.
Re:Up to 12M websites may have been compromised via Drupal v... by jiggy2011 on Saturday November 01, 2014 @04:49PM (#8544569)
I don't think PDO supports this AFAIK.
Re:Up to 12M websites may have been compromised via Drupal v... by SapphireSun on Wednesday November 05, 2014 @12:42AM (#8560656)
I had to implement it manually, but it works. FWIW, I'm using a custom wrapper around MySQLi though.
Re:Up to 12M websites may have been compromised via Drupal v... by opendais on Monday November 03, 2014 @10:50AM (#8551357)
You count the length of the array. You create the tuple with the appropriate number of ?'s. You then bind parameters.

No user input involved.

Re:Up to 12M websites may have been compromised via Drupal v... by opendais on Saturday November 01, 2014 @04:37PM (#8544525)
...

What part of you needed to write a function was unclear? o.O

Counting an array is a safe operation.

Re:Up to 12M websites may have been compromised via Drupal v... by jiggy2011 on Saturday November 01, 2014 @04:55PM (#8544581)
You need to count the array at runtime and convert to a tuple, the only way this can be done using PDO (AFAIK) is by appending the tuple to the string itself because PDO provides no tuple type.
Re:Up to 12M websites may have been compromised via Drupal v... by krapp on Saturday November 01, 2014 @10:16AM (#8543093)
>Why does Drupal use string manipulation to generate SQL statements instead of bind parameters?

Is 'elegance creep' a thing? I would be willing to bet money I don't have, that it's because someone thought it was more elegant and clever, and therefore just better.

Re:Up to 12M websites may have been compromised via Drupal v... by ams6110 on Saturday November 01, 2014 @10:15AM (#8543090)
Legacy? Tons of code to change, and users more interested in new features? No tests? Developer conceit about their own abilities to "do it right"? All the usual reasons any project has old code that exhibits bad practices by current standards.
Re:Up to 12M websites may have been compromised via Drupal v... by fuligo on Saturday November 01, 2014 @10:14AM (#8543083)
Generating SQL statements always involves string manipulation at some stage, doesn't it? I mean at some point even PDO has to send query strings to the SQL server.

The question is whether Drupal really needed to implement their own engine for this, though.

Re:Up to 12M websites may have been compromised via Drupal v... by UnoriginalGuy on Saturday November 01, 2014 @10:24AM (#8543129)
> Generating SQL statements always involves string manipulation at some stage, doesn't it? I mean at some point even PDO has to send query strings to the SQL server.

If your database supports it set PDO::ATTR_EMULATE_PREPARES to false. If not, I'd still take PDO's implementation over a million different implementations which other projects come up with such as this one.