4

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?

SidOfc
  • 4,552
  • 3
  • 27
  • 50
Francesco
  • 555
  • 4
  • 23

2 Answers2

4

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.

Álvaro González
  • 142,137
  • 41
  • 261
  • 360
  • So the data is separated from the query because they use two different communication channels? – Francesco Dec 04 '15 at 15:33
  • Don't take the word "channel" too literally. I just mean that —in native prepared statements— code and data are not jammed together into a single string. I haven't studied the details of the communication protocol used by any DBMS. – Álvaro González Dec 07 '15 at 20:08
1

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

Christian Kiewiet
  • 869
  • 6
  • 19
  • The preparation and execution of a statement is done by the server if the engine is mysql. If other engines handle it differently is something I don't know but I'd guess it's most of the time the server. – Charlotte Dunois Dec 03 '15 at 13:04