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

Question

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.

score 2 · Accepted Answer · edited Apr 29 '16 at 02:42

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.

Perhaps this is a separate question, but I'm not really sure what other internal tests I should be doing if parameter binding is already taken care of. — Suragch, Apr 29 '16 at 02:42

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

1 Answers1