Strange Query on Client's Site

Turns out the culprit is AjaxSearch.  I was using AjaxSearch 1.6 still on that site, that is why the SQL did not exactly match what is now in the 1.8.1 version.  However, I upgraded to that newest version and can replicate the extremely long SQL query.  I just do a search for:

"food assets assets assets assets assets assets assets assets assets assets assets plugins directresize highslide assets assets plugins directresize highslide assets assets jquery-1.2.1.min.js.gz"

Or any other search with lots of keywords and the query is extremely long.  So I imagine this was an attack of some sort.  Does AjaxSearch allow for some way to limit the amount of keywords that will be queried?

For example, placing this in the search form:

wine food stuff chambourcin cab franc basket menu selection vinticultural retail location dinner party worry matching

Produces the following query:

SELECT sc.id, sc.pagetitle, sc.longtitle, sc.description, sc.alias, sc.introtext, sc.menutitle, sc.content, sc.publishedon, GROUP_CONCAT( DISTINCT CAST(ntv.id AS CHAR) SEPARATOR "," ) AS tv_id, GROUP_CONCAT( DISTINCT ntv.value SEPARATOR ", " ) AS tv_value FROM `modx_site_content` sc LEFT JOIN( SELECT DISTINCT tv.id, tv.value, tv.contentid FROM `modx_site_tmplvar_contentvalues` tv WHERE (((tv.value LIKE ’%wine%’) OR (tv.value LIKE ’%food%’) OR (tv.value LIKE ’%stuff%’) OR (tv.value LIKE ’%chambourcin%’) OR (tv.value LIKE ’%cab%’) OR (tv.value LIKE ’%franc%’) OR (tv.value LIKE ’%basket%’) OR (tv.value LIKE ’%menu%’) OR (tv.value LIKE ’%selection%’) OR (tv.value LIKE ’%vinticultural%’) OR (tv.value LIKE ’%retail%’) OR (tv.value LIKE ’%location%’) OR (tv.value LIKE ’%dinner%’) OR (tv.value LIKE ’%party%’) OR (tv.value LIKE ’%worry%’) OR (tv.value LIKE ’%matching%’))) ) AS ntv ON sc.id = ntv.contentid WHERE ((sc.published=1) AND (sc.searchable=1) AND (sc.deleted=0) AND (sc.type=’document’) AND (sc.privateweb=0)) GROUP BY sc.id HAVING (((sc.pagetitle LIKE ’%wine%’) OR (sc.longtitle LIKE ’%wine%’) OR (sc.description LIKE ’%wine%’) OR (sc.alias LIKE ’%wine%’) OR (sc.introtext LIKE ’%wine%’) OR (sc.menutitle LIKE ’%wine%’) OR (sc.content LIKE ’%wine%’) OR (tv_value LIKE ’%wine%’)) OR ((sc.pagetitle LIKE ’%food%’) OR (sc.longtitle LIKE ’%food%’) OR (sc.description LIKE ’%food%’) OR (sc.alias LIKE ’%food%’) OR (sc.introtext LIKE ’%food%’) OR (sc.menutitle LIKE ’%food%’) OR (sc.content LIKE ’%food%’) OR (tv_value LIKE ’%food%’)) OR ((sc.pagetitle LIKE ’%stuff%’) OR (sc.longtitle LIKE ’%stuff%’) OR (sc.description LIKE ’%stuff%’) OR (sc.alias LIKE ’%stuff%’) OR (sc.introtext LIKE ’%stuff%’) OR (sc.menutitle LIKE ’%stuff%’) OR (sc.content LIKE ’%stuff%’) OR (tv_value LIKE ’%stuff%’)) OR ((sc.pagetitle LIKE ’%chambourcin%’) OR (sc.longtitle LIKE ’%chambourcin%’) OR (sc.description LIKE ’%chambourcin%’) OR (sc.alias LIKE ’%chambourcin%’) OR (sc.introtext LIKE ’%chambourcin%’) OR (sc.menutitle LIKE ’%chambourcin%’) OR (sc.content LIKE ’%chambourcin%’) OR (tv_value LIKE ’%chambourcin%’)) OR ((sc.pagetitle LIKE ’%cab%’) OR (sc.longtitle LIKE ’%cab%’) OR (sc.description LIKE ’%cab%’) OR (sc.alias LIKE ’%cab%’) OR (sc.introtext LIKE ’%cab%’) OR (sc.menutitle LIKE ’%cab%’) OR (sc.content LIKE ’%cab%’) OR (tv_value LIKE ’%cab%’)) OR ((sc.pagetitle LIKE ’%franc%’) OR (sc.longtitle LIKE ’%franc%’) OR (sc.description LIKE ’%franc%’) OR (sc.alias LIKE ’%franc%’) OR (sc.introtext LIKE ’%franc%’) OR (sc.menutitle LIKE ’%franc%’) OR (sc.content LIKE ’%franc%’) OR (tv_value LIKE ’%franc%’)) OR ((sc.pagetitle LIKE ’%basket%’) OR (sc.longtitle LIKE ’%basket%’) OR (sc.description LIKE ’%basket%’) OR (sc.alias LIKE ’%basket%’) OR (sc.introtext LIKE ’%basket%’) OR (sc.menutitle LIKE ’%basket%’) OR (sc.content LIKE ’%basket%’) OR (tv_value LIKE ’%basket%’)) OR ((sc.pagetitle LIKE ’%menu%’) OR (sc.longtitle LIKE ’%menu%’) OR (sc.description LIKE ’%menu%’) OR (sc.alias LIKE ’%menu%’) OR (sc.introtext LIKE ’%menu%’) OR (sc.menutitle LIKE ’%menu%’) OR (sc.content LIKE ’%menu%’) OR (tv_value LIKE ’%menu%’)) OR ((sc.pagetitle LIKE ’%selection%’) OR (sc.longtitle LIKE ’%selection%’) OR (sc.description LIKE ’%selection%’) OR (sc.alias LIKE ’%selection%’) OR (sc.introtext LIKE ’%selection%’) OR (sc.menutitle LIKE ’%selection%’) OR (sc.content LIKE ’%selection%’) OR (tv_value LIKE ’%selection%’)) OR ((sc.pagetitle LIKE ’%vinticultural%’) OR (sc.longtitle LIKE ’%vinticultural%’) OR (sc.description LIKE ’%vinticultural%’) OR (sc.alias LIKE ’%vinticultural%’) OR (sc.introtext LIKE ’%vinticultural%’) OR (sc.menutitle LIKE ’%vinticultural%’) OR (sc.content LIKE ’%vinticultural%’) OR (tv_value LIKE ’%vinticultural%’)) OR ((sc.pagetitle LIKE ’%retail%’) OR (sc.longtitle LIKE ’%retail%’) OR (sc.description LIKE ’%retail%’) OR (sc.alias LIKE ’%retail%’) OR (sc.introtext LIKE ’%retail%’) OR (sc.menutitle LIKE ’%retail%’) OR (sc.content LIKE ’%retail%’) OR (tv_value LIKE ’%retail%’)) OR ((sc.pagetitle LIKE ’%location%’) OR (sc.longtitle LIKE ’%location%’) OR (sc.description LIKE ’%location%’) OR (sc.alias LIKE ’%location%’) OR (sc.introtext LIKE ’%location%’) OR (sc.menutitle LIKE ’%location%’) OR (sc.content LIKE ’%location%’) OR (tv_value LIKE ’%location%’)) OR ((sc.pagetitle LIKE ’%dinner%’) OR (sc.longtitle LIKE ’%dinner%’) OR (sc.description LIKE ’%dinner%’) OR (sc.alias LIKE ’%dinner%’) OR (sc.introtext LIKE ’%dinner%’) OR (sc.menutitle LIKE ’%dinner%’) OR (sc.content LIKE ’%dinner%’) OR (tv_value LIKE ’%dinner%’)) OR ((sc.pagetitle LIKE ’%party%’) OR (sc.longtitle LIKE ’%party%’) OR (sc.description LIKE ’%party%’) OR (sc.alias LIKE ’%party%’) OR (sc.introtext LIKE ’%party%’) OR (sc.menutitle LIKE ’%party%’) OR (sc.content LIKE ’%party%’) OR (tv_value LIKE ’%party%’)) OR ((sc.pagetitle LIKE ’%worry%’) OR (sc.longtitle LIKE ’%worry%’) OR (sc.description LIKE ’%worry%’) OR (sc.alias LIKE ’%worry%’) OR (sc.introtext LIKE ’%worry%’) OR (sc.menutitle LIKE ’%worry%’) OR (sc.content LIKE ’%worry%’) OR (tv_value LIKE ’%worry%’)) OR ((sc.pagetitle LIKE ’%matching%’) OR (sc.longtitle LIKE ’%matching%’) OR (sc.description LIKE ’%matching%’) OR (sc.alias LIKE ’%matching%’) OR (sc.introtext LIKE ’%matching%’) OR (sc.menutitle LIKE ’%matching%’) OR (sc.content LIKE ’%matching%’) OR (tv_value LIKE ’%matching%’))) ORDER BY sc.publishedon,sc.pagetitle

