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
I have already reported this issue on the bug tracker:
http://bugs.php.net/bug.php?id=54638But 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
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
I have already reported this issue on the bug tracker:
http://bugs.php.net/bug.php?id=54638But 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
I have already reported this issue on the bug tracker:
http://bugs.php.net/bug.php?id=54638But 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.
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
Am 30.04.2011 17:45, schrieb Ferenc Kovacs:
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/
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)
On Sat, Apr 30, 2011 at 6:02 PM, Reindl Harald h.reindl@thelounge.netwrote:
Am 30.04.2011 17:45, schrieb Ferenc Kovacs:
On Sat, Apr 30, 2011 at 5:39 PM, Rasmus Lerdorf rasmus@lerdorf.com
wrote: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/
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
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
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
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, butSince 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
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, butSince 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
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
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...
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, butSince 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
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
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."
http://www.mysqlperformanceblog.com/2008/08/22/mysql-end-of-life-eol-policy/
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
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=2852it'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
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=2852it'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
Am 30.04.2011 20:10, schrieb Ferenc Kovacs:
People upgrade their databases even slower than they upgrade their PHP.
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
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
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
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
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
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.
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
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
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
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
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
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
Excerpts from Rasmus Lerdorf's message of Sat Apr 30 10:53:30 -0700 2011:
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, butSince 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.
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.
Excerpts from Rasmus Lerdorf's message of Sat Apr 30 10:53:30 -0700 2011:
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, butSince 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.
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.
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
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,
Excerpts from Rasmus Lerdorf's message of Sat Apr 30 10:53:30 -0700 2011:
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, butSince 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.
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.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 EOLTyrael
--
Pierre
@pierrejoye | http://blog.thepimp.net | http://www.libgd.org
Hi,
I have already reported this issue on the bug tracker:
http://bugs.php.net/bug.php?id=54638
Besides the question about query cache and security there is a bit more to keep in mind:
Using native prepared statements means one more round-trip between PHP and MySQL which might impact performance. This is even more relevant as PDO::query will go through the PS API ... While PS uses a more compact binary protocol.
Also there are still a few (minor) statements which can't be prepared in MySQL where PDO will silently fall back silently to emulation. (don't have the list at hand, but should be in the docs)
Also mind that native PS still runs through PDO's SQL parser to handle place holder so it won't solve all issues in there.
johannes