SQL error on banned word removal

  • fidoboy

    (@fidoboy)


    3 years, 9 months ago

    If you add any word into the banned words list, then there is a SQL syntax error when the query is being built. For example you put the word “the” into the list of forbidden words. Then you write into the search something like this: “get the book”. The word “the” is being removed from there, but the query will be:

    SELECT DISTINCT (MATCH(wp_posts.post_title) AGAINST ('get+ +book' IN BOOLEAN MODE ) ...

    As you can see, there is an error because there are two + symbols and no word between them.

    I hope that it can be solved into next release soon.

    Kind regards,

Viewing 10 replies - 1 through 10 (of 10 total)
  • Plugin Author Ajay

    (@ajay)

    3 years, 9 months ago

    Thanks for flagging this. Let me take a look. I note you have boolean mode on.

    Is your exact search term “get the book” or there any + in the query?

    Thread Starter fidoboy

    (@fidoboy)

    3 years, 9 months ago

    That is the exact search terms. There is not any + symbol in my query (and I haven’t tested with it).

    Thread Starter fidoboy

    (@fidoboy)

    3 years, 9 months ago

    And yes, I have the boolean mode ON.

    Plugin Author Ajay

    (@ajay)

    3 years, 9 months ago

    I’ve filed a GitHub issue to investigate for the next version: https://github.com/WebberZone/better-search/issues/89

    Thread Starter fidoboy

    (@fidoboy)

    3 years, 9 months ago

    I’m already getting this errors on my logs even when I disabled the banned words list by making it empty. I’ve noticed that the error is caused from malformed URL calls, may be by bots or caused by links from outside my site. This crashes when the plugin tries to build the SQL search query, creating weird things like:

    Error syntax error, unexpected '+' in WordPress database for the query SELECT DISTINCT (MATCH(wp_posts.post_title) AGAINST ('Open+++door' IN BOOLEAN MODE ) * 10 ) + (MATCH(wp_posts.post_content) AGAINST ('Open+++door' IN BOOLEAN MODE ) * 1 ) as score FROM wp_posts LEFT JOIN wp_term_relationships AS bsq_tr ON (wp_posts.ID = bsq_tr.object_id) LEFT JOIN wp_term_taxonomy AS bsq_tt ON (bsq_tr.term_taxonomy_id = bsq_tt.term_taxonomy_id) LEFT JOIN wp_terms AS bsq_t ON (bsq_t.term_id = bsq_tt.term_id) LEFT JOIN wp_users AS bsq_users ON (wp_posts.post_author = bsq_users.ID) WHERE 1=1 AND (

    So I believe that you should sanitize the received URL query parameter before using it to build the SQL query.

    Plugin Author Ajay

    (@ajay)

    3 years, 9 months ago

    Are your logs showing what the exact URLs are?

    All search terms are sanitised by the plugin before it’s used in a query.

    I’m wonder if it has to do with common terms being pulled out. E.g. the in Open the door.

    What I’m really wondering are the various + in there. As the BOOLEAN mode by default just uses the same phrase but just adds IN BOOLEAN MODE.
    Can you disable this and tell me what you’re seeing?

    Also, worthwhile checking queries with Query Monitor.

    Thread Starter fidoboy

    (@fidoboy)

    3 years, 9 months ago

    I’ll try to check that using the query monitor. But about the common words being pulled out, as I’ve said before, I’ve emptied all, there is no one right now, that’s why it’s weird. Can’t understand how that queries are being executed or builded. All I know for now is that they are throwing errors in my log. But it seems that there are missing words and as a result there are all of these plus symbols.

    • This reply was modified 3 years, 9 months ago by fidoboy.
    Thread Starter fidoboy

    (@fidoboy)

    3 years, 9 months ago

    I forgot to mention something. I customized the SQL common words by creating a special table with a list and modified the configuration so it could be used with FULLTEXT search. May it be that it’s related and the MariaDb engine is removing that words from there?

    I’ve remembered right now and it has sense, but it shouldn’t affect to the plugin behavior.

    • This reply was modified 3 years, 9 months ago by fidoboy.
    Thread Starter fidoboy

    (@fidoboy)

    3 years, 9 months ago

    I’ve disabled boolean search and now the errors have disappeared.

    Plugin Author Ajay

    (@ajay)

    3 years, 9 months ago

    Thanks for confirming.

    I’ll look at this when I can. Irrespective of the stopwords changes you’ve made in MariaDB, the BOOLEAN mode should work properly.

    I’ve been able to replace the bug. This happens when the search term explicitly has the + in my testing e.g. Open+the+closed+door -> assuming the and closed are banned/removed.

    So your search term effectively has %2B instead of + in the URL.

    I don’t have quick solution yet.

Viewing 10 replies - 1 through 10 (of 10 total)

The topic ‘SQL error on banned word removal’ is closed to new replies.