MySQL Prepared Statements

Should we always use prepared statements in MySQL and php or when to use these?

Non-prepared statements are sufficient if you have an SQL query that is entirely hard-coded, and needs no PHP variables in the SQL.

Here's an example:

$result = $mysqli->query("SELECT * FROM mytable WHERE updated_at > NOW() - INTERVAL 7 DAY");

The query is self-contained. It's just a fixed string, entirely under control of your application. There's no way any untrusted content can affect the query.

If your query needs some variable part, then use query parameters, like this:

$stmt = $mysqli->prepare("SELECT * FROM mytable WHERE updated_at > NOW() - INTERVAL ? DAY");
$stmt->bind_param("i", $number_of_days);
$stmt->execute();

The point of query parameters is to separate potentially untrusted content from the SQL parsing step. By using parameters, the value of the bound variable is not combined with the query until after the SQL has been parsed. Therefore there is no way the bound parameter can affect the logic of the query — the parameter will be limited to act as a single scalar value in the query.

Converting regular mysql into prepared statements

You don't need to change a query to a prepared statement if it has no PHP variables in it. If it has just constant expressions, it's safe from SQL injection.

$sql = "SELECT * from users where userid=10"; // Safe!
$stmt = $pdo->query($sql);
$data = $stmt->fetchAll();

You don't need to change a query that contains PHP variables, as long as the value of that variable is a constant specified in your code. If it doesn't take its value from any external source, it's safe.

$uid = 10;
$sql = "SELECT * from users where userid=$uid"; // Safe!
$stmt = $pdo->query($sql);
$data = $stmt->fetchAll();

You don't need to change a query that contains PHP variables, as long as you can filter the value to guarantee that it won't risk an SQL injection. A quick and easy way to do this is to cast it to an integer (if it's supposed to be an integer).

$uid = (int) $_GET['uid'];
$sql = "SELECT * from users where userid=$uid"; // Safe!
$stmt = $pdo->query($sql);
$data = $stmt->fetchAll();

That leaves cases where you are using "untrusted" values, which may have originated from user input, or reading a file, or even reading from the database. In those cases, parameters are the most reliable way to protect yourself. It's pretty easy:

$sql = "SELECT * from users where userid=?"; // Safe!

// two lines instead of the one line query()
$stmt = $pdo->prepare($sql);
$stmt->execute([$_GET['uid']]);

$data = $stmt->fetchAll();

In a subset of cases, you need one additional line of code than you would normally use.

So quit your whining! ;-)

Re your comment about doing prepared statements in mysqli.

The way they bind variables is harder to use than PDO. I don't like the examples given in http://php.net/manual/en/mysqli.prepare.php

Here's an easier way with mysqli:

$sql = "SELECT * from users where userid=?"; // Safe!

$stmt = $mysqli->prepare($sql);
$stmt->bind_param('i', $_GET['uid']);
$stmt->execute();
$result = $stmt->get_result();

$data = $result->fetch_all();

I don't like the stuff they do in their examples with bind_result(), that's confusing and unnecessary. Just use get_result(). So with mysqli, you need two more lines of code than you would with PDO.

I've written query wrappers for mysqli that emulate the convenience of PDO's execute() function. It's a PITA to get an array mapped to the variable-arguments style of bind_param().

See the solution in my answers to https://stackoverflow.com/a/15933696/20860 or https://stackoverflow.com/a/7383439/20860

SQL prepared statements

range is a reserved word in MySQL. You could escape it by surrounding it with forward quotes:

`range`

Is there a way to use a prepared statement to select a variable column?

It's not possible. The difference is that you are dynamically referencing an object of the database, versus just passing in a string.

Prepared statements work by specifying the SQL statement in full with placeholders for strings/values that you are passing in. Your RDBMS can then parse the query and determine it's full execution path before you pass in the parameters. Once that step is complete, it takes the parameters in and gets the data. This is why prepared statements are so secure. The execution path is predetermined so it's impossible to pass in more SQL and change it.

So if you don't know the column or table, then it can't parse and build the execution path. Instead, you'll have to build the SQL dynamically with concatenation and execute. If you are getting the column or table name from a user input then you'll have to sanitize it as best as you can and pray that your sanitation efforts are better than your sneaky users abilities to inject sql.