This is not good at all...there definitely needs to be a reasonable cap on the number of OR clauses being produced or in the number of terms that are used for the search.

is it normal behaviour to do a search with so many terms ?

wine food stuff chambourcin cab franc basket menu selection vinticultural retail location dinner party worry matching

there definitely needs to be a reasonable cap on the number of OR clauses being produced
reagarding the number of OR clauses produced, as we search at least in sc.pagetitle, sc.longtitle, sc.description, sc.alias, sc.introtext, sc.menutitle, sc.content, sc.publishedon we have at least 7 OR clauses for one search term with the table site_content.

But Ok I will offer a maximum search terms in the next release.
What is for you the correct number of OR clauses ?

Quote from: coroico at Oct 18, 2008, 11:32 AM

is it normal behaviour to do a search with so many terms ?

It is not normal behavior to try and attack other people’s web sites, so probably not, no. This is a very standard vector for attack by these abnormal folk though, and preventing such denial of service attempts is the goal.

Quote from: coroico at Oct 18, 2008, 11:32 AM

What is for you the correct number of OR clauses ?

Any OR clauses are too many IMO ( ), but, as they are necessary in this component, I would come up with some configurable limitations, perhaps set as a snippet parameter, with a reasonable default; maybe 5 keywords?

It definitely is not normal behavior, but creates a problem if someone is trying to overload the server. I would say a reasonable number of terms is 5. Also, it brings up whether AjaxSearch should do some other validation before querying the the database, similar to forum searches (i.e., certain amount of time must expire before running another search, query is exactly the same as the last query, etc.)