emulated prepared statements vs real prepared statements

Question

What's exactly the difference between the two kinds of prepared statements ?

I think real prepared statements require server side support wich accepts paramenters after parsing and compiling the schema/template of sql code, and , I suppose ,that's what guarantees us against sql-injection.

In the case of emulated prepared statements ,with no server support, what does
it guarantee us against it ?

Answer 1

You are correct, real prepared statements must be supported by the server. A real prepared means querying the database in two steps.

The fist step consists in sending a query template, that the server can pre-compile. The database engine also prepares in advance the execution plan (mostly, what indexes will be used to serve the actual query).

The second step is giving actual values to the placeholders and run the actual query with these parameters.

This typically allows faster execution of several similar queries, because 1. the query has already been pre-compiled (the execution plan is already computed) and 2. only the parameters values are sent subsequently.

A emulated query is just a syntactic sugar, that only allows easier sending (not faster execution) of several, successive, similar queries. Full SQL statements are sent to the server everytime an emulated query is executed.

When the sever does not support real prepared statements, it is still recommended to use emulated prepared statements, because the driver still takes care of escaping values for you, making SQL injection less likely.

Answer 2

In the case of emulated prepared statements ,with no server support, what does it guarantee us against it ?

That's a good question.

Ironically, the very same mechanism as with native prepared statements, in a way.

The main idea of a prepared statement is to make data literal processed properly and make such a processing inevitable. While details of such a processing is not that important.

The main problem with SQL injection is that it doesn't exist on it's own. All the "danger" is coming from improperly formatted query only. Every time you see an injection case, you will find an improperly formatted literal as a cause.

While properly formatted query is already protected.

that's why one should take care of no injections at all, but on the proper formatting only.
So prepared statement does such a formatting, this way or another.

That's all.