3 ms·

If there's anything more annoying than the lingering bad examples of PHP code online, it's people lying about what PHP "recommends", like you do. Common databa

If there's anything more annoying than the lingering bad examples of PHP code online, it's people lying about what PHP "recommends", like you do. Common database drivers like PGSQL, MySQL, SQLite etc. don't accept arrays of values for parameterized queries. This is at C level, in their own client libraries, and their own communication protocols. This means there's nothing specific to PHP about this problem. Many higher level libraries, including for PHP, abstract over this problem and do offer arrays by binding per query. So where are statements like yours coming from? Probably just being eager to say something bad about PHP without checking your facts too much.

sellyme 5y ago
> This means there's nothing specific to PHP about this problem. I don't particularly care if languages that I am not using have the same problem. It's up to people who use those languages to make those criticisms. > Many higher level libraries, including for PHP, abstract over this problem and do offer arrays by binding per query. Feel free to link an in-built PHP library that supports this feature, that would be far more useful than just obliquely suggesting that one exists.
- TrispusAttucks 5y ago
  PDO + Prepared Statements https://www.php.net/manual/en/pdo.prepared-statements.php https://www.php.net/manual/en/pdo.prepared-statements.php
  - sellyme 5y ago
    PDO explicitly does not support the functionality being discussed [1]: > For example, you cannot bind multiple values to a single parameter in the IN() clause of an SQL statement. You're required to roll your own implementation, something that thankfully isn't particularly difficult, but unfortunately also seems to be enough of a barrier that a lot of programmers don't bother. [1]: https://www.php.net/manual/en/pdo.prepare.php https://www.php.net/manual/en/pdo.prepare.php
    - noduerme 5y ago
      I'm pretty sure PDO doesn't support escaping WHERE/IN(...) because MySQL prepared statements don't support it. In theory, PDO could support it in emulation mode, but supporting something like that only in emulation would be an antipattern. Similarly, PDO doesn't support dynamic table names. You're expected to roll your own precisely because it demands more situationally specific escaping than regular parameters would, e.g. testing a list of allowed tables.
      - yourenotsmart 5y ago
        > would be an antipattern I just want to point out the above is identical to saying "is bad" without explaining why it's bad. We can say it doesn't align with PDO's goal of being barebones library that doesn't add non-native features on top. But that's not what it does. It both omits many native features on the various databases it supports, and has non-native features like hydrating objects, for some reason. I'd say if a PHP library is at the same level as the C library that backs it, that's a failure of design goals. C is intentionally low-level (by modern standards) and PHP is supposed to be high-level and consumable by people with much less clue than C programmers. It's unfortunate that you pretty much need a DBAL (like Doctrine DBAL) on top of a DBAL (PDO) to get some of the missing features. Like, say, escaping identifiers.
        tored 5y ago
        It is hard to design good API:s that can last for decades. Just look at Java, language that is backed by large international corporations but the Java standard library still sucks. PHP core is a much smaller community driven project. Key to great APIs is battle tested APIs in real world projects, finetuned over years of experience. PHP has that in the much larger general PHP community and to access it you use composer. We, the PHP community, must use all resources to compete with other languages, it is unrealistic that the core PHP team can implement a great API, for example we can look at the filter API, it works but it is not great. PHP core also has longer release cycles. We need to push PHP developers to use composer more, maybe the PHP docs should state that.
    - 5y ago
- Sander_Marechal 5y ago
  Doctrine DBAL: https://www.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html#list-of-parameters-conversion https://www.doctrine-project.org/projects/doctrine-dbal/en/l...
- geon 5y ago
  Are you just talking about prepared statements? Mysqli was released in 2004. https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php https://www.php.net/manual/en/mysqli.quickstart.prepared-sta...
nh2 5y ago
Maybe I'm misunderstanding you, but it looks like it is a PHP specific problem. Python does not have it. Of course at C level parameterised queries ("prepared statements") do not accept arrays. It would not make sense for them to do. This is because the placeholders can be substituted by values of different types. In typed programming languages, arrays have elements of the same type. For example in SQLite, you are supposed to call `sqlite3_bind_int()`, `sqlite3_bind_text()` [1], once for each query parameter. In languages like PHP and Python, where arrays can carry values of different types, their wrappers around the SQLite C functions can do this function calling for each value in the array. In Python, that is easy, the default, and explained in the very beginning of the official standard library's sqlite documentation [2]: It states at the very beginning that query construction by string construction is unsafe and must be avoided. It immediately provides an example of how to safely call a parameterised query with an array of values, using `execute()` and `executemany()`. PHP's standard library simply does not seem to have such an `execute()` function that accepts an array [3], nor do the official docs seem to contain any prose that could explain how to use the library safely [4]. The only way you can find out is by reading user-contributed comments on some specific functions in the function reference. So Python's standard library provides safe functions, and immediately instructs the user how to use them. PHP's does not. Unclear to me how one can conclude that this isn't a PHP specific problem. [1]: https://www.sqlite.org/c3ref/bind_blob.html https://www.sqlite.org/c3ref/bind_blob.html [2]: https://docs.python.org/3/library/sqlite3.html https://docs.python.org/3/library/sqlite3.html [3]: https://www.php.net/manual/en/sqlite3.prepare.php https://www.php.net/manual/en/sqlite3.prepare.php [4]: https://www.php.net/manual/en/class.sqlite3.php https://www.php.net/manual/en/class.sqlite3.php
- dreyfan 5y ago
  You're misunderstanding You cannot do something like the following: "WHERE status in (...?)" and then ->execute($status_array) But you can pass an array of parameters just fine (to individually bound input placeholders). It depends on which API you're using but it's of the format: ->bind_param($types, ...$params); or ->execute($params);
