Search for exact phrases not working

[nighknight]

Hello!
The search for exact phrases using quotes ("exact phrase") is not working in the current version (2.9.9). The search strings seems to be converted to 'exact AND phrase'.

See screenshot:

The first result should not be found as it does not contain the exact search string "die Suche nicht", but only the string "die Suche mit Anführungszeichen nicht".

I did some research and this issue seems to be related to the escaping, see
https://stackoverflow.com/questions/16674139/php-mysql-boolean-full-text-searches-exact-string-operator-gives-differ

Unfortunately, my knowledge of PHP / MySQL is rather limited, so I am unable to fix the problem myself, but maybe this is helpful for you!

[thorsten]

How many entries do you have? I think the search switches back to a "LIKE" search.

[nighknight]

I got three systems running:

• one production system (~500 entries running 2.8.29)
• one test system, where I am currently testing an update to 2.9.x (same ~500 entries)
• one basic development system (3 entries, used to make the screenshots)

The issue is present on all three systems.

If I echo the resulting SQL query and paste it to phpMyAdmin, the result is ok.

Screenshot from development/phpmyfaq/inc/PMF/DB/Mysqli.php

Resulting query

SELECT fd.id AS id, fd.lang AS lang, fd.solution_id AS solution_id, faqcategoryrelations.category_id AS category_id, fd.thema AS question, fd.content AS answer FROM faqdata AS fd LEFT JOIN faqcategoryrelations ON fd.id = faqcategoryrelations.record_id AND fd.lang = faqcategoryrelations.record_lang WHERE MATCH (fd.thema, fd.content, fd.keywords) AGAINST ('"suche nicht"' IN BOOLEAN MODE) AND fd.lang = 'de' AND fd.active = 'yes' 

phpMyAdmin Output

[nighknight]

Further comment: I just realized that the relation between the search words is actually a logical "OR" and not an "AND". So "suche irgendwas" actually results in the same output.

[thorsten]

This is the search code for MySQL:

https://github.com/thorsten/phpMyFAQ/blob/2.9/phpmyfaq/inc/PMF/Search/Database/Mysqli.php#L86-L125

If the first query does not return anything, a LIKE search will be executed.

[nighknight]

Thanks for the clarification, I was not aware of that.

However, in my case two results are found and returned immediately. No LIKE search is executed, see output:

SELECT fd.id AS id, fd.lang AS lang, fd.thema AS thema, fcr.category_id AS category_id, fv.visits AS visits FROM faqdata fd LEFT JOIN faqcategoryrelations fcr ON fd.id = fcr.record_id AND fd.lang = fcr.record_lang LEFT JOIN faqvisits fv ON fd.id = fv.id AND fv.lang = fd.lang LEFT JOIN faqdata_group fdg ON fd.id = fdg.record_id LEFT JOIN faqdata_user fdu ON fd.id = fdu.record_id WHERE fd.date_start <= '20170802133021' AND fd.date_end >= '20170802133021' AND fd.active = 'yes' AND fcr.category_id = 0 AND fd.lang = 'de' GROUP BY fd.id, fd.lang, fd.thema, fcr.category_id, fv.visits ORDER BY id DESC
SELECT fd.id AS id, fd.lang AS lang, fd.solution_id AS solution_id, faqcategoryrelations.category_id AS category_id, fd.thema AS question, fd.content AS answer FROM faqdata AS fd LEFT JOIN faqcategoryrelations ON fd.id = faqcategoryrelations.record_id AND fd.lang = faqcategoryrelations.record_lang WHERE MATCH (fd.thema, fd.content, fd.keywords) AGAINST ('"suche nicht"' IN BOOLEAN MODE) AND fd.lang = 'de' AND fd.active = 'yes'
Number of results: 2
SELECT user_id AS permission FROM faqdata_user WHERE record_id = 2
SELECT user_id AS permission FROM faqdata_user WHERE record_id = 3
SELECT MAX(id) AS current_id FROM faqsearches
INSERT INTO faqsearches (id, lang, searchterm, searchdate) VALUES (62, 'de', '"suche nicht"', '2017-08-02 13:30:21')

So my problem is not connected to the LIKE search.

[nighknight]

I did some more analysis of this issue by turning on the mysql query log.

It revealed a problem with the encoding of the quotes:

SELECT
	fd.id AS id, fd.lang AS lang, fd.solution_id AS solution_id, 
	faqcategoryrelations.category_id AS category_id, 
	fd.thema AS question, fd.content AS answer
FROM
	faqdata AS fd  
		LEFT JOIN faqcategoryrelations ON  fd.id = faqcategoryrelations.record_id 
		AND fd.lang = faqcategoryrelations.record_lang
WHERE
	MATCH (fd.thema, fd.content, fd.keywords) 
		AGAINST (''die suche nicht'' IN BOOLEAN MODE)
			AND fd.lang = 'de' AND fd.active = 'yes'

So basically the quotes in the query are not shown as quotes but as html entities (').

As a test, I put a htmlspecialchars_decode() before the final call to mysql and then the query was executed correctly.

Screenshot from development/phpmyfaq/inc/PMF/DB/Mysqli.php

Resulting output

Maybe this helps?
It strongly assume that my hack does not constitute a proper fix...

Note: During the research, I found a comment that the encoding in htmlspecialchars($str, $quoteStyle, "UTF-8") is case sensitive:

Source

In development/phpmyfaq/inc/PMF/String.php it is actually lower case, but should be upper case according to the comment: