Asked  7 Months ago    Answers:  5   Viewed   34 times

I understand the security benefits of prepared statements in MySQL. No need to cover that topic here. I'm wondering about the performance aspect of them.

Now, I know when a query using a prepared statement is executed twice in a single PHP script, it's faster because the query is only parsed once, once for each query. The client makes one trip to prepare, then sends data twice using the binary protocol. The binary protocol is faster, and you're not taking the hit of having to parse a second time.

However, what about the case where I only want to perform a query once in a single PHP script? It would seem using a prepared statement is worse, because you're making two trips to the server, once to prepare, and once to send the data. The benefit of only having to parse once is lost, and you're penalized for that second trip. If the data isn't sufficiently smaller in binary format, you lose by using a prepared statement, right?

However, I've read some conflicting reports about what PHP's mysqli or PDO libraries do? Do either of them cache the prepared statement across script execution? Is the server going to have to parse the prepared statement again on a subsequent pageload or not? If the answer is no, that the statement doesn't have to be parsed on the second pageload, then it would seem that prepared statements ARE better, even if you're only executing the query once per pageload.

Please take into consideration if anything has changed between versions of MySQL regarding this. You can safely assume I'm using PHP 5.2

EDIT: Just to make it clear, I want an answer for MySQL and PHP specifically, specifying the MySQL version and if this was ever different, and to ONLY consider performance, not ease of use or security.

UPDATE: I accepted the answer I did because of the follow up comment had a few good ideas. I'm still a bit disappointed that no one seems to be able to answer the crux of the actual question I asked with any certainty. I guess sometimes the answer really is "it depends."

 Answers

86

The History

This was my first Stackoverflow answer. A lot has changed since, specially the deprecation and removal of the mysql API. Even if you are still on php 5.6, the mysql_* api should not be used. Now PDO or mysqli are the only options to choose. PDO is better to lots of reasons.

Are prepared statements cached across page loads?

I've read some conflicting reports about what PHP's mysqli or PDO libraries do? Do either of them cache the prepared statement across script execution?

The same prepared statement will not be used in between page loads. It has to be prepared every time. If squeezing every large millisecond matters, a stored procedure might be a good idea (assuming you have a complicated query).

For large inserts (thousands of rows) A bigger boost can probably be gained by dumping your data into a text file and loading it with LOAD DATA IN FILE . It's a lot faster than a series of inserts.

The original answer

The truth of the matter is that sometimes mysqli is faster and at other times mysql api is faster. But the difference is really really small. If you look at any of the performance tests on the web the difference is really just 10 - 20 milliseconds. The best way to boost performance is to optimize table design.

Many of the tests that 'prove' the older api to be faster conveniently forget that for maximum security mysql_real_escape_string() should be called for each variable used in the query.

Queries are cached by the server, if and only if the data on all the tables that are used in the query have remained unchanged.

Await another update with actual numbers

Wednesday, March 31, 2021
 
commonpike
answered 7 Months ago
76

In short,

  • Binding is a must, being a cornerstone of protection, no matter if it is supported by a native driver or not. It's the idea of substitution that matters.
  • The difference is negligible in either safety and performance.
  • Performance is the last thing to consider. There is NO API that is considerable slower than other. It is not a class or a function that may cause whatever performance problem but a data manipulation or a bad algorithm. Optimize your queries, not mere functions to call them.
  • If you are going to use a raw bare API, then PDO is the only choice. While wrapped in a higher level class, mysqli seems more preferable for mysql.
  • Both mysqli and PDO lack bindings for the identifiers and keywords. In this case a whitelist-based protection must be implemented. Here is my article with the ready made example, Adding a field name to the SQL query dynamically
Wednesday, March 31, 2021
 
Tak
answered 7 Months ago
Tak
59

See PDO::prepare

You cannot use a named parameter marker of the same name twice in a prepared statement
Wednesday, March 31, 2021
 
DilbertDave
answered 7 Months ago
62

IMO, that doesn't look clean in PHP. It's like you're trying to jam the C# way into PHP because you think it's a nice feature of C#.

I see it as: In PHP, you're making a single function for doing two completely different things, which doesn't make sense. Meanwhile, in C#, it is single property, which is designed to be a single point of access for an object to be set/retrieved.

Saturday, May 29, 2021
 
Jeff
answered 5 Months ago
98

The difference considered to be negligible.

Nevertheless, one have to distinguish native prepared statements from the general idea of a prepared statement.

The former is just a form of running queries supported by most of DBMS, explained here. Its usage can be questioned.
The latter is a general idea of substituting actual data with a placeholder, implying further processing of the substituted data. It is widely used in programming, a well-known printf() function is an example. And this latter approach have to be ALWAYS used to run a query against a database, no matter if it is backed by native prepared statements or not. Because:

  • prepared statement makes proper formatting (or handling) inevitable.
  • prepared statement does proper formatting (or handling) in the only proper place - right before query execution, not somewhere else, so, our safety won't rely on such unreliable sources like
    • some PHP 'magic' feature which rather spoils the data than make it safe.
    • good will of one (or several) programmers, who can decide to format (or not to format) our variable somewhere in the program flow. That's the point of great importance.
  • prepared statement affects the very value that is going into query, but not the source variable, which remains intact and can be used in the further code (to be sent via email or shown on-screen).
  • prepared statement can make application code dramatically shorter, doing all the formatting behind the scenes (*only if driver permits).

So, even if you consider not using native prepared statements (which is quite okay), you have to always create your queries using placeholders instead of the actual data. For this purpose you can use PDO, which works exactly as described above - by default it just emulate prepares, means regular SQL query being created out prepared query and data, and then run against database.

However, PDO lacks support for many important data types, such as identifier or an array - thus it makes you unable to always use placeholders and thus makes an injection quite possible. Luckily, safeMysql has placeholders for the every data type and allows you to run queries safely.

Tuesday, August 17, 2021
 
PeanutsMcgee
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :