Scott Arciszewski
[PHP-DEV] Idea: PDO - Single-round-trip prepared statements
November 03, 2017 04:40PM
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
Matteo Beccati
Re: [PHP-DEV] Idea: PDO - Single-round-trip prepared statements
November 03, 2017 09:00PM
Hi Scott,

On 03/11/2017 16:33, Scott Arciszewski wrote:
> 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.

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/

--
PHP Internals - PHP Runtime Development Mailing List
To unsubscribe, visit: http://www.php.net/unsub.php
Scott Arciszewski
Re: [PHP-DEV] Idea: PDO - Single-round-trip prepared statements
November 03, 2017 09:30PM
On Fri, Nov 3, 2017 at 3:49 PM, Matteo Beccati <[email protected]> wrote:

> Hi Scott,
>
> On 03/11/2017 16:33, Scott Arciszewski wrote:
> > 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.
>
> 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.

https://dev.mysql.com/doc/internals/en/x-protocol-use-cases-use-cases.html#x-protocol-use-cases-prepared-statements-with-single-round-trip

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:

1. Throw a PDOException, or
2. 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/

Matteo Beccati
Re: [PHP-DEV] Idea: PDO - Single-round-trip prepared statements
November 04, 2017 08:30AM
Hi,

On 03/11/2017 21:25, Scott Arciszewski wrote:
> ​MySQL calls it an X Protocol, apparently.
>
> https://dev.mysql.com/doc/internals/en/x-protocol-use-cases-use-cases.html#x-protocol-use-cases-prepared-statements-with-single-round-trip

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:
>
> 1. Throw a PDOException, or
> 2. 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/

--
PHP Internals - PHP Runtime Development Mailing List
To unsubscribe, visit: http://www.php.net/unsub.php
> On 4 Nov 2017, at 14:28, Matteo Beccati <[email protected]> wrote:
>
> Hi,
>
>> On 03/11/2017 21:25, Scott Arciszewski wrote:
>> ​MySQL calls it an X Protocol, apparently.
>>
>> https://dev.mysql.com/doc/internals/en/x-protocol-use-cases-use-cases.html#x-protocol-use-cases-prepared-statements-with-single-round-trip
>
> 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:
>>
>> 1. Throw a PDOException, or
>> 2. 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/
>
> --
> PHP Internals - PHP Runtime Development Mailing List
> To unsubscribe, visit: http://www.php.net/unsub.php

(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.

--
PHP Internals - PHP Runtime Development Mailing List
To unsubscribe, visit: http://www.php.net/unsub.php
Rowan Collins
Re: [PHP-DEV] Idea: PDO - Single-round-trip prepared statements
November 04, 2017 06:10PM
On 4 November 2017 16:24:58 GMT+00:00, Stephen Reay <[email protected]> wrote:
>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]

--
PHP Internals - PHP Runtime Development Mailing List
To unsubscribe, visit: http://www.php.net/unsub.php
Johannes Schlüter
Re: [PHP-DEV] Idea: PDO - Single-round-trip prepared statements
November 05, 2017 01:10AM
On Sa, 2017-11-04 at 08:28 +0100, Matteo Beccati wrote:
> Hi,
>
> On 03/11/2017 21:25, Scott Arciszewski wrote:
> >
> > 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


--
PHP Internals - PHP Runtime Development Mailing List
To unsubscribe, visit: http://www.php.net/unsub.php
Sorry, only registered users may post in this forum.

Click here to login