sqlite3_expanded_sql() randomly overwrites the original SQL statement

(1) By anonymous on 2022-09-03 16:32:30 [link] [source]

For some reason the sqlite3_expanded_sql() randomly decides to overwrite the memory of the original SQL statement, which gives problems when this memory segment is supposed to contain the statement's tail.

const char *sql, *pzTail;

sql = "(id,name,age,address,salary) VALUES (?,?,?,?,?);";
sqlite3_stmt *stmt;
/* Prepare an SQL statement */
sqlite3_prepare_v2(db, sql, -1, &stmt, &pzTail);

// Here I do some bindings...

std::cout << pzTail << std::endl; // INSERT INTO company (id,name,age,address,salary) VALUES (?,?,?,?,?);
std::cout << sql << std::endl; // This is an empty string...

char *expanded_sql = sqlite3_expanded_sql(stmt);
sqlite3_free(expanded_sql);

std::cout << pzTail << std::endl; // ,'California',20000.0); <-- This should still be empty!!!
std::cout << sql << std::endl; // INSERT INTO company (id,name,age,address,salary) VALUES (1,'Paul',32,'California',20000.0); <-- This shouldn't be expanded!

This only seems to happen randomly for some reason? Anyone know what is going on here?

Is the original sql statement supposed to be overwritten?

(2) By anonymous on 2022-09-03 19:21:26 in reply to 1 [link] [source]

sql = "(id,name,age,address,salary) VALUES (?,?,?,?,?);";

The contents of the string is not a valid SQL statement, which means that the code you included is probably not your actual code. We can't help you if we can't see the actual code.

(3) By Larry Brasfield (larrybr) on 2022-09-03 19:30:21 in reply to 1 [link] [source]

What you seem to be guessing is happening might be a bug. Can you please post some code that exhibits the misbehavior you suspect? (I am adverse to guessing what somebody did to induce a problem.)

Thanks in advance.

(4) By anonymous on 2022-09-04 08:22:27 in reply to 2 [link] [source]

Sorry this was a typo :(
The correct sql string is supposed to be:

sql = "INSERT INTO company (id,name,age,address,salary) VALUES (?,?,?,?,?);";

(5) By anonymous on 2022-09-05 04:07:21 in reply to 1 [source]

Here's a short, complete program based on your post that doesn't exhibit the described behaviour.

If you still want help, your task is to come up with a short, complete program that does.

#include <iostream>
#include <sqlite3.h>

#define CREATE "create table company (id integer,name text,age integer,address text,salary real);"
#define INSERT "INSERT INTO company (id,name,age,address,salary) VALUES (?,?,?,?,?);"

int main(void)
{
    int status;
    sqlite3 *db = NULL;
    sqlite3_stmt *stmt = NULL;
    char const *sql = NULL, *pzTail = NULL;
    char *expanded_sql = NULL, *errmsg = NULL;

    status = sqlite3_open_v2(
        "", &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
    if (status!=SQLITE_OK) {
        std::cerr << "sqlite3_open: "
             << (db ? sqlite3_errmsg(db) : sqlite3_errstr(status))
             << std::endl;
        return 1;
    }
    status = sqlite3_exec(db, CREATE, 0, NULL, &errmsg);
    if (status!=SQLITE_OK) {
        std::cerr << "sqlite3_exec: " << errmsg << std::endl;
        return 1;
    }
    sql = INSERT;
    status = sqlite3_prepare_v2(db, sql, -1, &stmt, &pzTail);
    if (status!=SQLITE_OK) {
        std::cerr << "sqlite3_prepare: " << sqlite3_errmsg(db) << std::endl;
        return 1;
    }

    std::cout << "(before)" << std::endl;
    std::cout << "sql = " << sql << std::endl;
    std::cout << "pzTail = " << pzTail << std::endl << std::endl;

    sqlite3_bind_int(stmt, 1, 1);
    sqlite3_bind_text(stmt, 2, "Paul", -1, SQLITE_STATIC);
    sqlite3_bind_int(stmt, 3, 32);
    sqlite3_bind_text(stmt, 4, "California", -1, SQLITE_STATIC);
    sqlite3_bind_double(stmt, 5, 20000.0);

    expanded_sql = sqlite3_expanded_sql(stmt);
    std::cout << "expanded_sql = " << expanded_sql << std::endl << std::endl;
    sqlite3_free(expanded_sql);
    expanded_sql = NULL;

    std::cout << "(after)" << std::endl;
    std::cout << "sql = " << sql << std::endl;
    std::cout << "pzTail = " << pzTail << std::endl << std::endl;

    return 0;
}

/* expected output:
(before)
sql = INSERT INTO company (id,name,age,address,salary) VALUES (?,?,?,?,?);
pzTail = 

expanded_sql = INSERT INTO company (id,name,age,address,salary) VALUES (1,'Paul',32,'California',20000.0);

(after)
sql = INSERT INTO company (id,name,age,address,salary) VALUES (?,?,?,?,?);
pzTail = 

*/