PDO Query Nuance: UPDATE WHERE IN Only Updates One Row

I prefer to always use parameterized queries in PDO when possible. Just now, I encountered a situation in which the complications of using them would have not even been worth it. Take a look at this query:

Given that the post_id‘s exist, this will set a deleted flag on all of them right? Ok, let’s try to define that query using PDO and prepared statements…

As it turns out, that will ONLY update the first row. Turns out, the IN clause is a little more complicated than I thought with prepared statements. There is a proposed solution here, but in my case, the data is not user input and can thus sanitize it myself.

You Are Vulnerable For SQL Injection

Do you use the mysql_* series of PHP functions? Then you are most likely vulnerable to SQL injection.

This is not because there is a flaw in those functions, rather they don’t particularly encourage or provide for proper handling of user input and database queries. In fact, according to the documentation: This extension is not recommended for writing new code. Instead, either the mysqli or PDO_MySQL extension should be used.
Have you ever done this?

If you have, then you have written code ripe for SQL Injection.  Suppose I place in the password field of the form: [ abc’ OR ‘1’=’1 ] (without the brackets). Then the password = ” part of the query turns into password = ‘abc’ OR ‘1’=’1′ which makes your application think it found a matching user (in fact it gets ALL users) due to the boolean logic of OR and will most likely allow them to be logged in. Either the username AND password match, OR if 1 is equal to 1 (which is always true).

There is a function to help alleviate this possibility: using mysql_real_escape_string(). But seriously, don’t even bother. You need to start using an extension that supports prepared statements / parameterized queries.

PDO to the Rescue!

PDO ( PHP Data Objects) provides abstracts your database interactions and currently supports (at the time of this post) twelve database drivers. It has the ability, and encourages use of, prepared statements:

The parameters to prepared statements don’t need to be quoted; the driver automatically handles this. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible).

Prepared statements are so useful that they are the only feature that PDO will emulate for drivers that don’t support them. This ensures that an application will be able to use the same data access paradigm regardless of the capabilities of the database.

Here is another example of using PDO:

Prepared statements are also possibly using the mysqli extension, but that locks you in to using MySQL database. The beauty of PDO is if later down the road you choose to migrate to PostgreSQL or SQL Server, for example, it’s a matter of changing your connection settings. The rest of your code stays the same.