4 ms·

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

by michh 12y ago

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.

opendais 12y ago
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.
- michh 12y ago
  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 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.
  - opendais 12y ago
    > 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.
    - michh 12y ago
      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".
altcognito 12y ago
Java has this issue as well for IN clauses.
- spullara 12y ago
  JDBC 4, released in Java 6 in 2006 supports setArray. However, that doesn't mean that your database driver supports it safely.
Maarten88 12y ago
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 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.

by michh 12y ago

opendais 12y ago

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.

michh 12y ago
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 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.
- opendais 12y ago
  > 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.
  - michh 12y ago
    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".

altcognito 12y ago

Java has this issue as well for IN clauses.

spullara 12y ago
JDBC 4, released in Java 6 in 2006 supports setArray. However, that doesn't mean that your database driver supports it safely.

Maarten88 12y ago

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 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.