How to avoid special characters when using ServiceStack OrmLite with SQLite?

Question

We have a piece of code in which we are trying to match a template with data in a database.

We use ServiceStack.OrmLite for our SQLite database.

So, for example, taking into account the following entries:

ColA    ColB
-----   ---------
ABC     ABC_Long
GHI     GHI_Long
GHIP    GHIP_Long

We use the predicate as:

var result = db.Select(x => x.ColA.StartsWith("GHI_")); 

Everything works fine until we have a search pattern that includes a special SQL character, such as "%" or "_", for example, given the search pattern "GHI _":

The expected line should be:

GHI     GHI_Long

However, we get:

GHI     GHI_Long
GHIP    GHIP_Long

Due to the fact that ORMLite does not escape from the special character and generates below SQL:

SELECT * FROM myTable WHERE UPPER(colA) LIKE 'GHI_%' OR UPPER(colB) LIKE 'GHI_%';

Instead of a properly shielded version, which should be:

SELECT * FROM myTable WHERE UPPER(colA) LIKE 'GHI\_%' OR UPPER(colB) LIKE 'GHI\_%' ESCAPE '\';

Can you come up with a way to solve this problem?

Answer 1

, , LIKE, StartsWith, EndsWith Contains, :

using (var db = OpenDbConnection())
{
    db.DropAndCreateTable<Poco>();

    db.Insert(new Poco { Name = "a" });
    db.Insert(new Poco { Name = "ab" });
    db.Insert(new Poco { Name = "a_c" });
    db.Insert(new Poco { Name = "a_cd" });
    db.Insert(new Poco { Name = "abcd" });
    db.Insert(new Poco { Name = "a%" });
    db.Insert(new Poco { Name = "a%b" });
    db.Insert(new Poco { Name = "a%bc" });
    db.Insert(new Poco { Name = "a\\" });
    db.Insert(new Poco { Name = "a\\b" });
    db.Insert(new Poco { Name = "a\\bc" });

    Assert.That(db.Count<Poco>(q => q.Name == "a_"), Is.EqualTo(0));
    Assert.That(db.Count<Poco>(q => q.Name.StartsWith("a_")), Is.EqualTo(2));
    Assert.That(db.Count<Poco>(q => q.Name.StartsWith("a%")), Is.EqualTo(3));
    Assert.That(db.Count<Poco>(q => q.Name.StartsWith("a_c")), Is.EqualTo(2));
    Assert.That(db.Count<Poco>(q => q.Name.StartsWith("a\\")), Is.EqualTo(3));
    Assert.That(db.Count<Poco>(q => q.Name.StartsWith("a\\b")), Is.EqualTo(2));
}

v4.0.19, MyGet.

Answer 2

OrmLite , SQL, . - :

var notes = db.Select<NoteDto>(" upper(\"NoteText\") like 'GHI@_%' ESCAPE '@' ");

, where, - DTO.

escape- , SQLite.