# Change Request: Make PDO default to not emulate prepared statements for MySQL - Thread: https://externals.io/message/52097 - Original archive: https://news-web.php.net/php.internals/52097 - Started by Anthony Ferrara on 2011-04-30T15:13:41+00:00 - Messages: 26 - Page 1 of 2: messages 1 to 25 (25 per page, add `?page=N` to the URL) - Next page: https://externals.io/message/52097.md?page=2 - Reply by email to internals@lists.php.net - More for agents: search with https://externals.io/search.md?q={query} · JSON API at https://externals.io/api · documentation at https://externals.io/llms.txt --- ## #52097 — Anthony Ferrara — 2011-04-30T15:13:41+00:00 URL: https://externals.io/message/52097#52097 I have already reported this issue on the bug tracker: http://bugs.php.net/bug.php?id=54638 But I figured it would be good to start a discussion on it here. To me, I consider this a pretty significant issue since it's not possible to do true prepared statements while using PDO. All the code to do so is there (and it does work). But it's just the single flag that defaults emulation to be on that's holding things up. Since it will fallback to using emulation mode if the library or server can't support prepared statements, I don't personally see any issue with changing the default in a point release. Thanks Anthony --- ## #52099 — Rasmus Lerdorf — 2011-04-30T15:39:07+00:00 URL: https://externals.io/message/52097#52099 · In reply to: #52097 > I have already reported this issue on the bug tracker: > http://bugs.php.net/bug.php?id=54638 > > But I figured it would be good to start a discussion on it here. To > me, I consider this a pretty significant issue since it's not possible > to do true prepared statements while using PDO. All the code to do so > is there (and it does work). But it's just the single flag that > defaults emulation to be on that's holding things up. > > Since it will fallback to using emulation mode if the library or > server can't support prepared statements, I don't personally see any > issue with changing the default in a point release. Do you realize why we did this in the first place? The common versions of MySQL in use out there are not very clever when it comes to the native prepared statement handling. First, there is no prepared statement cache, so there is no benefit to doing them natively, but worse, when you use a native prepared statement you completely miss the query result cache. As a result emulated prepared statements are either the same speed or faster than the native ones. Changing this default would result in a performance hit for most people. It should be better documented, but that is the only problem I see here. -Rasmus --- ## #52100 — Anthony Ferrara — 2011-04-30T15:42:03+00:00 URL: https://externals.io/message/52097#52100 · In reply to: #52099 Well, the benefit to doing it natively would be to prevent SQL Injection (which is possible now even with PDO's prepared statements). As it stands now, it's quite possible to inject when using prepared statements (easy <= 5.3.5, and possible >= 5.3.6, depending on config). So basically the choice of emulating is drawing the line saying we care more about performance than we do about security (if that's how I read the reply). Anthony > > > […] > > Do you realize why we did this in the first place? The common versions of > MySQL in use out there are not very clever when it comes to the native > prepared statement handling. First, there is no prepared statement cache, so > there is no benefit to doing them natively, but worse, when you use a native > prepared statement you completely miss the query result cache. As a result > emulated prepared statements are either the same speed or faster than the > native ones. Changing this default would result in a performance hit for > most people. It should be better documented, but that is the only problem I > see here. > > -Rasmus --- ## #52101 — Ferenc Kovacs — 2011-04-30T15:45:09+00:00 URL: https://externals.io/message/52097#52101 · In reply to: #52099 > > > > […] > > Do you realize why we did this in the first place? The common versions of > MySQL in use out there are not very clever when it comes to the native > prepared statement handling. First, there is no prepared statement cache, so > there is no benefit to doing them natively, but worse, when you use a native > prepared statement you completely miss the query result cache. As a result > emulated prepared statements are either the same speed or faster than the > native ones. Changing this default would result in a performance hit for > most people. It should be better documented, but that is the only problem I > see here. > > I disable query_cache on my machines, because it can cause performance and stability issues. http://www.mysqlperformanceblog.com/2011/04/10/should-we-give-a-mysqlquery-cache-a-second-chance/ Tyrael --- ## #52102 — Reindl Harald — 2011-04-30T16:02:57+00:00 URL: https://externals.io/message/52097#52102 · In reply to: #52101 Am 30.04.2011 17:45, schrieb Ferenc Kovacs: > > > […] > I disable query_cache on my machines, because it can cause performance and > stability issues. > http://www.mysqlperformanceblog.com/2011/04/10/should-we-give-a-mysqlquery-cache-a-second-chance/ i guess you must have some really strange things in your applications > which can take a lot of time if you have millions of queries cache for given table makes me sure you do something wrong and not know the SQL_NO:_CACHE hints for disable caching of queries from them you know that they can not benefit or with really small results with the wehre-statemant on the primary key where the cache is not faster as the normal query and I/O does not matter because the result size _______________________ we are using mysql-query-cache on all servers with some hundret domains since years and as long the whole generate time of a dynamic page is done between 0.008 and 0.011 seconds there is no performance issue disable the query cache would degrade the whole box 10 years back and even on a dbmail-mailserver with innodb-backend the qc improve performance dramatically [--] Reads / Writes: 68% / 32% [--] Total buffers: 2.4G global + 3.2M per thread (200 max threads) [OK] Maximum possible memory usage: 3.0G (37% of installed RAM) [OK] Slow queries: 0% (3/27M) [OK] Highest usage of available connections: 17% (35/200) [OK] Key buffer size / total MyISAM indexes: 256.0M/106.8M [OK] Key buffer hit rate: 99.9% (109M cached / 126K reads) [OK] Query cache efficiency: 89.6% (20M cached / 23M selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (924 temp sorts / 210K sorts) --- ## #52103 — Rasmus Lerdorf — 2011-04-30T16:06:33+00:00 URL: https://externals.io/message/52097#52103 · In reply to: #52101 > I disable query_cache on my machines, because it can cause performance > and stability issues. > http://www.mysqlperformanceblog.com/2011/04/10/should-we-give-a-mysqlquery-cache-a-second-chance/ Sure, there are some problems with it, but if you understand how it works and cache the queries that will benefit, it can give you a significant performance boost. -Rasmus --- ## #52104 — Ferenc Kovacs — 2011-04-30T16:34:18+00:00 URL: https://externals.io/message/52097#52104 · In reply to: #52102 On Sat, Apr 30, 2011 at 6:02 PM, Reindl Harald wrote: > > Am 30.04.2011 17:45, schrieb Ferenc Kovacs: > > […] > wrote: > > […] > of > > […] > cache, so > > […] > native > > […] > result > > […] > the > > […] > problem I > > […] > and > > […] > http://www.mysqlperformanceblog.com/2011/04/10/should-we-give-a-mysqlquery-cache-a-second-chance/ > > i guess you must have some really strange things in your applications > > not really. but the global mutex and the coarse invalidation makes it capable of stalling a server and it wasn't easy so spot that. at least with vanilla mysql 5.1 http://www.xaprb.com/blog/2010/09/15/making-query-cache-contention-more-obvious/ http://bugs.mysql.com/bug.php?id=56822 but this is offtopic here, I shouldn't have brought this up. Tyrael --- ## #52107 — Stas Malyshev — 2011-04-30T17:36:05+00:00 URL: https://externals.io/message/52097#52107 · In reply to: #52099 Hi! > Do you realize why we did this in the first place? The common versions > of MySQL in use out there are not very clever when it comes to the > native prepared statement handling. First, there is no prepared > statement cache, so there is no benefit to doing them natively, but Since 5.1.17 there is: http://dev.mysql.com/doc/refman/5.1/en/query-cache-operation.html And 5.1.17 is 4 years old already. -- Stanislav Malyshev, Software Architect SugarCRM: http://www.sugarcrm.com/ (408)454-6900 ext. 227 --- ## #52108 — Rasmus Lerdorf — 2011-04-30T17:53:30+00:00 URL: https://externals.io/message/52097#52108 · In reply to: #52107 > Hi! > > > […] > > Since 5.1.17 there is: > http://dev.mysql.com/doc/refman/5.1/en/query-cache-operation.html > And 5.1.17 is 4 years old already. People upgrade their databases even slower than they upgrade their PHP. -R --- ## #52109 — Ferenc Kovacs — 2011-04-30T18:10:49+00:00 URL: https://externals.io/message/52097#52109 · In reply to: #52108 > > > > […] > > People upgrade their databases even slower than they upgrade their PHP. > > -R > > > with 5.0 EOL-ed for some time, and even the debian stable is running 5.1, I wonder how many of our user runs 5.0. Tyrael --- ## #52110 — Matt Wilson — 2011-04-30T18:16:03+00:00 URL: https://externals.io/message/52097#52110 · In reply to: #52109 Really would depend on the user. Part of my company still runs on SQLServer 2003, for instance... Corporate infrastructure rarely sees upgrades. Your cousin with the wordpress fetish, on the other hand... > > > > […] > with 5.0 EOL-ed for some time, and even the debian stable is running 5.1, I > wonder how many of our user runs 5.0. > > Tyrael --- ## #52111 — Reindl Harald — 2011-04-30T18:23:04+00:00 URL: https://externals.io/message/52097#52111 · In reply to: #52109 Am 30.04.2011 20:10, schrieb Ferenc Kovacs: > > […] > with 5.0 EOL-ed for some time, and even the debian stable is running 5.1, I > wonder how many of our user runs 5.0. and why should anybody wait for lazy people? 5.5 did not hurt anything what is not crappy written --- ## #52112 — Rasmus Lerdorf — 2011-04-30T18:37:06+00:00 URL: https://externals.io/message/52097#52112 · In reply to: #52109 > with 5.0 EOL-ed for some time, and even the debian stable is running > 5.1, I wonder how many of our user runs 5.0. I'm not disagreeing, I just don't agree it is a bug against 5.3. There were good reasons for the default at the time 5.3 was released. For 5.4 it is probably time to switch it. -Rasmus --- ## #52113 — Ferenc Kovacs — 2011-04-30T18:44:11+00:00 URL: https://externals.io/message/52097#52113 · In reply to: #52110 > Really would depend on the user. Part of my company still runs on SQLServer > 2003, for instance... > > Corporate infrastructure rarely sees upgrades. Your cousin with the > wordpress fetish, on the other hand... > > what is SQLServer 2003? can't see that on http://en.wikipedia.org/wiki/Microsoft_SQL_Server btw. SQLServer has more enterprise level lifecycle: http://support.microsoft.com/lifecycle/?p1=2852 http://support.microsoft.com/lifecycle/?p1=2852 it's much longer than the mysql 2 years after first GA + 3 years extended support. http://www.mysqlperformanceblog.com/2008/08/22/mysql-end-of-life-eol-policy/ so imo enterprise doesn't mean that you run EOLed version, but that you have longer support so you can upgrade less frequently. for example PCI-DSS mandates: "All critical systems must have the most recently released software patches to prevent exploitation. Organizations should apply patches to less-critical systems as soon as possible, based on a risk-based vulnerability management program." I don't see why can't we change this for php-next. That would still give us time to document the change and for the users to take actions. I don't think that many people who uses 5.0 when php-next get released would be eager to upgrade. Tyrael Tyrael --- ## #52114 — Matt Wilson — 2011-04-30T18:45:33+00:00 URL: https://externals.io/message/52097#52114 · In reply to: #52113 Sorry, you're right. It's 2000 service pack 3 :) > > > > Really would depend on the user. Part of my company still runs on SQLServer 2003, for instance... > > Corporate infrastructure rarely sees upgrades. Your cousin with the wordpress fetish, on the other hand... > > > what is SQLServer 2003? > can't see that on http://en.wikipedia.org/wiki/Microsoft_SQL_Server > btw. SQLServer has more enterprise level lifecycle: > http://support.microsoft.com/lifecycle/?p1=2852 > http://support.microsoft.com/lifecycle/?p1=2852 > > it's much longer than the mysql 2 years after first GA + 3 years extended support. > http://www.mysqlperformanceblog.com/2008/08/22/mysql-end-of-life-eol-policy/ > > so imo enterprise doesn't mean that you run EOLed version, but that you have longer support so you can upgrade less frequently. > for example PCI-DSS mandates: > "All critical systems must have the most recently released software patches to prevent exploitation. > Organizations should apply patches to less-critical systems as soon as possible, based on a risk-based vulnerability management program." > > I don't see why can't we change this for php-next. > That would still give us time to document the change and for the users to take actions. > I don't think that many people who uses 5.0 when php-next get released would be eager to upgrade. > > Tyrael > > Tyrael --- ## #52115 — Matt Wilson — 2011-04-30T18:47:16+00:00 URL: https://externals.io/message/52097#52115 · In reply to: #52113 Actually, I'm talking out my ass. I know it's some version prior to 2005 and I'm only guessing I meant SP3. I don't work on it, i've just heard the number 3 thrown around with it. I'll remove myself from the conversation before I make a larger fool of myself :) > > > > Really would depend on the user. Part of my company still runs on SQLServer 2003, for instance... > > Corporate infrastructure rarely sees upgrades. Your cousin with the wordpress fetish, on the other hand... > > > what is SQLServer 2003? > can't see that on http://en.wikipedia.org/wiki/Microsoft_SQL_Server > btw. SQLServer has more enterprise level lifecycle: > http://support.microsoft.com/lifecycle/?p1=2852 > http://support.microsoft.com/lifecycle/?p1=2852 > > it's much longer than the mysql 2 years after first GA + 3 years extended support. > http://www.mysqlperformanceblog.com/2008/08/22/mysql-end-of-life-eol-policy/ > > so imo enterprise doesn't mean that you run EOLed version, but that you have longer support so you can upgrade less frequently. > for example PCI-DSS mandates: > "All critical systems must have the most recently released software patches to prevent exploitation. > Organizations should apply patches to less-critical systems as soon as possible, based on a risk-based vulnerability management program." > > I don't see why can't we change this for php-next. > That would still give us time to document the change and for the users to take actions. > I don't think that many people who uses 5.0 when php-next get released would be eager to upgrade. > > Tyrael > > Tyrael --- ## #52116 — Anthony Ferrara — 2011-04-30T18:59:53+00:00 URL: https://externals.io/message/52097#52116 · In reply to: #52112 I'm not arguing if there weren't reasons for implementing it this way. I am arguing if they are good enough reasons to justify the security impact. It's not my decision (and I respect that), but I would stress that what PDO is doing is not prepared statements or even parameterized queries, and as such does not have the same benefits of using true prepared statements (and perhaps the documentation needs to be updated to reflect that). Anthony > > > > […] > > I'm not disagreeing, I just don't agree it is a bug against 5.3. There were > good reasons for the default at the time 5.3 was released. For 5.4 it is > probably time to switch it. > > -Rasmus --- ## #52117 — Rasmus Lerdorf — 2011-04-30T19:01:31+00:00 URL: https://externals.io/message/52097#52117 · In reply to: #52116 > I'm not arguing if there weren't reasons for implementing it this way. > I am arguing if they are good enough reasons to justify the security > impact. It's not my decision (and I respect that), but I would stress > that what PDO is doing is not prepared statements or even > parameterized queries, and as such does not have the same benefits of > using true prepared statements (and perhaps the documentation needs to > be updated to reflect that). How is native prepared statements any more secure than emulated ones? Neither will completely protect you against SQLi. -Rasmus --- ## #52118 — Anthony Ferrara — 2011-04-30T19:05:38+00:00 URL: https://externals.io/message/52097#52118 · In reply to: #52117 Native prepared statements will completely protect you from injection via any of the bound parameters. The wire-level passage of the data is completely different (and the data is sent by length, rather than by deliminator). As an exercise, view the traffic that's sent to the server via Wireshark... As such, they are not subject to proper escaping by character set. > > > […] > > How is native prepared statements any more secure than emulated ones? > Neither will completely protect you against SQLi. > > -Rasmus --- ## #52119 — Rasmus Lerdorf — 2011-04-30T19:12:00+00:00 URL: https://externals.io/message/52097#52119 · In reply to: #52118 > Native prepared statements will completely protect you from injection > via any of the bound parameters. The wire-level passage of the data > is completely different (and the data is sent by length, rather than > by deliminator). As an exercise, view the traffic that's sent to the > server via Wireshark... As such, they are not subject to proper > escaping by character set. As long as PDO and MySQL agree on which character set is in use bound parameters are safe in emulated prepares as well. -Rasmus --- ## #52120 — Anthony Ferrara — 2011-04-30T19:17:59+00:00 URL: https://externals.io/message/52097#52120 · In reply to: #52119 Correct. But prior to 5.3.6 it was not possible to have PDO and MySQL agree on the character set (at least without having root access to the server). And after 5.3.6, the DSN *must* include the charset parameter to make them agree. The common technique of setting the charset via SET NAMES (which most frameworks and libraries do) will not work. So instead you have people using prepared statements thinking that they are safe (since all documentation says so) while they are actually not. My suggestion is to make them actually safe right from the source rather than requiring an API change. The best solution would be education and raising awareness as well as fixing it in the core. The code is there to do so, all it takes is a single bit change in the source... Again, that's just my opinion... Anthony > > > […] > > As long as PDO and MySQL agree on which character set is in use bound > parameters are safe in emulated prepares as well. > > -Rasmus --- ## #52121 — Ferenc Kovacs — 2011-04-30T19:58:35+00:00 URL: https://externals.io/message/52097#52121 · In reply to: #52117 > > > > […] > > How is native prepared statements any more secure than emulated ones? > Neither will completely protect you against SQLi. > > -Rasmus > > real prepared statements push the parameter binding to the server, so you have only one component where things could go wrong, with emulated prepared statements you can be screwed both via the php parameter binding or when the query is executed on the server (for example via the misunderstanding in the encoding by the php lib and the mysql server) Tyrael --- ## #52123 — Clint Byrum — 2011-04-30T20:05:31+00:00 URL: https://externals.io/message/52097#52123 · In reply to: #52108 Excerpts from Rasmus Lerdorf's message of Sat Apr 30 10:53:30 -0700 2011: > > > […] > > People upgrade their databases even slower than they upgrade their PHP. That said, MySQL 5.0 is only in "Extended Support"[1] (read: security only) from Oracle, and will likely be deprecated to full EOL at some point in the near future. I think its fair to say that if something is a massive problem for a version that the authors don't even support, its probably ok to leave those users behind with defaults, as long as you give them a way to turn it off. So maybe this could be considered blocked only by 5.0's EOL. -- [1] http://www.mysql.com/support/eol-notice.html --- ## #52124 — Ferenc Kovacs — 2011-04-30T21:26:57+00:00 URL: https://externals.io/message/52097#52124 · In reply to: #52123 > Excerpts from Rasmus Lerdorf's message of Sat Apr 30 10:53:30 -0700 2011: > > […] > > That said, MySQL 5.0 is only in "Extended Support"[1] (read: security > only) from Oracle, and will likely be deprecated to full EOL at some > point in the near future. I think its fair to say that if something is > a massive problem for a version that the authors don't even support, > its probably ok to leave those users behind with defaults, as long as > you give them a way to turn it off. So maybe this could be considered > blocked only by 5.0's EOL. > > -- > [1] http://www.mysql.com/support/eol-notice.html > > if oracle keeps the original lifecycle of the mysql product line, then the 2 year extended support is 2 years from the eol of the active support, so it means that 5.0 extended will end 2011 dec 31. but the extended support is only available for those who bought the premium support, so for the rest of us, 5.0 is already EOL Tyrael --- ## #52126 — Pierre Joye — 2011-05-01T00:01:07+00:00 URL: https://externals.io/message/52097#52126 · In reply to: #52124 hi, It is also important to keep in mind that there are a growing alternative to Oracle's mysql, so focusing only on Oracle plans is not necessary a good thing to do. That being said, it seems that we have an agreement to change this behavior in trunk. It is a documentation matter then. There is no need to argue to death about this issue. And last but not least, let the mysql developers reply and decide what they want to do. Cheers, > > > > […] > if oracle keeps the original lifecycle of the mysql product line, then the 2 > year extended support is 2 years from the eol of the active support, so it > means that 5.0 extended will end 2011 dec 31. > but the extended support is only available for those who bought the premium > support, so for the rest of us, 5.0 is already EOL > > Tyrael > -- Pierre @pierrejoye | http://blog.thepimp.net | http://www.libgd.org --- Next page: https://externals.io/message/52097.md?page=2