Escaping generated SQL correctly for MATCH clauses

[jaseemabid]

I'm using diesel with sqlite's full text search. I have a fairly simple table with schema (id, text). The query string must be quoted with an extra double quote to handle special characters.

The first example works, while the second doesn't.

sqlite> SELECT id, text from fts where text match '"@foo"';
sqlite> ✅

sqlite> SELECT id, text from fts where text match '@foo';
Error: stepping, fts5: syntax error near "@" (1)

Since diesel doesn't support MATCH out of the box, I made a new infix operator

/// Sqlite module to provide full text search helpers
mod sqlite {
    use diesel::{
        expression::{AsExpression, Expression, TypedExpressionType},
        sql_types::SqlType,
    };

    diesel::infix_operator!(Match, " MATCH ");

    // Normally you would put this on a trait instead
    pub fn match_<T, U, ST>(left: T, right: U) -> Match<T, U::Expression>
    where
        T: Expression<SqlType = ST>,
        U: AsExpression<ST>,
        ST: SqlType + TypedExpressionType,
    {
        Match::new(left, right.as_expression())
    }
}

And its used like

let query = fts.select(id).filter(match_(text, q)).order_by(rank);

Now this works fine for simple strings, but fails when the query contains a @.

Example with error returned by sqlite:

running 1 test
[src/search.rs:92] "XXX Exec {:?}" = "XXX Exec {:?}"
[src/search.rs:92] debug_query(&query) = Query {
    sql: "SELECT `fts`.`id` FROM `fts` WHERE `fts`.`text` MATCH ? ORDER BY `fts`.`rank`",
    binds: [
        "@Foo",
    ],
}
thread 'search::test::full_text_search' panicked at 'called `Result::unwrap()` on an `Err` value: DB(DatabaseError(Unknown, "fts5: syntax error near \"@\""))', src/search.rs:174:14

❓ How would you go about fixing this? 🙏

[mohe2015]

No idea but does it work if you set q to "\"@foo\""? Or are you aware of that but want a nicer solution?

[jaseemabid]

@mohe2015, unfortunately its a bit trickier than this. let quoted = format!("\"{}\"", q); wont do :(

In case of @foo, you can quote the whole expression, buy for something like @foo OR @bar, you need "@foo" OR "@bar". OR is a "bareword" in sqlite and must not be quoted by default for it to work as a logical operator.

[weiznich]

I was not able to reproduce this. For reference I've used the following code:

use diesel::{
    expression::{AsExpression, Expression, TypedExpressionType},
    sql_types::SqlType,
};

diesel::infix_operator!(Match, " MATCH ");

// Normally you would put this on a trait instead
pub fn match_<T, U, ST>(left: T, right: U) -> Match<T, U::Expression>
where
    T: Expression<SqlType = ST>,
    U: AsExpression<ST>,
    ST: SqlType + TypedExpressionType,
{
    Match::new(left, right.as_expression())
}
use diesel::prelude::*;

table! {
    fts(id) {
        id -> Integer,
        text -> Text,
        rank -> Integer,
    }
}

fn main() {
    use self::fts::dsl::*;

    let mut conn = SqliteConnection::establish(":memory:").unwrap();
    diesel::sql_query("CREATE VIRTUAL TABLE fts USING fts4(id INTEGER NOT NULL PRIMARY KEY, text TEXT NOT NULL, rank INTEGER NOT NULL);").execute(&mut conn).unwrap();
    diesel::insert_into(fts).values((id.eq(42), text.eq("foo"), rank.eq(1))).execute(&mut conn).unwrap();
    let q = "@foo";

    let query = fts.select(id).filter(match_(text, q)).order_by(rank);
    dbg!(diesel::debug_query(&query));
    let res = query.load::<i32>(&mut conn).unwrap();
    dbg!(fts.load::<(i32, String, i32)>(&mut conn));
}

[package]
name = "diesel_fst_test"
version = "0.1.0"
edition = "2021"

# See more keys and their definitions at https://doc.rust-lang.org/cargo/reference/manifest.html

[dependencies]
diesel = { version = "2.0.0-rc.0", features = ["sqlite"] }

[jaseemabid]

@weiznich Thanks a lot for trying this out.

1. I'm using fts5, not 4.
2. Version 2476a76 of diesel, I upgraded to latest master a while ago for some feature.

[jaseemabid]

For now this did the trick for me, but I'm not sure this covers all the edge cases.

    pub fn quote(q: &str) -> String {
        q.split_whitespace()
            .map(|w| {
                if w.starts_with(['#', '@']) {
                    format!("\"{}\"", w)
                } else {
                    w.to_string()
                }
            })
            .collect::<Vec<_>>()
            .join(" ")
    }

    #[test]
    fn q() {
        assert_eq!(quote("hello world"), "hello world");
        assert_eq!(quote("@A OR @B"), "\"@A\" OR \"@B\"");
    }