SQLite SQLITE_MISUSE error in Swift 3

Question

I´m trying to create a function that injects queries to SQLite in Swift 3 but I´m having problems with the escaping chars

class SQLiteQueryManager {

    static func insertNewStore(StoreId: Int64, Name: String, Address: String) -> Bool {

        let vCommand = "INSERT INTO Store (Id, StoreId, Name, Address) VALUES  (\(SQLiteConnectionManager.nextID("Store")),\(StoreId),'\(Name)','\(Address)')"

        return SQLiteConnectionManager.insertDatabase(vCommand)
    }

}

The problem ins that when I´m trying to execute SQLiteConnectionManager.insertDatabase function the string that I´m sending to SQLite looks like this:

INSERT INTO Store (Id, StoreId, Name, Address) VALUES (1,1,\'Tienda 1\',\'Dirección 1\')

And SQLite is rejecting the query.

I have tried .replacingOccurrences(of: "\", with: "") but it dose not work.

I have tested in DB Browser the query and works

INSERT INTO Store (Id, StoreId, Name, Address) VALUES (1,1,'Tienda 1','Dirección 1')

How can I remove the \??

My SQLite function is this:

static func insertDatabase(_ pCommand: String) -> Bool
{
    var vInsertStatement: OpaquePointer? = nil

    let vDB: OpaquePointer = SQLiteConnectionManager.openDatabase()

    var vReturn: Bool

    if sqlite3_prepare_v2(vDB, pCommand.replacingOccurrences(of: "\\", with: ""), -1, &vInsertStatement, nil) == SQLITE_OK {
        if sqlite3_step(vInsertStatement) == SQLITE_DONE {
            print("insertDatabase() correct with statement \(pCommand)")
            vReturn = true
        } else {
            print("insertDatabase() fail with statement \(pCommand)")
            vReturn = false
        }
    } else {
        print("insertDatabase() pCommand could not be prepared")
        vReturn = false
    }

    sqlite3_finalize(vInsertStatement)

    sqlite3_close(vDB)

    return vReturn
}

The Open function return ok so my guess is the escaping char or something like that.

This is the print output of the function:

insertDatabase() fail with statement INSERT INTO Store (Id, StoreId, Name, Address) VALUES (1,1,'Tienda 1','Dirección 1')

UPDATE 1:

sqlite3_step(vInsertStatement) is returning SQLITE_MISUSE but I can't find the mistake, the DB is in the bundle and the open() statement work and a select I'm doing works, what can be wrong?

UPDATE 2:

This is how I open de DB and returns OK:

private static func openDatabase() -> OpaquePointer {
    var vDB: OpaquePointer? = nil
    mDBURL = Bundle.main.url(forResource: "ARDB", withExtension: "db")
    if let vDBURL = mDBURL{
        if sqlite3_open(vDBURL.absoluteString, &vDB) == SQLITE_OK {
            print("The database is open.")
        } else {
            print("Unable to open database in method openDatabase().")
        }
        return vDB!
    } else {
        return vDB!
    }
}

Then I run this to get the last Id and works:

static func nextID(_ pTableName: String!) -> Int
{
    var vGetIdStatement: OpaquePointer? = nil

    let vDB: OpaquePointer = SQLiteConnectionManager.openDatabase()

    let vCommand = String(format: "SELECT Id FROM %@ ORDER BY Id DESC LIMIT 1", pTableName)

    var vResult: Int32? = 0

    if sqlite3_prepare_v2(vDB, vCommand, -1, &vGetIdStatement, nil) == SQLITE_OK {
        if sqlite3_step(vGetIdStatement) == SQLITE_ROW {
            vResult = sqlite3_column_int(vGetIdStatement, 0)
            print("nextID() correct with statement \(vCommand)")
        } else {
            print("nextID() fail with statement \(vCommand)")
        }

    } else {
        print("nextID() statement could not be prepared")
    }

    sqlite3_finalize(vGetIdStatement)

    sqlite3_close(vDB)

    var id: Int = 1
    if (vResult != nil)
    {
        id = Int(vResult!) + 1
    }

    return id
}

I have change my insert function to this with or without cString statement:

static func insertDatabase(_ pCommand: String) -> Bool
{
    var vInsertStatement: OpaquePointer? = nil

    let vDB: OpaquePointer = SQLiteConnectionManager.openDatabase()

    var vReturn: Bool

    if sqlite3_prepare_v2(vDB, pCommand.cString(using: .utf8), -1, &vInsertStatement, nil) == SQLITE_OK {
        if sqlite3_step(vInsertStatement) == SQLITE_DONE {
            print("insertDatabase() correct with statement \(pCommand)")
            vReturn = true
        } else {
            print("insertDatabase() fail with statement \(pCommand) with error: \(sqlite3_step(vInsertStatement)) : \(sqlite3_errmsg(vDB))")
            vReturn = false
        }
    } else {
        print("insertDatabase() \(pCommand) could not be prepared with error: \(sqlite3_prepare_v2(vDB, pCommand.cString(using: .utf8), -1, &vInsertStatement, nil))")
        vReturn = false
    }

    sqlite3_finalize(vInsertStatement)

    sqlite3_close(vDB)

    return vReturn
}

If I print the sqlite3_errmsg(vDB) in the console I get this that does not help:

▿ Optional> ▿ some : 0x0000000105347e80 - pointerValue : 4382293632

If I print sqlite3_step(vInsertStatement) returns 21 SQLITE_MISUSE

Any help will be appreciated.

Thanks in advance.

Try using binding instead of interpolating the strings directly. It's good practice for security reasons anyway. — John Montgomery
– John Montgomery, Commented Apr 19, 2017 at 17:36
I agree with John Montgomery: you are not supposed to escape anything. SQLite statements can be fed with values without any need for any escaping: that's "statement bindings". Now you could also use a robust Swift library like github.com/groue/GRDB.swift: try db.execute("INSERT ... VALUES (?,...)", arguments: [value, ...]) — Gwendal Roué
– Gwendal Roué, Commented Apr 20, 2017 at 6:27
@GwendalRoué Hi sqlite3_step(vInsertStatement) is returning SQLITE_MISUSE but I can't find the mistake, the DB is in the bundle and the open() statement work and a select I'm doing works, what can be wrong? — Stornu2
– Stornu2, Commented Apr 20, 2017 at 7:14

Stornu2 · Accepted Answer · 2017-04-20 09:32:59Z

I just found the problem, we don´t have permission of writing in the main bundle.

So you have to copy the DB first, here is the code:

private static func copyFromBundle () {
    let vBundlePath = Bundle.main.path(forResource: "ARDB", ofType: ".db")
    let vDestPath = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).first!
    let vFileManager = FileManager.default
    let vFullDestPath = URL(fileURLWithPath: vDestPath).appendingPathComponent("ARDB.db")
    mDocumentsPath = ""
    if vFileManager.fileExists(atPath: vFullDestPath.path){
        print("Database file exist don´t copy.")
        mDocumentsPath = vFullDestPath.path
    } else {
        if vFileManager.fileExists(atPath: vBundlePath!) {
            do {
                try vFileManager.copyItem(atPath: vBundlePath!, toPath: vFullDestPath.path)
                print("Database file does´t exist copy it.")
                mDocumentsPath = vFullDestPath.path
            } catch {
                print("copyFromBundle() fail with error: ",error)
            }
        }
    }
}

Thanks for the help.

Happy coding.

Collectives™ on Stack Overflow

SQLite SQLITE_MISUSE error in Swift 3

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related