Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:101043
Mailing-List: contact internals-help@lists.php.net; run by ezmlm
Received-SPF: pass (pb1.pair.com: domain paragonie.com designates 74.125.82.172 as permitted sender)
MIME-Version: 1.0
Date: Fri, 3 Nov 2017 11:33:58 -0400
Message-ID: <CAKws9z1CeGHDmJ7hvX=mTXabMY+2fWgCjJw1N03bLZ=6PAsVng@mail.gmail.com>
To: PHP Internals <internals@lists.php.net>
Content-Type: multipart/alternative; boundary="001a113b1c1662186f055d15d507"
Subject: Idea: PDO - Single-round-trip prepared statements
From: scott@paragonie.com (Scott Arciszewski)

--001a113b1c1662186f055d15d507
Content-Type: text/plain; charset="UTF-8"

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>

--001a113b1c1662186f055d15d507--