Very long running query (600ms): could be security issue

Discussion of general topics related to the new version 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!
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!
Post Reply
jmpoure
Registered User
Posts: 81
Joined: Sun Mar 11, 2007 9:42 am

Very long running query (600ms): could be security issue

Post by jmpoure »

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

jmpoure
Registered User
Posts: 81
Joined: Sun Mar 11, 2007 9:42 am

Re: Very long running query (600ms): could be security issue

Post by jmpoure »

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.

code reader
Registered User
Posts: 653
Joined: Wed Sep 21, 2005 3:01 pm

Re: Very long running query (600ms): could be security issue

Post by code reader »

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

User avatar
DavidMJ
Registered User
Posts: 932
Joined: Thu Jun 16, 2005 1:14 am
Location: Great Neck, NY

Re: Very long running query (600ms): could be security issue

Post by DavidMJ »

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

TerryE
Registered User
Posts: 95
Joined: Sat May 23, 2009 12:24 am
Contact:

Re: Very long running query (600ms): could be security issue

Post by TerryE »

code reader wrote:to trigger this query, you should have a forum that uses approval
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 as
  • 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);
(though the join form uses a better index). The second select is a single row fetch to get the timestamp of the target post. The main query uses the tid_post_time(topic_id, post_time) index. This needs to fetch the N rows -- one per post for every post ahead of the referenced one in the topic. AFAIR (unlike PostgreSQL which will use the index only when all the data elements are in the index) MySQL still goes to the rows. There are two things that you need to do to get the runtime down.
  • 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.
This query has the same execution plan with and without the post_approved = 1 clause and given that this is true for virtually all posts, adding post_approved to the index won't really help.

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?

Post Reply