mysql - 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.