UBB.Developers Forums General Discussion Suggestions and Feedback What is this query doing ??

[:"red"]
SELECT t1.B_Number,t1.B_Main,t3.U_Username,t1.B_Subject,t1.B_Posted,t1.B_Board,
t3.U_Color,t1.B_Reged,t1.B_Icon,t2.Bo_Title,t2.Bo_Read_Perm,t2.Bo_Cat,
t1.B_Posterid,t1.B_Status,t1.B_AnonName,t1.B_Body
FROM
w3t_Posts AS t1, w3t_Boards AS t2, w3t_Users AS t3
WHERE
t1.B_Approved = 'yes' AND t1.B_Board = t2.Bo_Keyword
AND t1.B_Posterid = t3.U_Number AND t1.B_Status <> 'M'
AND t1.B_Board IN ('board1','board2','other boards...')
ORDER BY B_Posted DESC LIMIT 26
[/]


Hi , I wonder what is this query string doing ? and how to 'trigger' this query in UBBThreads ?
It seems this query is from dosearch.php ...
But , to prevent serious search loading , I've removed all search link and forms in my threads. Even registered users can not search.

But I often observed this query running and cause my site hang ...
How does ubbthreads trigger this query ? What templates or code should I change ?

If I am not wrong , dosearch should do SQL LIKE search , and "show user's all post query" . The former can be removed by editing search.tmpl and postlist.tmpl . And the later query is 'light' to MySQL.

But I just cannot figure how to trigger this strange query ... And ... how to prevent it ?

That's a normal search query.
The easiest way to prevent any searches is to insert a code snippet that displays an error messages (by using the not_right function).

Shouldn't normal search something like 'SQL LIKE' ???
It had been disabled long time ago...

Looks like the show all posts by a user search (from their profile)

[]JoshPet said:
Looks like the show all posts by a user search (from their profile) [/]

Can you tell me how to do this ? (In what page ? Press what button ? or what link ?)
I just cannot reproduce this query ...
I don't know who and I don't know how he/she invoke this query ...
But it occures so often , that my site often hangs because of this strange query...

I *think* it's the show all posts by a user.

View someone's profile and click "Show all this user's posts".

although maybe not - as I see there's a board query with it as well.

[]JoshPet said:
I *think* it's the show all posts by a user.

View someone's profile and click "Show all this user's posts".

although maybe not - as I see there's a board query with it as well. [/]

No , it is not ...
"Show all this user's posts" is very 'light' to MySQL , almost can be done in 0.5 seconds.

But this strange query is very heavy , almost cause my server down.

The code is in dosearch.php , line 249 - 261 (UBBthreads 6.4.2)

What action (page ? link ? button ?) cause this query ?

I don't think it is caused by search.php , because search.php's action is another heavy 'SQL LIKE' query , not this strange/heave query. Furthermore , I had already removed 'Submit' button in search page , that no one can search.

Hmm, I know you said you removed all of the links to the search on your forums, but did you remove the actual search.php file? If not, maybe people are accessing it directly by typing in the URL? Not likely, but another thought

[]Jeremy said:
Hmm, I know you said you removed all of the links to the search on your forums, but did you remove the actual search.php file? If not, maybe people are accessing it directly by typing in the URL? Not likely, but another thought [/]

If I remove search.php , then "Show the user's all posts" will not work...
That's not what I want ...

search is just the interface - it doesn't do any queries.

