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.

Suragch · Accepted Answer · 2016-04-29 02:42:48Z

2

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.

edited Apr 29, 2016 at 2:42

Suragch

516k340 gold badges1.4k silver badges1.4k bronze badges

answered Apr 29, 2016 at 2:22

Evdzhan Mustafa

3,7351 gold badge29 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Suragch Over a year ago

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.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related