Hello,
My board has more than 600.000 messages. I log slow running queries, more than 30ms.
In the log, I can read every second or so:
<code>2009-05-15 10:31:46 CEST LOG: durée : 606.712 ms, instruction : SELECT COUNT(p1.post_id) AS prev_posts
FROM phpbb_posts p1, phpbb_posts p2
WHERE p1.topic_id = 43596
AND p2.post_id = 751391
AND p1.post_approved = 1
AND p1.post_time <= p2.post_time</code>
This query executes in 6 seconds. I don't know where this query comes from, but it could be an overflow in PHP if attackers could query this function several times every second. Could this be changed to something less SQL extensive?
Kind regards,
Jean-Michel
Very long running query (600ms): could be security issue
Forum rules
Discussion of general topics related to the new release and its place in the world. Don't discuss new features, report bugs, ask for support, et cetera. Don't use this to spam for other boards or attack those boards!
Discussion of general topics related to the new release and its place in the world. Don't discuss new features, report bugs, ask for support, et cetera. Don't use this to spam for other boards or attack those boards!
Re: Very long running query (600ms): could be security issue
This query is slowing down my server considerably. Example :
2009-05-15 11:14:17 CEST LOG: durée : 630.813 ms, instruction : SELECT COUNT(p1.post_id) AS prev_posts
FROM phpbb_posts p1, phpbb_posts p2
WHERE p1.topic_id = 43596
AND p2.post_id = 792501
AND p1.post_approved = 1
AND p1.post_time <= p2.post_time
2009-05-15 11:14:25 CEST LOG: durée : 630.163 ms, instruction : SELECT COUNT(p1.post_id) AS prev_posts
FROM phpbb_posts p1, phpbb_posts p2
WHERE p1.topic_id = 43596
AND p2.post_id = 792478
AND p1.post_approved = 1
AND p1.post_time <= p2.post_time
Two queries in 1 second !
If attackers understand where this query comes from, it can be used to drive down ANY server running PhpBB.
2009-05-15 11:14:17 CEST LOG: durée : 630.813 ms, instruction : SELECT COUNT(p1.post_id) AS prev_posts
FROM phpbb_posts p1, phpbb_posts p2
WHERE p1.topic_id = 43596
AND p2.post_id = 792501
AND p1.post_approved = 1
AND p1.post_time <= p2.post_time
2009-05-15 11:14:25 CEST LOG: durée : 630.163 ms, instruction : SELECT COUNT(p1.post_id) AS prev_posts
FROM phpbb_posts p1, phpbb_posts p2
WHERE p1.topic_id = 43596
AND p2.post_id = 792478
AND p1.post_approved = 1
AND p1.post_time <= p2.post_time
Two queries in 1 second !
If attackers understand where this query comes from, it can be used to drive down ANY server running PhpBB.
-
- Registered User
- Posts: 653
- Joined: Wed Sep 21, 2005 3:01 pm
Re: Very long running query (600ms): could be security issue
this query is from viewtopic.
REPRODUCTION
to trigger this query, you should have a forum that uses approval (i.e., at least one user group can post but not without approval), and a topic in this forum that spans several pages. viewing any topic except the first of last page should trigger this query.
apparently you already have all the prerequisites, so all you need to do is figure which forum/topic you need to view.
*note that this reproduction analysis is based on reading the code. i did not actually test it myself*
POSSIBLE (untested) SOLUTION
you might want to try and add to phpbb_posts a new index, based on (post_approved, topic_id, post_time).
(alter table phpbb_posts add index app_tid_ptime (post_approved, topic_id, post_time);)
i can not guarantee that this will help, but if it will you should definitely report it.
before going to this length, i would suggest adding the index i suggested and measure the query time with and without this index by running the query manually.
if it makes a huge difference, maybe you want to report this on one of the "performance tweaks" threads here or on phpbb.com
REPRODUCTION
to trigger this query, you should have a forum that uses approval (i.e., at least one user group can post but not without approval), and a topic in this forum that spans several pages. viewing any topic except the first of last page should trigger this query.
apparently you already have all the prerequisites, so all you need to do is figure which forum/topic you need to view.
*note that this reproduction analysis is based on reading the code. i did not actually test it myself*
POSSIBLE (untested) SOLUTION
you might want to try and add to phpbb_posts a new index, based on (post_approved, topic_id, post_time).
(alter table phpbb_posts add index app_tid_ptime (post_approved, topic_id, post_time);)
i can not guarantee that this will help, but if it will you should definitely report it.
before going to this length, i would suggest adding the index i suggested and measure the query time with and without this index by running the query manually.
if it makes a huge difference, maybe you want to report this on one of the "performance tweaks" threads here or on phpbb.com
Re: Very long running query (600ms): could be security issue
That index will not help. What might help is some sort of partitioning of the table into two tables, one for unapproved messages and one for approved. This would allow the lookups to be much faster but would require us to do a minimal amount of extra work on the PHP side of things...
Freedom from fear
Re: Very long running query (600ms): could be security issue
Unfortunately not true as it is executed on any forum where a user views a topic using a post reference. Even though the query is a join it is functionally the same ascode reader wrote:to trigger this query, you should have a forum that uses approval
- SELECT COUNT(post_time) AS prev_posts FROM phpbb_en_posts WHERE post_approved = 1 AND topic_id = 8500 AND post_time <= (select post_time FROM phpbb_en_posts WHERE post_id = 40769);
- The first only applies if you have control over your mysql config (that is your D/B isn't on a shared service) and that is to ensure that your indexes are fully cached. The key_buffer parameter in /etc/mysql/my.cnf is important. Set this as large as possible (e.g. 128Mb or more for the number of posts that you have). You can see whether the indices are properly cached by doing a show global status like 'Key%'.
- The other killer is having huge topics when users access them by post_id. OK, phpBB already optimises the first and last post IDs, but otherwise (if this query isn't cached) if you have 600K posts then this will often involve up to N seeks for the Nth post. So
Custom BBCodes (last but one post) will hit the phpBB forum hard. One way to avoid this is to take those top dozen or so hot topics and split them at, say, the 3 month point and edit the spanning posts to link the two together. All of the hits will be against the short tail topics, but the earlier posts will still be accessible.
I did the equivalent query on my dev LAMP VM (our longest topic has 96 posts); it took 0.00sec on the mysql clock. I then redid it on my prod system just in case I had cached rows in selecting which topic/post to use. Again: 1 row in set (0.00 sec). My point is that you might asking the wrong Q. Instead of asking what is wrong with phpBB, you might consider asking: have I got the correct service/database configuration for my board size and usage?
Forum Admin OpenOffice.org User Groups