PG to_tsquery Exception：syntax error in tsquery: "What's your password policies?"

[ThreeFish-AI]

Self Checks

• This is only for bug report, if you would like to ask a question, please head to Discussions.
• I have searched for existing issues search for existing issues, including closed ones.
• I confirm that I am using English to submit this report (我已阅读并同意 Language Policy).
• [FOR CHINESE USERS] 请务必使用英文提交 Issue，否则会被关闭。谢谢！:）
• Please do not modify this template :) and fill in all the required fields.

Dify version

0.8.3

Cloud or Self Hosted

Self Hosted (Source)

Steps to reproduce

In hybrid search, when you do a full-text search with keywords in PG, if there are special characters in the keywords, such as single quotes, an exception will be thrown: syntax error in tsquery: "What's your password policies?"

✔️ Expected Behavior

Fix this bug.

❌ Actual Behavior

No response

[dosubot]

Hello, @ThreeFish-AI! I'm here to help you with any bugs, questions, or contributions you may have. Let's work together to resolve any issues you're experiencing.

The issue you're encountering is due to the presence of special characters, such as single quotes, in the full-text search query, which causes a syntax error in PostgreSQL's tsquery.

To address this, you can modify the escape_query_for_search method to handle single quotes as well. Here is an updated version of the method:

@staticmethod
def escape_query_for_search(query: str) -> str:
    return query.replace("'", "''").replace('"', '\\"')

This method will escape single quotes by doubling them, which is the correct way to handle single quotes in PostgreSQL queries.

Additionally, ensure that this method is used wherever user input is passed to the full-text search functionality. For example, in the HitTestingService class:

class HitTestingService:
    @classmethod
    def retrieve(cls, dataset: Dataset, query: str, account: Account, retrieval_model: dict, limit: int = 10) -> dict:
        # ... other code ...
        all_documents = RetrievalService.retrieve(
            retrieval_method=retrieval_model.get("search_method", "semantic_search"),
            dataset_id=dataset.id,
            query=cls.escape_query_for_search(query),
            # ... other parameters ...
        )
        # ... other code ...

    @staticmethod
    def escape_query_for_search(query: str) -> str:
        return query.replace("'", "''").replace('"', '\\"')

This should prevent the syntax error when performing full-text searches with special characters like single quotes ^[1][2].

To continue talking to Dosu, mention @dosu.