This came up in a discussion on Reddit:
https://www.reddit.com/r/PHP/comments/79xgcg/disclosure_wordpress_wpdb_sql_injection_technical/dp7wln0/?context=5
For database drivers that support sending the query and parameters in the
same TCP packet (n.b. not in the same query string, though, or we lose the
code-data separation benefits that prepared statements offers and makes SQL
injection provably mitigated), we can make prepared statements as efficient
as unsafe queries.
My proposal, for one-off prepared queries:
$results = $pdo->safeQuery(
"SELECT * FROM foo WHERE id = :userid",
array('userid' => $_GET['user_id'])
);
In this case, $results will be a PDOStatement object just like if you
performed the following:
$results = $pdo->prepare("SELECT * FROM foo WHERE id = :userid");
$results->execute(['userid' => $_GET['user_id']);
However, it won't need a separate execute() call. You can immediately fetch
the results.
We use a similar interface in EasyDB[1], but this is a higher-level
abstraction around PDO::prepare() and PDOStatement::execute().
Questions/Challenges:
1. Which DB drivers (and which versions) support 1RT prepared statements in
addition to 2RT prepared statements?
2. Is there a better name for this usage than safeQuery()?
If this turns out to be a good idea, I'll write up an RFC targeting PHP 7.3.
-----
[1]: https://github.com/paragonie/easydb
Scott Arciszewski
Chief Development Officer
Paragon Initiative Enterprises <https://paragonie.com>
