Strings passed to SQLite C API are often incorrect

Using Swift
,
1

I’m baffled by what's going on here. It's 0542 and I'm exhausted, so it’s probably something stupid.

I’ve written a super-simple wrapper around SQLite. In it, I have methods to convert UUID to string, and bind it to columns as text. Here’s the thing: it always changes my UUID strings to "187178B5-C411-49E5-AE81-6D685A6C8B33", but text literals turn out okay:

try self.db.prepare(sql: "insert into Measurement (id, timestamp, sensor, temperature, furnaceID, experimentID) values(?, ?, ?, ?, ?, ?);")

try self.db.bind(idx: 1, val: UUID())
try self.db.bind(idx: 2, val: Date())
try self.db.bind(idx: 3, val: "sample")
try self.db.bind(idx: 4, val: Float.random(in: 900.0...2000.0))
try self.db.bind(idx: 5, val: UUID(uuidString: "FBD8EBC2-8806-4129-BEB6-8B7B1D5A760B")!)
try self.db.bind(idx: 6, val: UUID(uuidString: "187178B5-C411-49E5-AE81-6D685A6C8B33")!)

Swift.print("Stmt: \(self.db.stmtString)")

try self.db.step()

I have some Swift.print() statements in my wrapper, and executing the code above on an empty table, it prints out:

Binding idx 1 to text val [5237AD9B-FFDF-4BFD-8B8A-676C2846D094]
Binding idx 2 to double val [631115696.888909]
Binding idx 3 to text val [sample]
Binding idx 4 to double val [1185.1787109375]
Binding idx 5 to text val [FBD8EBC2-8806-4129-BEB6-8B7B1D5A760B]
Binding idx 6 to text val [187178B5-C411-49E5-AE81-6D685A6C8B33]
Stmt: Optional("insert into Measurement (id, timestamp, sensor, temperature, furnaceID, experimentID) values(\'187178B5-C411-49E5-AE81-6D685A6C8B33\', 631115696.888909, \'sample\', 1185.1787109375, \'187178B5-C411-49E5-AE81-6D685A6C8B33\', \'187178B5-C411-49E5-AE81-6D685A6C8B33\');")

Note the UUID values that get printed, and then how they look in the expanded SQL! But "sample" is unmodified. Both use the same code path.

If I swap the binding calls for value indexes 3 & 6 (as well as the column names in the prepare statement), then the UUID values are all "FBD8EBC2-8806-4129-BEB6-8B7B1D5A760B", and "sample" becomes the corrupted "8z?l?" when examined in the sqlite3 command line tool.

I’m just…baffled.

2

You may well have issues in your sqlite3_bind_text usage. You use nil as its last (fifth) argument. The doc says:

The fifth argument to the BLOB and string binding interfaces is a destructor used to dispose of the BLOB or string after SQLite has finished with it. The destructor is called to dispose of the BLOB or string even if the call to the bind API fails, except the destructor is not called if the third parameter is a NULL pointer or the fourth parameter is negative. If the fifth argument is the special value SQLITE_STATIC, then SQLite assumes that the information is in static, unmanaged space and does not need to be freed. If the fifth argument has the value SQLITE_TRANSIENT, then SQLite makes its own private copy of the data immediately, before the sqlite3_bind_*() routine returns.

Remember that Swift strings are not implemented as UTF8 C strings (a pointer to a 0-terminated buffer). Some flavors of Strings may be, but short strings are encoded right into the String value (no pointer to a heap-allocated buffer exists), NSStrings hold an UTF16 buffer, etc. In general, you must not assume there exists a stable buffer in memory that holds the String content in the form of the UTF8 C string expected by SQLite.

When you call sqlite3_bind_text(..., string, ...), Swift builds a convenience temporary C string, that feeds the const char * argument of the C function.

The lifetime of this temporary C string does not exceed the duration of the function call. After the function has returned, the temporary buffer is disposed, and can be reused for any other purpose. But SQLite was given a pointer to this vanished buffer, and told to use it as-is! When the SQLite statement is run with sqlite3_step, SQLite just reads garbage.

Instead, you need to instruct SQLite to perform a copy of the input string, a copy that SQLite will manage until the statement is finalized, or another value is bound.

To do this, replace nil with SQLITE_TRANSIENT in your source code:

let SQLITE_TRANSIENT = unsafeBitCast(OpaquePointer(bitPattern: -1), to: sqlite3_destructor_type.self)
let result = sqlite3_bind_text(stmt, Int32(inIdx), inVal, -1, SQLITE_TRANSIENT)
7 Likes
3

I wish I could Google for the definitive documentation about this. Nothing shows up in The Swift Programming Language: Redirect, or in Imported C and Objective-C APIs | Apple Developer Documentation. ¯\_(ツ)_/¯

2 Likes
4

Ugh, this was exactly the problem. I was basing my code off an example I saw online that used an older version of Swift. Also, the SQLite docs aren’t exactly written for skimming. I also wondered about how Swift was converting strings, but couldn't find documentation about it. If I hadn’t been so sleep deprived, I might’ve figured it out.

I’m extremely grateful you showed me how to type -1. I’m dismayed that C interop still doesn’t know how to convert macros.

5

Happy new year, Rick :-)

1 Like
6

Likewise, Gwendal!

7

I was once wondering about how exactly Swift passes String to C.

I made a simple example for myself and built it using swift build -Xswiftc -Xllvm -Xswiftc -sil-print-all > main.silsteps . Long story short :) You may find a lot of more information how Swift coverts various types to C here:

2 Likes