- ransom1538 5y ago
  "Maybe I'm misunderstanding you, but it looks like it is a PHP specific problem. Python does not have it." You can concatenate a sql query just fine in Python anyway you want. Adding a sql injection is just as easy in Python, PHP, or lisp. Thus this is a choice. Nothing to do with a language. Language bashing is gross and spreads lies. And yes, you can bind an array of params in PHP.
x0x0 5y ago
postgres does. PQexecParams, which executes a sql statement with parameters, accepts oids that are array types.
pjungwir 5y ago
I see what you're saying, but maybe "array" is the wrong term. Indeed you can't say `WHERE foo IN (?)` and pass more than one parameter (or an array parameter) for the single `?`. That's a limitation of the databases, not the programming languages. But Postgres does have arrays, and you can pass them as parameters. In fact that's how you solve this problem. An equivalent to `foo IN (...)` is `foo = ANY (array[...])`. So parameterized that would be `foo = ANY (?)`. Instead of a bunch of parameters, you have one parameter of array type. The parens here signify a subquery, not a list of single-attribute tuples. In fact using ANY is more expressive, since you can say `ANY (array[])` (but you might need to add a cast), but you can't say IN ()` (which is a syntax error). Anyway your point is true that lots of languages' client libraries and ORMs implement sql "parameters" by string substitution. That's still better than having the programmer do it himself, but not as good as it could be.
Akronymus 5y ago
> Common database drivers like PGSQL, MySQL, SQLite etc. don't accept arrays of values for parameterized queries. One workaround to that is passing the array as a string with some separator, deconstructing it into a temp table and then using that table as a array when it is part of a stored procedure.
- matharmin 5y ago
  Even better, you can pass it in a JSON array, and use the built-in JSON functionality to iterate through it. I started doing this in SQLite, and it made some queries a lot cleaner.

sellyme 5y ago

> This means there's nothing specific to PHP about this problem. I don't particularly care if languages that I am not using have the same problem. It's up to people who use those languages to make those criticisms. > Many higher level libraries, including for PHP, abstract over this problem and do offer arrays by binding per query. Feel free to link an in-built PHP library that supports this feature, that would be far more useful than just obliquely suggesting that one exists.

TrispusAttucks 5y ago
PDO + Prepared Statements https://www.php.net/manual/en/pdo.prepared-statements.php https://www.php.net/manual/en/pdo.prepared-statements.php
- sellyme 5y ago
  PDO explicitly does not support the functionality being discussed [1]: > For example, you cannot bind multiple values to a single parameter in the IN() clause of an SQL statement. You're required to roll your own implementation, something that thankfully isn't particularly difficult, but unfortunately also seems to be enough of a barrier that a lot of programmers don't bother. [1]: https://www.php.net/manual/en/pdo.prepare.php https://www.php.net/manual/en/pdo.prepare.php
  - noduerme 5y ago
    I'm pretty sure PDO doesn't support escaping WHERE/IN(...) because MySQL prepared statements don't support it. In theory, PDO could support it in emulation mode, but supporting something like that only in emulation would be an antipattern. Similarly, PDO doesn't support dynamic table names. You're expected to roll your own precisely because it demands more situationally specific escaping than regular parameters would, e.g. testing a list of allowed tables.
    - yourenotsmart 5y ago
      > would be an antipattern I just want to point out the above is identical to saying "is bad" without explaining why it's bad. We can say it doesn't align with PDO's goal of being barebones library that doesn't add non-native features on top. But that's not what it does. It both omits many native features on the various databases it supports, and has non-native features like hydrating objects, for some reason. I'd say if a PHP library is at the same level as the C library that backs it, that's a failure of design goals. C is intentionally low-level (by modern standards) and PHP is supposed to be high-level and consumable by people with much less clue than C programmers. It's unfortunate that you pretty much need a DBAL (like Doctrine DBAL) on top of a DBAL (PDO) to get some of the missing features. Like, say, escaping identifiers.
      - tored 5y ago
        It is hard to design good API:s that can last for decades. Just look at Java, language that is backed by large international corporations but the Java standard library still sucks. PHP core is a much smaller community driven project. Key to great APIs is battle tested APIs in real world projects, finetuned over years of experience. PHP has that in the much larger general PHP community and to access it you use composer. We, the PHP community, must use all resources to compete with other languages, it is unrealistic that the core PHP team can implement a great API, for example we can look at the filter API, it works but it is not great. PHP core also has longer release cycles. We need to push PHP developers to use composer more, maybe the PHP docs should state that.
  - 5y ago
