How to escape colon character in search string?

Question

I have the following query, which works perfectly as long as there is no colon : character in the search query.

search_query =  "http:"
with Session() as session:
    search_query_stmt = text(
        """
        SELECT count(rowid) as count
        FROM post_fts
        WHERE post_fts=:search_string
        """
    )
    count = session.execute(
        search_query_stmt, {"search_string": search_query}
    ).scalar_one()

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: http
[SQL: 
                SELECT count(rowid) as count
                FROM post_fts
                WHERE post_fts=?
                ]
[parameters: ('http:',)]

I have tried the following as per SQLAlchemy documentation

search_query =  "http:"
search_query = search_query.replace(':',  r'\:')

Which gives the following error

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) fts5: syntax error near "\"
[SQL: 
                SELECT count(rowid) as count
                FROM post_fts
                WHERE post_fts match ?;
                ]
[parameters: ('http\\:',)]

`http:` as the value of `search_query` works for me without error. The error `no such column: http` suggests that the value is not being escaped, which doesn't match your code. — snakecharmerb, Apr 17 '22 at 07:51
@snakecharmerb I have edited the question to the bare minimum required. Basically I am stuck between 2 errors - one without escaping and the other one while trying to escape the colon. — dCoder, Apr 17 '22 at 10:36

dCoder · Answer 1 · 2022-04-18T05:47:24.810

Got a solution after I got an error for another character (hyphen -). The issue was that I was not querying any normal table or view. I was querying a SQLite FTS5 index.

Searching if anyone faced a similar error using FTS5 index, led me to this question.

You cannot simply use any random string with FTS

Read the SQLite FTS5 Docs again and found this:

Within an FTS expression a string may be specified in one of two ways:

By enclosing it in double quotes ("). Within a string, any embedded double quote characters may be escaped SQL-style - by adding a second double-quote character.

...

My current working solution is this:

search_query = '"' + search_query.replace('"', '""') + '"'

How to escape colon character in search string?

1 Answers1