This came up in a discussion on Reddit:
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 EasyDB1, but this is a higher-level
abstraction around PDO::prepare() and PDOStatement::execute().
Questions/Challenges:
- Which DB drivers (and which versions) support 1RT prepared statements in
addition to 2RT prepared statements? - 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.
Scott Arciszewski
Chief Development Officer
Paragon Initiative Enterprises <https://paragonie.com
Hi Scott,
- Which DB drivers (and which versions) support 1RT prepared statements in
addition to 2RT prepared statements?- 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.
I've added a similar feature to pdo_pgsql a while ago
(PDO::PGSQL_ATTR_DISABLE_PREPARES), which is not enabled by default.
It is basically somwthing in between real prepared statements (which is
the default on pdo_pgsql) and emulated prepares, and it uses libpq's own
PQexecParams function.
At that time I had checked if libmysqlclient offered a similar function,
but it didn't seem like it, so I went for the pgsql-only constant.
Cheers
Matteo Beccati
Development & Consulting - http://www.beccati.com/
Hi Scott,
- Which DB drivers (and which versions) support 1RT prepared statements
in
addition to 2RT prepared statements?- 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.I've added a similar feature to pdo_pgsql a while ago
(PDO::PGSQL_ATTR_DISABLE_PREPARES), which is not enabled by default.It is basically somwthing in between real prepared statements (which is
the default on pdo_pgsql) and emulated prepares, and it uses libpq's own
PQexecParams function.At that time I had checked if libmysqlclient offered a similar function,
but it didn't seem like it, so I went for the pgsql-only constant.Cheers
Matteo Beccati
Development & Consulting - http://www.beccati.com/
MySQL calls it an X Protocol, apparently.
We don't want to disable prepared statements (that constant's name is
somewhat scary).
We don't want to emulate prepared statements (although we'll probably have
to respect the current configuration).
We just need a separate method (my proposed safeQuery() being distinct from
prepare()) that uses whatever that driver's single-round-trip
prepare-and-execute equivalent API is. If none is available for the given
driver, we need to decide whether to:
- Throw a PDOException, or
- Silently use two round trips in the background so it "just works" even
if it's a slight performance hit
(In case it wasn't clear, I'm very much NOT a fan of emulated prepares,
and in EasyDB we go out of our way to disable this feature.)
Scott Arciszewski
Chief Development Officer
Paragon Initiative Enterprises https://paragonie.com/
Hi,
MySQL calls it an X Protocol, apparently.
OK, which is something that neither libmysqlclient nor mysqlnd seem to
support.
We don't want to disable prepared statements (that constant's name is
somewhat scary).
Well, in my experience there are very few occasions that actually
benefit from prepared queries. I know it can sound scary, but I do
believe that more than 95% of the times prepared queries are just
wasting resources. The few patterns that actually benefit from them
could just enable them temporarily. Not ideal, but we are kinda used to
it with PDO, aren't we?
We don't want to emulate prepared statements (although we'll probably
have to respect the current configuration).
At the very least we should turn it off as a default on MySQL.
We just need a separate method (my proposed safeQuery() being distinct
from prepare()) that uses whatever that driver's single-round-trip
prepare-and-execute equivalent API is. If none is available for the
given driver, we need to decide whether to:
- Throw a PDOException, or
- Silently use two round trips in the background so it "just works"
even if it's a slight performance hit(In case it wasn't clear, I'm very much NOT a fan of emulated prepares,
and in EasyDB we go out of our way to disable this feature.)
I'm not opposed to a new method, but I think it would be confusing.
Moreover with safeQuery you'd miss all the possibilities to bind
parameters etc, offered by PDOStatement.
Cheers
Matteo Beccati
Development & Consulting - http://www.beccati.com/
Hi,
MySQL calls it an X Protocol, apparently.
OK, which is something that neither libmysqlclient nor mysqlnd seem to
support.We don't want to disable prepared statements (that constant's name is
somewhat scary).Well, in my experience there are very few occasions that actually
benefit from prepared queries. I know it can sound scary, but I do
believe that more than 95% of the times prepared queries are just
wasting resources. The few patterns that actually benefit from them
could just enable them temporarily. Not ideal, but we are kinda used to
it with PDO, aren't we?We don't want to emulate prepared statements (although we'll probably
have to respect the current configuration).At the very least we should turn it off as a default on MySQL.
We just need a separate method (my proposed safeQuery() being distinct
from prepare()) that uses whatever that driver's single-round-trip
prepare-and-execute equivalent API is. If none is available for the
given driver, we need to decide whether to:
- Throw a PDOException, or
- Silently use two round trips in the background so it "just works"
even if it's a slight performance hit(In case it wasn't clear, I'm very much NOT a fan of emulated prepares,
and in EasyDB we go out of our way to disable this feature.)I'm not opposed to a new method, but I think it would be confusing.
Moreover with safeQuery you'd miss all the possibilities to bind
parameters etc, offered by PDOStatement.Cheers
Matteo Beccati
Development & Consulting - http://www.beccati.com/
--
(Sending again from on-list address)
With MySQL "true" prepared queries mean the data segments are never parsed as sql on the server.
So while most apps don't need to reuse the prepared statement they benefit from the more bulletproof parameterization, IMO.
See the recent Wordpress/Anthony bullshittery for why string manipulation "prepare" is a problem.
Also - why does this need a new method? Can't it be an option to PDO (eg like emulated prepared statements option) and similar to emulated prepares (which wouldn't need to connect in the prepare() stage) it just stores the query until you call exec($data) and then makes one call to the DB server.
So while most apps don't need to reuse the prepared statement they
benefit from the more bulletproof parameterization, IMO.
I think a lot of unnecessary confusion comes about because people say "prepared statements" when it would be clearer to talk about "parameterized statements".
Also - why does this need a new method? Can't it be an option to PDO
(eg like emulated prepared statements option) and similar to emulated
prepares (which wouldn't need to connect in the prepare() stage) it
just stores the query until you call exec($data) and then makes one
call to the DB server.
This makes sense to me. In effect, there are (for drivers that support them) two independent options:
- Prepare statement on server or send only when executed?
- Parameterize statement on server or pass as full SQL statement?
Preparing on the server without parameterizing on the server doesn't make much sense, so you end up with three modes: prepare, parameterize, or neither.
"Emulated prepares" should really be called "parameterize on client" - there's nothing really to "prepare" until the data is provided to substitute in.
Finally, there are occasional cases where the entire SQL is static code, in which case it makes no difference which parameterization mode you use, but there might still be cases for toggling "prepare on server".
Regards,
--
Rowan Collins
[IMSoP]
Hi,
MySQL calls it an X Protocol, apparently.
https://dev.mysql.com/doc/internals/en/x-protocol-use-cases-use-cas
es.html#x-protocol-use-cases-prepared-statements-with-single-round-
trip
OK, which is something that neither libmysqlclient nor mysqlnd seem
to support.
The X Protocol is supported via
https://pecl.php.net/package/mysql_xdevapi which adds a complete new
set of APIs with higher level CRUD stuff. We won't put it directly in
mysqlnd or such as it has different semantics in different areas making
the protocol no simple drop-in replacement. It's however based on
Google protobuf, thus it is relatively straight forward to implement
(or one could build upon based on the DevAPI) maybe doing a PDO
prototype might be interesting ...
Aside from that: "true" prepared statements are improved quite a lot in
MySQL 8 (currently in RC), but with the "old" protocol still need the
extra roundtrip.
In general: When doing something in the area one should also look into
more "advanced" abstractions. One thing I often see requested are
"variadic binds" for stuff like "WHERE field IN (?...)" which isn't
supported natively (50% of the way can be done by faking this by
binding to a JSON array) Maybe there are other ideas what can be done
liberating from concepts from the 1970ies. With the X protocol we
(MySQL) have liberties to extend this, which we didn't had before ...
:)
johannes