everything (inlcuding show the user's posts) happens from dosearch.php. It's the only script which does searches. If it's a problem, that'd be the one to remove.

[]JoshPet said:
search is just the interface - it doesn't do any queries.

everything (inlcuding show the user's posts) happens from dosearch.php. It's the only script which does searches. If it's a problem, that'd be the one to remove. [/]

sorry , just typo .
If I remove dosearch.php , then "Show the user's all post" will not work , which is not what I want.


Anyway , can anybody tell me how does dosearch.php line 249-261 be triggered (UBBT 6.4.2) ? What page ? what link ? what button ?
It is the most important question ... (and still nobody knows the answer)

There is only ONE query doing a SELECT in dosearch - it does all searches, includeing the show all posts by this user. All searching is done with that query. So I'm not sure what you're asking.
You either want it or you don't.

The very thing you want to keep is what you're asking how to remove.

[]
It is the most important question ... (and still nobody knows the answer) [/]
It's been answered in this thread - at least 3 times.

Hi,

No, he wants to know what else is causing this statement to get called, and how to prevent it.

At least that is how I read his question.

Sanuk!

It's a search being done.

How to prevent it - take away the ability to search.

At first I thought that was someone searching for all posts by a user - now that I edited the post so I could see the whole thing without scrolling, it looks like someone searching for all posts in a particular category. Just going there, clicking on a category, and clicking search. Or even leving it on all forums and clicking search.

The search is run from several places (search.php, showprofile.php, and ubbthreads.php) so if you wanted to toally kill it - you'd probably want to remove dosearch.php.

That's all been said above by several different people.

6.5 has some better checks in place to prohibit such broad searches, you can require how much they have to fill in and apply a flood control to search. Otherwise, broad searches can be a killer. 6.4.2 should have a date restirction in place by default though of 1 month.

Short of removing it, you might try restricting it to registered users only.

In dosearch.php after the authenticate() function, add this:



That might slow people down a bit if you don't want to toally remove it.

Let me clarify my question ...

There are three type of 'search' :

• 1. Show the user's all post (from showprofile.php) : This is done in dosearch.php , and it is very 'light' to MySQL , and can be response in 1 second. I DO NOT want to disable it.
  
• 2. Text Search : This is done in search.php/tmpl and postlist.tmpl (at bottom) , and this form posts to dosearch.php and trigger 'SQL LIKE' query . This is a very heavy query , I have disabled both button in search.tmpl and postlist.tmpl , that no one can do any text search.
  
• 3. This strange query . It is not a 'SQL LIKE' query , so , it is not text search. I observed this query from mytop ( http://jeremy.zawodny.com/mysql/mytop/ ) . This query often crashes my system , but I just cannot re-produce this query . I don't who clicks what button or link triggers this strange query. And when I manual query this query , just got a list of posts , not from a specific user.
  

All three are done with the only query in dosearch.php. There's only one place any search queries are done. The LIKE query isn't there because nobody typed anything to search for.

See my post above. I originally thought it was the show all posts by a user. But I couldn't see the whole query because it was stretched way left/right.

I think it's a search without typing anything in the box. Those searches can also be generated from the bottom of the postlist page - just click Search.

But every search is done by that one query. There are just different WHERE clauses depending on what was entered.

Just becaue you've disabled the search.php page - doesn't mean that anyone half clever couldn't do it with a URL. You can call the dosearch.php script directly.

See Click Here a search on all forums, no date restriction, and no keywords. Notice how long it takes - its a real server killer.

Like I say, there are some improvements with this in 6.5.

[]JoshPet said:
See Click Here a search on all forums, no date restriction, and no keywords. Notice how long it takes - its a real server killer.

Like I say, there are some improvements with this in 6.5. [/]

OK , I know , ...
Maybe somebody clicks some link in some post , and that post contains a search link...


Hope 6.5 will be released soon...

And if it's in a post - spiders could be triggering it.

You could try this - if the only query you want is the show all posts by a user.

In dosearch.php

After all the get input steps, try this (untested)


if (($Name) && (!$Words)) {
exit;
}

See if the user search still works, but it kills the other query.

[]smallufo said:
SELECT t1.B_Number,t1.B_Main,t3.U_Username,t1.B_Subject,t1.B_Posted,t1.B_Board,
t3.U_Color,t1.B_Reged,t1.B_Icon,t2.Bo_Title,t2.Bo_Read_Perm,t2.Bo_Cat,
t1.B_Posterid,t1.B_Status,t1.B_AnonName,t1.B_Body
FROM
w3t_Posts AS t1, w3t_Boards AS t2, w3t_Users AS t3
WHERE
t1.B_Approved = 'yes' AND t1.B_Board = t2.Bo_Keyword
AND t1.B_Posterid = t3.U_Number AND t1.B_Status <> 'M'
AND t1.B_Board IN ('board1','board2','other boards...')
ORDER BY B_Posted DESC LIMIT 26 [/]

As you can see, this query doesn't have any search words. As Josh said, you might want to insert some checks to make sure that the search is properly formated and provide an error message if not.
This is a bit tricky, because the dosearch script is called from different locations, as you already said.
So you can't deny all searches that have no words, because it will break the "show all user's posts" search.

I finally found the answer !!!

In UBBT 6.4.2 (or maybe earler) , dosearch.php doesn't prevent 'null_parameter' query. Therefore , anybody just type http://yoursite.com/ubbthreads/dosearch.php can produce this query and cause serious MySQL overhead (if you have a lot of posts)

Solution :
Add the code after line 263 in dosearch.php



I think this is a serious bug , maybe there should be a formal bugfix.