Suragch Suragch - 2 years ago 180
Swift Question

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

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

// SELECT * FROM "users" WHERE ("email" LIKE '')

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

let stringPrefix = userInput + "%"
// 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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download