Prepared statements are useful because preparing "templates" to add the data prevents SQL injections, my question is, how is this possible?
准备好的语句很有用,因为准备“模板”来添加数据可以防止SQL注入,我的问题是,这怎么可能?
How do prepared statements really work?
准备好的报表是如何工作的?
After I write a query, bound the params and executed the query, what happens?
在我编写查询、绑定params并执行查询之后,会发生什么?
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?
我不认为params是在查询中“插入”的,在这种情况下,准备语句的效果会失败……也许它使用特殊的分隔符来检测查询中的数据的开始和结束。如果这是对的,他们是什么?
2 个解决方案
#1
2
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:
精确的行为决定。例如,PDO中的MySQL驱动程序可以根据PDO:: attr_emulate_prepare属性的值做两件完全不同的事情:
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.
启用或禁用已准备语句的模拟。有些驱动不支持原生准备语句,或者对它们的支持有限。使用此设置可以强制PDO总是模拟已准备语句(如果为真),或者尝试使用本机已准备语句(如果为假)。如果驱动程序不能成功地准备当前查询,那么它将总是回到模拟准备好的语句。
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.
仿真模式就像您描述的那样:PHP有一个SQL解析器,它用实际值替换位置占位符。唯一的实际好处是代码更简洁、更易于维护。
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.
本机模式主要通过两个独立的通道向服务器发送代码和数据。数据可以按原样发送(即使是二进制模式)。这需要客户端和服务器的支持。好处包括安全性、节省带宽以及一次解析SQL代码并多次使用不同数据集运行SQL代码的可能性。实际的实现取决于DBMS。
#2
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).
这完全取决于使用PDO适配器可以模拟准备(sqlsrv就是这样的一个例子)或是否适应实际支持的RDBMS预处理语句,在这种情况下,准备和执行的语句实际上是由客户端(或者在某些情况下甚至服务器)。
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
如果您对特定的PDO适配器(可能是pdo_mysql)如何处理这个问题感兴趣,请查看源代码:https://github.com/php/php-src/blob/master/ext/pdo_mysql/mysql_statement.c
#1
2
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:
精确的行为决定。例如,PDO中的MySQL驱动程序可以根据PDO:: attr_emulate_prepare属性的值做两件完全不同的事情:
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.
启用或禁用已准备语句的模拟。有些驱动不支持原生准备语句,或者对它们的支持有限。使用此设置可以强制PDO总是模拟已准备语句(如果为真),或者尝试使用本机已准备语句(如果为假)。如果驱动程序不能成功地准备当前查询,那么它将总是回到模拟准备好的语句。
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.
仿真模式就像您描述的那样:PHP有一个SQL解析器,它用实际值替换位置占位符。唯一的实际好处是代码更简洁、更易于维护。
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.
本机模式主要通过两个独立的通道向服务器发送代码和数据。数据可以按原样发送(即使是二进制模式)。这需要客户端和服务器的支持。好处包括安全性、节省带宽以及一次解析SQL代码并多次使用不同数据集运行SQL代码的可能性。实际的实现取决于DBMS。
#2
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).
这完全取决于使用PDO适配器可以模拟准备(sqlsrv就是这样的一个例子)或是否适应实际支持的RDBMS预处理语句,在这种情况下,准备和执行的语句实际上是由客户端(或者在某些情况下甚至服务器)。
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
如果您对特定的PDO适配器(可能是pdo_mysql)如何处理这个问题感兴趣,请查看源代码:https://github.com/php/php-src/blob/master/ext/pdo_mysql/mysql_statement.c