Search code examples
iosswiftsqlitefull-text-searchfmdb

SQLite WHERE column MATCH parameter binding

I have FTS table and query that matches all rows where column contains both "all" and "in".

try db.executeQuery("SELECT * FROM table WHERE column MATCH '\"all\" AND \"in\"'", values: nil)

How can I make this work with parameter binding? So I can provide:

values: ["all", "in"]
Solution

  • SQLite uses plain strings as full-text patterns. You thus have to build yourself one full-text pattern string from your multiple words, and provide it as one FMDB parameter, as below.

    let words = ["all", "in"]
let pattern = words
    .map { "\"\($0)\"" } // wrap each word inside quotes
    .joined(separator: " AND ")
try db.executeQuery("SELECT * FROM table WHERE column MATCH ?", values: [pattern])

    Beware that not all patterns are valid. See the Full-Text Index Queries Grammar. This means that some inputs will trigger SQLite errors. For example, if the words come from a text field, and the user types a quote, as in "attack, then you may build the invalid pattern ""attack" that SQLite won't parse (SQLite error 1: malformed MATCH expression).

    You can try to sanitize user input yourself, but this is difficult to do while preserving the maximum usable information. After all, application users are usually not aware of SQLite subtleties: there's no point punishing them with empty search results if they happen to type a funny search string.

    If you need to perform pattern sanitization, I recommend you have a look at GRDB, an alternative to FMDB with great support for full-text search. It can build safe patterns from user input:

    let userInput = textField.text
let pattern = FTS3Pattern(matchingAllTokensIn: userInput)
let rows = try Row.fetchAll(db, "SELECT * FROM table WHERE column MATCH ?", arguments: [pattern])

    When user types "attack, the FTS3Pattern(matchingAllTokensIn:) would build the sane attack FTS pattern instead of failing. GRDB uses SQLite itself to perform the sanitization, which means that it's pretty solid.

    For more information, see GRDB full-text search documentation.