MySQL cached prepared statements for performance

If you issue a prepared statement, MySQL prepares some things like parsing and executions plans, so when you later bind the actual parameters, it doesn't need to do this (again). The preparation usually requires only some ms. If that's relevant will depend on the total execution time of your query, and you can obviously only save time if you run it more than once (but you don't lose time either if you just run it once). That being said, using prepared statements even if you just run it once is a standard security measure.

The prepared statement is stored on the server and requires (a little bit of) memory. It is freed when you close it and is not cached afterwards. It's also specific to a connection, so different connections will allocate their own memory for the same query.

You can keep several prepared statements active. There is actually an upper limit for the total count of open prepared statements (given by max_prepared_stmt_count), but it will depend on how many users are using your app if that (and the memory allocated by them, which is still usually neglectable) is relevant for your consideration.

There are some practical implication when holding long-time prepared statements, the most important regarding that prepared statements are lost when you lose the connection (either by accident or if you close it intentionally). This may or may not complicate your code (to e.g. reinitialize your statements on connection loss). It also forces you to keep your connection open, and although MySQL usually doesn't need many resources to keep sleeping connection open, you may or may not want that. Also, this will basically prevent you from using connection pools (unless they support that), as you cannot give a connection back to the pool without losing the prepared statement (although this is usually less relevant for a c-application).

This is the behaviour of the standard c mysql api (as your syntax suggested). If you use alternative tools, this behaviour can of course be different. As an example, the java driver does (optionally) cache statements on .close. But it basically does that by simply not actually closing it (via the api), and handling things like reinitializing on connection loss and such internally. This implies there can be some use for such an (application side) cache, and while I am not aware of a c alternative for that, it is certainly possible (and should not be to complicated) to implement that (and it's likely someone already did).

And a last note, as you may be looking for that: Until MySQL 8, there was a query cache to cache the result of a specific sql query. If you run a query twice with the exact same code (with the exact same parameters) and if there were no updates to the tables involves (and if the cache was enabled and the resultset was not too big), MySQL would fetch the result from that cache without the need to reexecute the query. It's only closely related to prepared statements, and transparent to the application (you would not need to change anything), but might be relevant in the context of caching.

Client-side emulation prepared statement in MySQL JDBC driver

Simple answer: It doesn't. The MySQL driver - by default - will simply execute a query string created from the parameterized query and the (escaped) parameter values. It will create this query locally by replacing the parameter placeholders with the escaped values and then send it to the server

Note that JDBC does not require that a PreparedStatement is actually prepared server side, nor that it 'improves' performance. JDBC only requires that they work; as in: you can specify queries with ? as parameterplaceholders, and the driver will correctly execute the statement with the values set through the setXXX methods.

Also note that MySQL does have server side prepared statements, but you need to explicitly enable this using the connection property useServerPrepStmts with value true.

Is PDO still emulating prepared statements for MySQL?

The answer you are referring to is more like a scary tale than a real help. If you read the fine print at the bottom, it says that with actual software versions you are all right (actual means released past 2010).

So you can tell that security-wise there is no difference whether prepared statements are emulated or not. Hence, the answer to your question is not that important.

Besides, you incorrectly understood a certain statement from it.

However, be aware that PDO will silently fallback to emulating statements that MySQL can't prepare natively

It doesn't mean then mysql doesn't support native prepared statements at all. It means that only for some certain kinds of queries mysql does not support prepared statements. For such queries you don't have too much a choice, so it doesn't really matter again.

To make it clear

PDO does still emulate prepared statements for MySQL by default, when no option is set.
For the most used query types such as SELECT, INSERT, UPDATE and such, PDO does not emulate prepared statements for MySQL if explicitly told to use native statements. by the way, the list of supported statements is quite inclusive
as this behavior is decided on the server side, changing PHP API from PDO to mysqli won't help.
for some rarely used query types it may silently fallback to emulating statements but it is neither a security concern nor you have a choice anyway.

To sum it up:

For convenience sake, disable the emulation as a connection option. Means you have to change your current single-line connection to a full-blown PDO connection script which I suggest as a canonical example and then just move on.