Sander_Marechal 5y ago
Doctrine DBAL: https://www.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html#list-of-parameters-conversion https://www.doctrine-project.org/projects/doctrine-dbal/en/l...
geon 5y ago
Are you just talking about prepared statements? Mysqli was released in 2004. https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php https://www.php.net/manual/en/mysqli.quickstart.prepared-sta...

nh2 5y ago

Maybe I'm misunderstanding you, but it looks like it is a PHP specific problem. Python does not have it. Of course at C level parameterised queries ("prepared statements") do not accept arrays. It would not make sense for them to do. This is because the placeholders can be substituted by values of different types. In typed programming languages, arrays have elements of the same type. For example in SQLite, you are supposed to call `sqlite3_bind_int()`, `sqlite3_bind_text()` [1], once for each query parameter. In languages like PHP and Python, where arrays can carry values of different types, their wrappers around the SQLite C functions can do this function calling for each value in the array. In Python, that is easy, the default, and explained in the very beginning of the official standard library's sqlite documentation [2]: It states at the very beginning that query construction by string construction is unsafe and must be avoided. It immediately provides an example of how to safely call a parameterised query with an array of values, using `execute()` and `executemany()`. PHP's standard library simply does not seem to have such an `execute()` function that accepts an array [3], nor do the official docs seem to contain any prose that could explain how to use the library safely [4]. The only way you can find out is by reading user-contributed comments on some specific functions in the function reference. So Python's standard library provides safe functions, and immediately instructs the user how to use them. PHP's does not. Unclear to me how one can conclude that this isn't a PHP specific problem. [1]: https://www.sqlite.org/c3ref/bind_blob.html https://www.sqlite.org/c3ref/bind_blob.html [2]: https://docs.python.org/3/library/sqlite3.html https://docs.python.org/3/library/sqlite3.html [3]: https://www.php.net/manual/en/sqlite3.prepare.php https://www.php.net/manual/en/sqlite3.prepare.php [4]: https://www.php.net/manual/en/class.sqlite3.php https://www.php.net/manual/en/class.sqlite3.php

dreyfan 5y ago
You're misunderstanding You cannot do something like the following: "WHERE status in (...?)" and then ->execute($status_array) But you can pass an array of parameters just fine (to individually bound input placeholders). It depends on which API you're using but it's of the format: ->bind_param($types, ...$params); or ->execute($params);
ransom1538 5y ago
"Maybe I'm misunderstanding you, but it looks like it is a PHP specific problem. Python does not have it." You can concatenate a sql query just fine in Python anyway you want. Adding a sql injection is just as easy in Python, PHP, or lisp. Thus this is a choice. Nothing to do with a language. Language bashing is gross and spreads lies. And yes, you can bind an array of params in PHP.

x0x0 5y ago

postgres does. PQexecParams, which executes a sql statement with parameters, accepts oids that are array types.

pjungwir 5y ago

I see what you're saying, but maybe "array" is the wrong term. Indeed you can't say `WHERE foo IN (?)` and pass more than one parameter (or an array parameter) for the single `?`. That's a limitation of the databases, not the programming languages. But Postgres does have arrays, and you can pass them as parameters. In fact that's how you solve this problem. An equivalent to `foo IN (...)` is `foo = ANY (array[...])`. So parameterized that would be `foo = ANY (?)`. Instead of a bunch of parameters, you have one parameter of array type. The parens here signify a subquery, not a list of single-attribute tuples. In fact using ANY is more expressive, since you can say `ANY (array[])` (but you might need to add a cast), but you can't say IN ()` (which is a syntax error). Anyway your point is true that lots of languages' client libraries and ORMs implement sql "parameters" by string substitution. That's still better than having the programmer do it himself, but not as good as it could be.

Akronymus 5y ago

> Common database drivers like PGSQL, MySQL, SQLite etc. don't accept arrays of values for parameterized queries. One workaround to that is passing the array as a string with some separator, deconstructing it into a temp table and then using that table as a array when it is part of a stored procedure.

matharmin 5y ago
Even better, you can pass it in a JSON array, and use the built-in JSON functionality to iterate through it. I started doing this in SQLite, and it made some queries a lot cleaner.