SQLite Forum

string concatenation issue in prepared statement
Login

string concatenation issue in prepared statement

(1) By Benjamin G (benjamin.g) on 2021-02-02 11:12:45 [link] [source]

There seems to be a limit to the use of bounded variables mixed with string concatenation operator. With "myvar" set to B:

SELECT 'A' || :myvar || 'C'

returns 'AB'

whereas this:

SELECT printf("A%sC",:myvar)

correctly returns ABC

I couldn't find anywhere in the doc an explanation for this limitation. I'm using sqlite shipped with iOS (14), called from swift 5.3.

Full code (using swift, to be used in a XCTest) :

func testSqliteConcat() {
        var db: OpaquePointer?
        guard sqlite3_open(dbUrl.path, &db) == SQLITE_OK else {
            return XCTFail()
        }
        var statement: OpaquePointer?
        /* */
        XCTAssertEqual(
            sqlite3_prepare_v2(db, """
                    SELECT 'A' || :myvar || 'C'
                """, -1, &statement, nil
            ),

            SQLITE_OK
        )

        /* query that works
         XCTAssertEqual(
             sqlite3_prepare_v2(db, """
                     SELECT printf("A%sC",:myvar)
                 """, -1, &statement, nil
             ),

             SQLITE_OK
         )
         */
        XCTAssertEqual("B".utf8CString.withUnsafeBufferPointer { (buffer) -> Int32 in
            return sqlite3_bind_text(
                statement,
                1,
                buffer.baseAddress,
                Int32(buffer.count),
                unsafeBitCast(-1, to: sqlite3_destructor_type.self)
            )
        }, SQLITE_OK)

        while sqlite3_step(statement) == SQLITE_ROW {
        guard let cString = sqlite3_column_text(statement, 0) else {
            XCTFail("no column result")
            return
        }
            print("result: ", String(cString:cString))
        }
    }

(2) By Larry Brasfield (LarryBrasfield) on 2021-02-02 11:52:59 in reply to 1 [link] [source]

I do not grok swift or know what version of SQLite ships with iOS (14). However, I can assert that SQLite v3.33.0 handles your parameter substitution correctly and as you hoped. This fact is demonstrated by the following session screen-scrape: > sqlite3 SQLite version 3.33.0 2020-08-14 13:23:32 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .parameter set :myvar B sqlite> select 'A'||:myvar||'C'; ABC sqlite>

I also know that parameter substitution has worked correctly in most (if not all) SQLite library versions released over the last decade (if not much longer) and up to the v3.35.0 prerelease.

From that evidence, I conclude that something is going wrong with your call into sqlite3_bind_text().

(3) By Benjamin G (benjamin.g) on 2021-02-02 12:11:35 in reply to 2 [link] [source]

Hey, thanks for answering. I also suspected something would be wrong in my code, but i'm confused by the fact that the "printf" version seems to work just fine (and uses the same sqlite3_bind_text call)..

i've tried different operators (+) and types (int), and only string concatenation gives this weird result.

Any clue on what kind of problem to look for ? I'm thinking character encoding, and string nullchar termination, but once again, the fact that the printf version works just fine confuses me.

(4) By jake on 2021-02-02 12:28:13 in reply to 3 [link] [source]

I know nothing about swift, but would guess that you are binding the null terminator as part of your string.

Maybe try something like this:

        XCTAssertEqual("B".utf8CString.withUnsafeBufferPointer { (buffer) -> Int32 in
            return sqlite3_bind_text(
                statement,
                1,
                buffer.baseAddress,
                Int32(buffer.count)-1,
                unsafeBitCast(-1, to: sqlite3_destructor_type.self)
            )
        }, SQLITE_OK)

(5) By Benjamin G (benjamin.g) on 2021-02-02 12:30:21 in reply to 2 [link] [source]

For anyone stumbling upon this thread. It seems that the bug came from using buffer.count which in swift returns the total number of bytes (including the \0 character)

whereas by reading the doc, it seems that sqlite3_bind_text expects the number of bytes of the "value" (so, i assume, excluding the 0 character in the end).

It probably lead to having the null termination included in the resulting sql expression, and thus terminating the final string before its real end.

In the end, code that works is

"B".utf8CString.withUnsafeBufferPointer { (buffer) -> Int32 in
            return sqlite3_bind_text(
                statement,
                1,
                buffer.baseAddress,
                Int32(buffer.count - 1),
                unsafeBitCast(-1, to: sqlite3_destructor_type.self)
            )
        }

(6) By Benjamin G (benjamin.g) on 2021-02-02 12:31:17 in reply to 4 [link] [source]

hey, just read your post after writing mine. Thanks a lot for taking the time. That was indeed the bug !

(7) By Larry Brasfield (LarryBrasfield) on 2021-02-02 14:56:51 in reply to 5 [source]

Given the ubiquitous use of UTF-8, which cleverly avoids encodings having bytes equal to zero, relying on the NUL terminator would be simpler. As the docs say, If the fourth parameter to sqlite3_bind_text() or sqlite3_bind_text16() is negative, then the length of the string is the number of bytes up to the first zero terminator.. So, just write: return sqlite3_bind_text( statement, 1, buffer.baseAddress, Int32(-1), unsafeBitCast(-1, to: sqlite3_destructor_type.self) ) , and be done with it.