Skip to content

Escaping FTS search strings #246

@DeNeutoy

Description

@DeNeutoy

Thanks for the excellent library, it's very nice to use!

I've been building some in memory search functionality for a data annotation tool i'm making, and I got tripped up a little bit with escaping the full text search queries. First I tried using db.quote(q), which doesn't work, because sqlite FTS has it's own (separate) query syntax. You can see this happening here also:

http://search-24ways.herokuapp.com/24ways-f8f455f/articles?_search=acces%2A

I got around this by aggressively escaping quotes inside the query string like this:

        quoted = q.replace('"', '""')
        quoted = f'"{quoted}"'
        print(quoted)
        results = db["data"].search(quoted, columns=["id"])
        return [x["id"] for x in results]

This works in the sense it doesn't crash, but it also removes access to the search query syntax. Given the well specified definition, it might be possible for sqlite-utils to provide a db.quote_query(q) which would intelligently escape a query whilst leaving the syntax intact. This would be very nice!

Metadata

Metadata

Assignees

No one assigned

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions