How does the PDO prepared statements works “inside”?

Prepared statements are useful because preparing "templates" to add the data prevents SQL injections, my question is, how is this possible?

How do prepared statements really work?

After I write a query, bound the params and executed the query, what happens?

I don't think the params are "inserted" in the query, in that case the effect of prepared statements fails... Maybe it uses special delimiters to detect the start and end of the data in the query. If this is right, what they are?

Answers


Exact behaviour depends. For instance, the MySQL driver in PDO can do two entirely different things depending of the value of the PDO::ATTR_EMULATE_PREPARES attribute:

Enables or disables emulation of prepared statements. Some drivers do not support native prepared statements or have limited support for them. Use this setting to force PDO to either always emulate prepared statements (if TRUE), or to try to use native prepared statements (if FALSE). It will always fall back to emulating the prepared statement if the driver cannot successfully prepare the current query.

Emulated mode is just like you describe: PHP has a SQL parser that replaces place-holders with actual values. The only actual benefit is that code is cleaner and easier to maintain.

Native mode basically sends code and data to the server in two separate channels. Data can be sent as-is (even in binary mode). This requires both client and server support. Benefits include security, bandwidth saving and the possibility to parse SQL code once and run it several times with different data sets. Actual implementation depends on DBMS.


It entirely depends on whether the PDO adapter in use can emulate prepares (sqlsrv is one such example) or whether the RDBMS which is adapted to does actually support prepared statements, in which case the preparation and execution of a statement is actually handled by the client (or in some cases even the server).


Edit:

If you're interested in how your particular PDO adapter (probably pdo_mysql) handles this, have a look at the source code: https://github.com/php/php-src/blob/master/ext/pdo_mysql/mysql_statement.c


Need Your Help

how do you prevent verification code attack to server

mobile sms sms-verification

building a server for an app and one of the steps of the registration in the app is to enter a phone number verification code. The verification code will be sent to the phone through sms and the