Report on attempt to get declared effectiveness from prepared statements

Update: the word "unsuccessful" is removed from the title of the article. Details below!

Speaking in my article about typical misconceptions related to protection against SQL injection, among others, I noted the fact that server-prepared expressions do not work in PHP according to the declared effective script - prepare () once, then execute () 1000 times.

Well, that is, in theory, they work - within one script run. But do you know a lot of scripts (written by professional programmers) that execute a bunch of identical requests? I don’t know either. Repeated requests (some kind of multiple updates) - a fraction of a percent, and for the most part requests are unique (within the same script).
Accordingly, for our unique request, prepare () is executed first, then execute (), then the script dies safely, so that when it starts to process the next HTTP request, execute prepare () again ... It’s somehow not too much like optimization. Rather, the opposite.
As correctly noted in the comments, I should have mentioned exceptions in the form of console scripts and daemons that hold the database connection for a long time. However, the bulk of PHP scripts still run on the front end, dying after doing a couple of dozen requests.

But is there really no way to somehow cache the prepared request between starts?

And then an idea struck me!

But there were still some discrepancies, but just turned up very successfully HPC conference , which was going to speak people who could advise me. And the expectations did not deceive me - so, taking this opportunity, I want to express my deep gratitude to Sergey Averin and Kostya Osipov for their lively participation and invaluable consultations.

Let us pass to the substantive part.

So, the task:
We want to access the prepared request in a new PHP script.

There are two main problems:
  1. prepared statement lives only within one connection, it is so arranged. the problem, at first glance, is solved.
  2. the prepared expression's handler is implemented as an object. There are no options - it is impossible to transfer an object to another script

Looking ahead, I’ll say that everything turned out the other way around - the second problem was solved easily, and the first turned out to be an insurmountable obstacle.
But first things first.

The first problem is solved clearly how - using persistent connection.
After all, we are referring to the same connection as the previous script. That is, in theory, they should gain access to the entire environment that was created by previous calls.

The second problem was also resolved, and very simply.
Indeed, in Mysql there are TWO variants of prepared expressions - relatively speaking, “binary” and “string”!
And if the former do not suit us, then the latter is exactly what we need!

“Binary” prepared expressions work through the API, the prepared query handler is available only in the form of a class, and the data goes to the server in a binary package that resembles the one in which the database returns the query result. Without a serious alteration of the driver, access to them cannot be obtained.

But “string” or “SQL prepared statements” are regular SQL queries, which are given in the documentation as examples:
PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
SET @a = 3;
SET @b = 4;
EXECUTE stmt1 USING @a, @b;

Normal SQL queries!
What prevents us from executing prepare in one script and execute in another? No sooner said than done:

mysql_pconnect($host,$user,$pass); //важно - это pconnect!

mysql_query("PREPARE stmt2 FROM 'SELECT ?+? AS sum'") or trigger_error(mysql_error());
mysql_query("SET @a = 6") or trigger_error(mysql_error());
mysql_query("SET @b = 8") or trigger_error(mysql_error());
$res = mysql_query("EXECUTE stmt2 USING @a, @b") or trigger_error(mysql_error());
$row = mysql_fetch_array($res);

Then we comment on the line with PREPARE, request the page again ... The result has not changed. It works!

That is, a theoretical possibility to use the principle “one prepare () - many execute ()” exists.
In practice, alas, it will not work. The reasons here are, rather, of an ideological order, but implemented in the form of technical limitations.
Actually, I first tried to execute the code above using mysqli.
But nothing worked. I got into the Internet to read, and found out that the presence of “garbage” remaining after a previous call to the same connection is a serious fail: open transactions, not released locks, and so on. For this reason, in mysqli, a function from the C API called mysql_change_user () . Accordingly, upon repeated access to the same connection from another script, there is no trace of a previously prepared request, alas.

Given facts like
  • the idea initially contradicts the ideology of constant connections - “a persistent connection serves only to save on the connection, in all other aspects, providing the client with an absolutely clean connection, in everything similar to the new one”
  • gradual crawl of mysql ext to deprecated status - i.e. extreme undesirability of its use
  • inability to use mysqli_multi_query to send all queries with data in one batch
  • as well as the fact that, according to Kostya Osipov, SQL prepared statements are for demonstration purposes only and are not recommended for use in production
admittedly, the venture failed.

However, I do not consider time wasted.
I learned something new, I began to better understand the mechanism of the connections, both in terms of technical implementation, and in terms of ideology. Chatted with interesting people. Plus, in the end - this incomparable delight of the researcher who is developing a new topic. A negative result is also a result, an opportunity to share it so that other people do not step on the same rake.

I think you should also put a small disclaimer:
In this note, prepared statements were considered solely from the point of view of a potential increase in productivity, and not as protection against injections. Moreover, the wording “protection is provided by a placeholder” (which does not have to be implemented through native prepard statements) will be more correct, and it would be quite easy to make correct and safe generation of SET queries.

Very important update: Thanks to the comment Bvadim it turned out that the mysqli behavior described above is affected by the MYSQLI_NO_CHANGE_USER_ON_PCONNECT flag. That is, if you specify it when building the extension, then the prepared requests will be saved!
It turns out that there are no practical obstacles to the application of this technique, only ideological ones.