Getting Results from Arbitrary SQL Statements with Correct Binding in SQLite.Swift

Getting results from arbitrary SQL statements with correct binding in SQLite.swift

You can retrieve correctly typed selected columns from a table like this:

// The database.
let db = try Connection(...)

// The table.
let users = Table("users")

// Typed column expressions.
let id = Expression<Int64>("id")
let email = Expression<String>("email")

// The query: "SELECT id, email FROM users"
for user in try db.prepare(users.select(id, email)) {
    let id = user[id]       // Int64
    let mail = user[email]  // String
    print(id, mail)
}

An alternative is to (optionally) cast the Binding values
to the correct type:

let stmt = try db.prepare("SELECT id, email FROM users")
for row in stmt {
    if let id = row[0] as? Int64,
        let mail = row[1] as? String {
        print(id, mail)
    }
}

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

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.

Subtle cast warning when using SQLite.Swift ... Binding? to Any

You're using Array's sequence initialiser, which has the signature:

init<S>(_ s: S) where S : Sequence, Element == S.Iterator.Element

Because you typed r as [[Any]], Element is [Any]. However, the sequence you're passing in has an Iterator.Element type of [Binding?]. Therefore, you're implicitly coercing Binding? to Any, and as per SE-0140, this will invoke a warning – as you're losing the optionality of the inner elements, which is potentially undesirable.

As the proposal says, one way to silence this warning is to add an explicit as Any cast. In your case, this can be achieved by using a nested map(_:):

r = stmt.map { $0.map { $0 as Any } }

This shouldn't be any more costly than using Array's sequence initialiser due to the fact that a walk over all the inner elements will have to be done in either case, due to the difference in how Swift stores abstract-typed values (see this Q&A for more info).

However, really you should be asking yourself whether r should be of type [[Any]]. I see no reason why you shouldn't just type it as [[Binding?]]. Doing so will both get rid of the warning and give you better type-safety.

Save results from sqlite3_exec callback function into a dictionary in Swift

Following Shawn's comments, I created a new version of execute, where I using sqlite3_prepare_v2, sqlite3_step and sqlite3_finalize instead of the combined function sqlite3_exec:

func prepare(sql: String) -> OpaquePointer? {
    var statement: OpaquePointer? = nil
    guard sqlite3_prepare_v2(dbPointer, sql, -1, &statement, nil) == SQLITE_OK else {
        print(errorMessage)
        return nil
    }
    return statement
}


func execute(sql: String) -> [Dictionary<String, String>] {
    var result: [Dictionary<String, String>] = []

    guard let queryStatement = prepare(sql: sql) else {
        return result
    }

    defer {
        sqlite3_finalize(queryStatement)
    }

    while sqlite3_step(queryStatement) == SQLITE_ROW {
        var dictionary: Dictionary<String, String> = [:]

        for i in 0...sqlite3_column_count(queryStatement)-1 {
            let column: String = String(cString: sqlite3_column_name(queryStatement, i))
            let value: String = String(cString: sqlite3_column_text(queryStatement, i))
            dictionary[column] = value
        }

        result.append(dictionary)
    }

    guard sqlite3_step(queryStatement) == SQLITE_DONE else {
        print(errorMessage)
        return result
    }

    return result
}

My execute function now returns an array of dictionaries, where each dictionary represents a row for SQL statements like SELECT. For other SQL statements like INSERT, UPDATE or DELETE, where no rows will be returned, the function execute returns an empty array.

After executing a SQL statement the result can be accessed by

let result = execute(db, "SELECT id, name, age FROM Table")
print(result[i][age])      // prints the value in row i and column age

Accessing an SQLite Database in Swift

While you should probably use one of the many SQLite wrappers, if you wanted to know how to call the SQLite library yourself, you would:

1. Configure your Swift project to handle SQLite C calls. If using Xcode 9 or later, you can simply do:

   import SQLite3
   

