

![Hero](/images/escape-fts-queries/hero.png)

[Full Text Search](https://www.sqlite.org/fts3.html) (_FTS_) is a powerful feature of SQLite, but its intricacies sometimes make it a footgun.

> **Footgun** (_noun_) <sup>[_source_](https://en.wiktionary.org/wiki/footgun)</sup>
>
> _Any feature whose addition to a product results in the user shooting themselves in the foot._

Say you have a database of SpaceX launches, and you want to use FTS on it to power the search experience in an application. You create the FTS table, you build a nice UI around it, and it works perfectly. _Super easy!_

Then you notice whenever you search for anything that starts with a dash/minus-sign (`-`), your application crashes with an error like this:

```
android.database.sqlite.SQLiteException: malformed MATCH expression: [*-6*] (code 1 SQLITE_ERROR)
        at android.database.sqlite.SQLiteConnection.nativeExecuteForCursorWindow(Native Method)
        at android.database.sqlite.SQLiteConnection.executeForCursorWindow(SQLiteConnection.java:942)
        at android.database.sqlite.SQLiteSession.executeForCursorWindow(SQLiteSession.java:838)
        at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:62)
        at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:153)
        at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:140)
```

In this case, the search query was _'-6'_ to search for the [Starlink-6](https://www.rocketlaunch.live/launch/starlink-6) launch.

This happened to me recently. Upon investigation, I realized that I had shot myself in the foot with FTS because of its [Set Operations](https://www.sqlite.org/fts3.html#set_operations_using_the_standard_query_syntax) feature.

## FTS Set Operations

Set Operations allow users to combine the results of multiple FTS queries in different ways. Essentially, they allow you to use logical `AND`, `OR` and `NOT` operators in search queries.

Set Operations treat the unary minus symbol `-` as the logical `NOT` operator. When used correctly, it can be used to retrieve the complement result set (i.e., all results _minus_ the results for the given query). Additionally, any queries prefixed with `-` are forbidden, and SQLite throws an error when it encounters one (which explains the earlier crash).

Let's say you want to search for all Starlink launches, except the ones which contain _'8'_ in their name. You can accomplish this by simply prefixing the unwanted term with a `-`. The search query then becomes _'Starlink -8'_.

_...But wait!_ This is also the exact query you would to search for the [Starlink-8](https://www.rocketlaunch.live/launch/starlink-8) launch! But FTS interprets it completely differently!

To fix this problem, you must escape such characters in the search query.

## Escaping search queries

The simplest way to escape special characters in the user's search queries is to enclose them in double quotes. This ensures that SQLite treats the search query literally.

```kotlin
fun search(query: String) {
  db.search("\"$query\"") // Starlink-8 -> "Starlink-8"
}
```

Double quotes ensure that SQLite treats the search query literally.

Unfortunately, it's not enough. This code breaks if the input query contains double quotes too.

## Escaping search queries even more

SQLite's documentation clearly defines [verbatim strings for FTS5](https://sqlite.org/fts5.html#fts5_strings), and through experimentation I can confirm that the definition applies to FTS3 and FTS4 too.

According to the docs, any `"` in the query should be replaced with `""`, and then the whole thing must be wrapped in double quotes [^1]:

```kotlin
fun search(query: String) {
  val escapedQuery = query.replace(Regex.fromLiteral("\""), "\"\"")
  db.search("\"$escapedQuery\"") // Starlink"-8 -> "Starlink""-8"
}
```

This solution seems to work for most cases. I shall update this post if I discover any queries that break this code.

Search query manipulation can still be performed outside the quotes. For example, to match the query when it's in the middle of a word, surround it asterisks:

```kotlin
fun search(query: String) {
  val escapedQuery = query.replace(Regex.fromLiteral("\""), "\"\"")
  db.search("*\"$escapedQuery\"*") // Starlink"-8 -> *"Starlink""-8"*
}
```

---

Given the complexity of sanitizing user input, it makes more sense to expose Set Operations related functionality of FTS through controllable UI elements (such as buttons and toggles), rather than an uncontrolled text field.

In conclusion, always sanitize user input and escape your FTS search queries.

If you want learn more about the Full Text Search feature in Room, check out the tutorial I did for raywenderlich.com: [Introduction to Full Text Search in Room](https://www.raywenderlich.com/14292824-introduction-to-full-text-search-in-room).

---

_Question, comments or feedback? Feel free to reach out to me on Twitter [@haroldadmin](https://twitter.com/haroldadmin)_

[^1]:
    Here's a handy extension function to perform this sanitization on a String:
    `fun String.sanitize(): String = "\"${replace(Regex.fromLiteral("\""), "\"\"")\""`
    You can [try it here](https://pl.kotl.in/unazGaBQm).

---


Performing proper input sanitization on FTS search queries in SQLite

# Full Text Search