This is still to this day the recommended way to construct a "WHERE foo IN (a,b,c,...)" query in PHP. It's insane that there's no way to pass an array of values into a parameterised query for that use case.
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.
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
[2]: https://docs.python.org/3/library/sqlite3.html
"WHERE status in (...?)" and then ->execute($status_array)
->bind_param($types, ...$params); or ->execute($params);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.
I don't know how carefully you read what I said, if you misunderstood that this is a native library limitation (C level) of the actual database clients, and that most of the popular PHP libraries can bind arrays.
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.
> 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.
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.
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.
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.
https://www.php.net/manual/en/mysqli.quickstart.prepared-sta...
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.
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.
This is somewhat the point. If using the language's standard libraries is "absolutely doing it wrong", that's an indictment of the language.
Exactly, all languages have footguns but some have a lot more than others. You don't hear for example Java developers bitching about JDBC to anywhere close to the extent PHP developers bitch about the various common approaches to database connections.
You are being deliberately obtuse. Other comments in this thread offer correct examples of using PDO to avoid SQL injection. I didn’t mean it was impossible to write safe database code using the standard library—obviously, PHP is a Turing-complete language, it can be done!—I just meant it’s awkward, and verbose, and developers are unlikely to do it consistently throughout an application. Hence this type of concern is best abstracted into a library.
To your point about “indicting a language,” most languages have footguns like this. The worst you can say about PHP is that the documentation should do more to discourage new users from working with PDO directly. (And I mean the official documentation—the language maintainers can’t be held responsible for the kind of unofficial tutorials the article complains about.) But regardless of what the official docs say, most PHP development today is done using frameworks like Laravel, Symfony, and Zend framework that do not suffer from SQL injection issues.
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);
place_holders = ','.join(['%s'] * len(params))
cursor.execute("DELETE FROM foo.bar WHERE baz IN (%s)" % place_holders,
tuple(params))
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.
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.
$user_ids = [1, 6, 46, 3, 17];
$count = count($user_ids);
$in = '(' . implode(', ', array_fill(0, $count, '?')) . ')';
$sql = "SELECT user_id, email FROM user WHERE user_id IN {$in}";
$stmt = $pdo->prepare($sql);
$stmt->execute($user_ids);
var_dump($stmt->fetchAll());Unfortunately that gets people into the habit of using string concatenation, which is not a great habit to have.
I speculate that the reason this is such an issue is because the interface at the ODBC level is basically security wise broken. It works 'OK' for getting/putting the data but it has 2 modes of execution. One of those paths is not great for security, the other has a usage issue. 'Binding' can be a real pain as it takes at least 1 call per variable parameter. Then managing the buffers correctly. So just building up the strings is an easy way to skip a lot of steps. So many take it. But that path leads to security vulins.
There's no way of doing this with a single parameter. You need to parameterise every single individual item in the IN clause to do it that way, which is a horrific solution when it's of a completely unknown length.
Still better than string concatenation in many cases, but that the language has no in-built way of doing it is one of the many reasons PHP code is so often vulnerable to injection attacks. There's so much friction to writing secure code.
It's fairly trivial to do this, but now you're potentially adding thousands of parameters per query in circumstances where the contents of the IN() are variable in count. This is not ideal for a number of reasons.
Additionally, a language should be designed such that the easiest possible way to do something is at least moderately secure. If you need to attach some boilerplate code on top of the standard libraries every single time you use them for it to be safe, then there is no reason for that boilerplate to not be in the standard libraries.