2. Create/open database.

   let fileURL = try! FileManager.default
       .url(for: .applicationSupportDirectory, in: .userDomainMask, appropriateFor: nil, create: true)
       .appendingPathComponent("test.sqlite")
   
   // open database
   
   var db: OpaquePointer?
   guard sqlite3_open(fileURL.path, &db) == SQLITE_OK else {
       print("error opening database")
       sqlite3_close(db)
       db = nil
       return
   }
   

   Note, I know it seems weird to close the database upon failure to open, but the sqlite3_open documentation makes it explicit that we must do so to avoid leaking memory:

   Whether or not an error occurs when it is opened, resources associated with the database connection handle should be released by passing it to sqlite3_close() when it is no longer required.

3. Use sqlite3_exec to perform SQL (e.g. create table).

   if sqlite3_exec(db, "create table if not exists test (id integer primary key autoincrement, name text)", nil, nil, nil) != SQLITE_OK {
       let errmsg = String(cString: sqlite3_errmsg(db)!)
       print("error creating table: \(errmsg)")
   }
   

4. Use sqlite3_prepare_v2 to prepare SQL with ? placeholder to which we'll bind value.

   var statement: OpaquePointer?
   
   if sqlite3_prepare_v2(db, "insert into test (name) values (?)", -1, &statement, nil) != SQLITE_OK {
       let errmsg = String(cString: sqlite3_errmsg(db)!)
       print("error preparing insert: \(errmsg)")
   }
   
   if sqlite3_bind_text(statement, 1, "foo", -1, SQLITE_TRANSIENT) != SQLITE_OK {
       let errmsg = String(cString: sqlite3_errmsg(db)!)
       print("failure binding foo: \(errmsg)")
   }
   
   if sqlite3_step(statement) != SQLITE_DONE {
       let errmsg = String(cString: sqlite3_errmsg(db)!)
       print("failure inserting foo: \(errmsg)")
   }
   

   Note, that uses the SQLITE_TRANSIENT constant which can be implemented as follows:

   internal let SQLITE_STATIC = unsafeBitCast(0, to: sqlite3_destructor_type.self)
   internal let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
   

5. Reset SQL to insert another value. In this example, I'll insert a NULL value:

   if sqlite3_reset(statement) != SQLITE_OK {
       let errmsg = String(cString: sqlite3_errmsg(db)!)
       print("error resetting prepared statement: \(errmsg)")
   }
   
   if sqlite3_bind_null(statement, 1) != SQLITE_OK {
       let errmsg = String(cString: sqlite3_errmsg(db)!)
       print("failure binding null: \(errmsg)")
   }
   
   if sqlite3_step(statement) != SQLITE_DONE {
       let errmsg = String(cString: sqlite3_errmsg(db)!)
       print("failure inserting null: \(errmsg)")
   }
   

6. Finalize prepared statement to recover memory associated with that prepared statement:

   if sqlite3_finalize(statement) != SQLITE_OK {
       let errmsg = String(cString: sqlite3_errmsg(db)!)
       print("error finalizing prepared statement: \(errmsg)")
   }
   
   statement = nil
   

7. Prepare new statement for selecting values from table and loop through retrieving the values:

   if sqlite3_prepare_v2(db, "select id, name from test", -1, &statement, nil) != SQLITE_OK {
       let errmsg = String(cString: sqlite3_errmsg(db)!)
       print("error preparing select: \(errmsg)")
   }
   
   while sqlite3_step(statement) == SQLITE_ROW {
       let id = sqlite3_column_int64(statement, 0)
       print("id = \(id); ", terminator: "")
   
       if let cString = sqlite3_column_text(statement, 1) {
           let name = String(cString: cString)
           print("name = \(name)")
       } else {
           print("name not found")
       }
   }
   
   if sqlite3_finalize(statement) != SQLITE_OK {
       let errmsg = String(cString: sqlite3_errmsg(db)!)
       print("error finalizing prepared statement: \(errmsg)")
   }
   
   statement = nil
   

8. Close database:

   if sqlite3_close(db) != SQLITE_OK {
       print("error closing database")
   }
   
   db = nil
   

For Swift 2 and older versions of Xcode, see previous revisions of this answer.

---

Related Topics