Previous Thread
Next Thread
Print Thread
Rate Thread
#274802 06/10/2004 11:01 AM
Joined: May 1999
Posts: 39
Journeyman
Journeyman
Offline
Joined: May 1999
Posts: 39
[:"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 ?

Last edited by JoshPet; 06/11/2004 12:28 AM.
Sponsored Links
Joined: Dec 2000
Posts: 1,471
Addict
Addict
Offline
Joined: Dec 2000
Posts: 1,471
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).

Joined: May 1999
Posts: 39
Journeyman
Journeyman
Offline
Joined: May 1999
Posts: 39
Shouldn't normal search something like 'SQL LIKE' ???
It had been disabled long time ago...

Joined: Nov 2001
Posts: 10,369
I type Like navaho
I type Like navaho
Joined: Nov 2001
Posts: 10,369
Looks like the show all posts by a user search (from their profile)

Joined: May 1999
Posts: 39
Journeyman
Journeyman
Offline
Joined: May 1999
Posts: 39
[]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...

Sponsored Links
Joined: Nov 2001
Posts: 10,369
I type Like navaho
I type Like navaho
Joined: Nov 2001
Posts: 10,369
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.

Joined: May 1999
Posts: 39
Journeyman
Journeyman
Offline
Joined: May 1999
Posts: 39
[]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.

Joined: Feb 2002
Posts: 1,759
Addict
Addict
Offline
Joined: Feb 2002
Posts: 1,759
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

Joined: May 1999
Posts: 39
Journeyman
Journeyman
Offline
Joined: May 1999
Posts: 39
[]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 ...

Joined: Nov 2001
Posts: 10,369
I type Like navaho
I type Like navaho
Joined: Nov 2001
Posts: 10,369
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.

Sponsored Links
Joined: May 1999
Posts: 39
Journeyman
Journeyman
Offline
Joined: May 1999
Posts: 39
[]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)

Joined: Nov 2001
Posts: 10,369
I type Like navaho
I type Like navaho
Joined: Nov 2001
Posts: 10,369
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.

Joined: Feb 2002
Posts: 295
Member
Member
Offline
Joined: Feb 2002
Posts: 295
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!

Joined: Nov 2001
Posts: 10,369
I type Like navaho
I type Like navaho
Joined: Nov 2001
Posts: 10,369
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:

Code
 <br />if (!$user['U_Username']) { <br />    $html -> not_right("You must be logged in to search",$Cat); <br />} <br />


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

Joined: May 1999
Posts: 39
Journeyman
Journeyman
Offline
Joined: May 1999
Posts: 39
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.

Joined: Nov 2001
Posts: 10,369
I type Like navaho
I type Like navaho
Joined: Nov 2001
Posts: 10,369
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.

Joined: May 1999
Posts: 39
Journeyman
Journeyman
Offline
Joined: May 1999
Posts: 39
[]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...

Joined: Nov 2001
Posts: 10,369
I type Like navaho
I type Like navaho
Joined: Nov 2001
Posts: 10,369
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.

Joined: Dec 2000
Posts: 1,471
Addict
Addict
Offline
Joined: Dec 2000
Posts: 1,471
[]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.

Joined: May 1999
Posts: 39
Journeyman
Journeyman
Offline
Joined: May 1999
Posts: 39
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

Code
  <br />if ( !$Words)<br />{<br />  if ( (!$usersearch) && (!$newertime) && (!$oldertime) && (!$catonly))<br />  {<br />    $query="";<br />    exit;<br />  }<br />}<br />


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


Link Copied to Clipboard
Donate Today!
Donate via PayPal

Donate to UBBDev today to help aid in Operational, Server and Script Maintenance, and Development costs.

Please also see our parent organization VNC Web Services if you're in the need of a new UBB.threads Install or Upgrade, Site/Server Migrations, or Security and Coding Services.
Recommended Hosts
We have personally worked with and recommend the following Web Hosts:
Stable Host
bluehost
InterServer
Visit us on Facebook
Member Spotlight
isaac
isaac
California
Posts: 1,157
Joined: July 2001
Forum Statistics
Forums63
Topics37,573
Posts293,925
Members13,849
Most Online5,166
Sep 15th, 2019
Today's Statistics
Currently Online
Topics Created
Posts Made
Users Online
Birthdays
Top Posters
AllenAyres 21,079
JoshPet 10,369
LK 7,394
Lord Dexter 6,708
Gizmo 5,833
Greg Hard 4,625
Top Posters(30 Days)
Top Likes Received
isaac 82
Gizmo 20
Brett 7
WebGuy 2
Top Likes Received (30 Days)
None yet
The UBB.Developers Network (UBB.Dev/Threads.Dev) is ©2000-2024 VNC Web Services

 
Powered by UBB.threads™ PHP Forum Software 8.0.0
(Preview build 20221218)