5 ms·

> It's insane that there's no way to pass an array of values into a parameterised query for that use case. Maybe I'm misunderstanding you, but assuming $params

> It's insane that there's no way to pass an array of values into a parameterised query for that use case. Maybe I'm misunderstanding you, but assuming $params is an array in the following code, isn't this passing an array into a parameterized query for that use case? (Edited to note this is literally an example from the PHP documentation, and not one of the squiffy comments.) $place_holders = implode(',', array_fill(0, count($params), '?')); $sth = $dbh->prepare("SELECT id, name FROM contacts WHERE id IN ($place_holders)"); $sth->execute($params); In Python, using MySQLdb, I believe this would be something like place_holders = ','.join(['%s'] * len(params)) cursor.execute("DELETE FROM foo.bar WHERE baz IN (%s)" % place_holders, tuple(params)) Which, while more succinct, seems to be functionally exactly the same thing. I don't see what PHP is doing that's "worse" here offhand. One could argue, "Yes, but a Python programmer would use SQLAlchemy," which is probably true, but then you need to let the PHP programmer use Doctrine or Eloquent.

sellyme 5y ago
> isn't this passing an array into a parameterized query for that use case? Yes, I definitely should have been more specific there - what I'm referring to is passing it as one parameter, instead of potentially dozens or hundreds. There's a lot of ways to do this safely, but none of them are elegant. In the example presented here I believe it's the case that you can't do the ->execute($params) and bind some parameters explicitly, so if you had something like " AND status = ? AND due_date < ?" at the end of your query you have to chuck those variables into the same nondescript array. I prefer the looped bindParam() method for this reason, but that has its own challenges. Firstly, it requires some boilerplate (not a big deal, but no-one likes writing boilerplate), and more pressingly it still has the issue where it actually is each individual element of the array being parameterised, and spams the ever-loving crap out of any debug outputs. Obviously all of these issues are way less concerning than SQL injection vulnerabilities, but life would be so much easier if you could just do $sth->bindParam(1, $params); on a single question mark, and have that show up logically in things like debugDumpParams(). Even if you had to use special syntax to indicate when a parameter is expected to be an array, that would be a huge improvement. I'm sure there's technical reasons why this is more difficult to implement than it would initially seem, but I've seen enough string-concatenated queries on StackOverflow from people who just give up on getting the parameterisation to play nicely that I believe it's worth the effort to make doing things right as frictionless as possible.
- ipaddr 5y ago
  How often are you passing dozens or hundreds of parameters to a single sql statement? Maybe there is a better way to structure things.
  - sellyme 5y ago
    How often am I doing it? Not very. But when there's a WHERE foo IN(a,b,c,...) query that has an arbitrary list as input? Could be any number of parameters in there (although I think most SQL drivers start complaining in the early quadruple digits).

sellyme 5y ago

> isn't this passing an array into a parameterized query for that use case? Yes, I definitely should have been more specific there - what I'm referring to is passing it as one parameter, instead of potentially dozens or hundreds. There's a lot of ways to do this safely, but none of them are elegant. In the example presented here I believe it's the case that you can't do the ->execute($params) and bind some parameters explicitly, so if you had something like " AND status = ? AND due_date < ?" at the end of your query you have to chuck those variables into the same nondescript array. I prefer the looped bindParam() method for this reason, but that has its own challenges. Firstly, it requires some boilerplate (not a big deal, but no-one likes writing boilerplate), and more pressingly it still has the issue where it actually is each individual element of the array being parameterised, and spams the ever-loving crap out of any debug outputs. Obviously all of these issues are way less concerning than SQL injection vulnerabilities, but life would be so much easier if you could just do $sth->bindParam(1, $params); on a single question mark, and have that show up logically in things like debugDumpParams(). Even if you had to use special syntax to indicate when a parameter is expected to be an array, that would be a huge improvement. I'm sure there's technical reasons why this is more difficult to implement than it would initially seem, but I've seen enough string-concatenated queries on StackOverflow from people who just give up on getting the parameterisation to play nicely that I believe it's worth the effort to make doing things right as frictionless as possible.

ipaddr 5y ago
How often are you passing dozens or hundreds of parameters to a single sql statement? Maybe there is a better way to structure things.
- sellyme 5y ago
  How often am I doing it? Not very. But when there's a WHERE foo IN(a,b,c,...) query that has an arbitrary list as input? Could be any number of parameters in there (although I think most SQL drivers start complaining in the early quadruple digits).