Why does mysqli create two lines of log per statement and ORM just one, when using parameterized queries?

Question

Looking at thegeneral MySQL log, when I use mysqli with parameterized queries with PHP I see statements like this:

Prepare SELECT * FROM my_table WHERE id = ?
Execute SELECT * FROM my_table WHERE id = 9
Prepare INSERT INTO my_table SET name = ?
Execute INSERT INTO my_table SET name = 'Alex'

This makes me feel warm and fuzzy, because I distinctly see that first, my query was sent, and them, my parameters, in two separate statements.

But when using an ORM (Doctrine in this case), I see the following:

Query   SELECT t0.id AS id_1, t0.name AS name_2 FROM my_table t0 WHERE t0.id = '9'
Query   START TRANSACTION
Query   INSERT INTO my_table (name) VALUES ('Alex')
Query   COMMIT

This has me feel alerted, as I do not see the same sequence of statement being send followed by parameters. It's statement + parameters in one go.

Questions about this that I have are:

• Is Doctrine actually using parameterized statements, and why doesn't it do what MySQL does - log two packets, like mysqli does natively?
• Is Doctrine safe from injection attacks in whatever it is doing now?
• How is Doctrine safe from attacks, when it lumps statement and parameters into the same single query, per query? Does it really do something else here?

Answer 1

While I don't know the Doctrine library specifically, I can speak to MySQL logs and what's going on as far as the DB is concerned:

The first example (prepare/execute) uses prepared statements, while the latter (query) does not. Prepared statements have a number of advantages, primarily performance- and security-related (avoiding SQL injection, as you mentioned), and I would personally avoid an ORM that did not use prepared statements, as they are generally considered a best practice for executing queries from within an application.

That's not to say that Doctrine isn't doing some sanitation internally to protect from SQL injection, and I certainly hope it is; at the same time, it would be more effective to use prepared statements, and I don't know why they wouldn't.

Answer 2

Doctrine uses PDO internally in most cases.

http://php.net/manual/en/pdo.prepare.php says in part:

PDO will emulate prepared statements/bound parameters for drivers that do not natively support them, and can also rewrite named or question mark style parameter markers to something more appropriate, if the driver supports one style but not the other.

When emulation of prepared statements is enabled, the prepare() that your app runs is basically a no-op. PDO saves your SQL string, but it does not send the SQL query to the database server at that time.

Then when you call execute(), it copies your parameter values into the appropriate places in the query, using proper string-escaping. Then it finally sends the final string to the database server. No prepare is done at the database server, it just executes the query as-is.

This is also how PDO supports both positional (?) and named (:param) parameters for all brands of RDBMS, even though most brands support one or the other style, but not both.

If you disble emulation of prepared statements, you should see both the Prepare and the Execute lines appear in the query log. I believe this can be done in Doctrine this way:

$pdo = $entityManager->getConnection()->getWrappedConnection();
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);