29/11/2016 08:23:13: $update = 'UPDATE '.$table.' SET ';
You can't use a placeholder for a table name. Using a dynamic table name is pretty standard practice. The reason for that is to allow a table prefix, allowing multiple of the same app within a single database. And, typically, the table name originates from a config file, not web POSTS/GETS or other "taintable" sources.
03/12/2016 19:33:11: $pdo->prepare("UPDATE users SET avatar=? WHERE id=22 ")->execute([$u]) ;
It's pretty basic. For UPDATE, it's matching anything with UPDATE followed by SET, followed by a dollar sign+alphanumeric
And, it's specifically going after dynamic table names. Hmm.
'/UPDATE\s+.*?\$[a-zA-Z0-9].*?\sSET.*?/i', // UPDATE $table SET ...
... or by splitting the SQL string over multiple lines. Or building it up by concatenating strings. Let's stop this now, we've demonstrated that the code is very brittle :)
So people who use
"WHERE id=" . $_GET['id']
query("DELETE FROM foo WHERE id=1");$id=2;
$query="SELECT whatever from foo WHERE".
"id=$bar ".
"AND " .
"field=$value";- it's currently skipping variable names with underscores, and it's matching variable names that start with numbers, which isn't a valid variable name
- the false positives coming from ->execute($whatever) could be fixed by not searching after a > character. I don't think that would create any notable amount of false negatives.
Like, for example, changing:
'/UPDATE\s+.*?\sSET\s.*?\$[a-zA-Z0-9].*?/i'
'/UPDATE\s+.*?\sSET\s[^>]*?\$[a-zA-Z_]/i'This is precisely how PHP programmers think: that MySQL is somehow the gold standard in the databases world.
Table prefixes are just the stupid way of emulating database schemas where it's not supported, which is only MySQL (SQLite doesn't support this either, but it was never meant for web applications anyway).
Edit: And, for what it's worth, the affinity for Mysql in PHP code is most likely because both are there, by default, in shared hosting environments. I don't personally know any PHP devs that find Mysql to be a "gold standard". Most popular PHP apps and frameworks support Postrgres as well.
Yeah, two databases. One that was designed as an embedded storage engine and was never intended for web applications and the other that historically was probably the shittiest one (silently damaging unicode data, silently damaging foreign keys in some circumstances, silently ignoring CHECK constraints, and using non-ACID data store unless asked otherwise, though the last one was fixed just few years back) and should have died long time ago.
Not just php either. Ruby/Rails Active Record, for example, supports it.
And I imagine dynamic, variable bound table names are probably in every ORM in just about every language.
Another scenario I just thought of: your schema is more or less dynamic. For example, Drupal lets developers/site administrators create entities with custom fields, each field gets its own DB table, and to load an entity you need to
SELECT * FROM node
LEFT OUTER JOIN field_data_field_name_1 ON ..
LEFT OUTER JOIN field_data_field_name_2 ON ..
...