Suragch Suragch - 7 months ago 63
Swift Question

Correct variable binding and avoiding SQL injection with SQLite.swift queries

The SQLite.swift documentation for filtered queries gives this example:

users.filter(email.like("%@mac.com"))
// SELECT * FROM "users" WHERE ("email" LIKE '%@mac.com')


Since I want to search the database based on user input, I guess I could do the following:

let stringPrefix = userInput + "%"
users.filter(email.like(stringPrefix))
// SELECT * FROM "users" WHERE ("email" LIKE 'johndoe%')


Am I going about this the right way? With other SQLite environments in the past I have used variable binding with
?
to avoid SQL injection. Is this done behind the scenes with SQLite.swift? I didn't see any information in the documentation except for a little bit about binding in the Executing Arbitrary SQL section.

Answer

Taken from this source file :

@warn_unused_result public func like(pattern: String, escape character: Character? = nil) -> Expression<Bool> {
    guard let character = character else {
        return "LIKE".infix(self, pattern)
    }
    return Expression("(\(template) LIKE ? ESCAPE ?)", bindings + [pattern, String(character)])
}

This is just one of the overloads of the like function. The other overload looks quite similar, and does indeed also use parameter binding. Have a stroll in the source code to verify this yourself.

However, I would expect for you to have you internal tests to verify that SQLite injections are not possible.

Comments