Last argument overrides previous arguments in SQLite binding

Question

I know it is probably a very stupid mistake, but I've been literally sitting for hours staring on a few lines of code, running through documentations, and I am still clueless why this happens:

I am writing a convenience wrapper class for the SQLite C API in C++11. When connection to a database is established, the function sql prepares a statement, binds arguments to it if necessary, and executes it afterwards. Preparing and executing works fine, binding one argument works fine, but if I want to bind more than one argument, all values will always be set to the last argument in the list.

The sql function looks like this:

template <typename... Args>
bool sql(const std::string &query, const Args &... args)
{
    sql_statement call;
    if(sqlite3_prepare_v2(database_, query.data(), -1, &call.statement, nullptr)
       != SQLITE_OK)
    {
        return false;
    }

    if(!bind(call.statement, 1, args...)) {
        return false;
    }

    return sqlite3_step(call.statement) == SQLITE_DONE;
}

sql_statement is a wrapper struct for sqlite3_stmt that does nothing but free the statement when it runs out of scope. database_ is the handle of the working database. I'm not sure though, if this function even has anything to do with the problem. What I rather have in mind as the troublemaker is the bind function that is called within the sql function. Here it is:

template <typename T, typename... Args>
bool bind(sqlite3_stmt *statement, int current, const T &first, const Args &... args)
{
    std::stringstream ss;
    ss << first;
    if(sqlite3_bind_text(statement, current,
        ss.str().data(), ss.str().length(), SQLITE_STATIC) != SQLITE_OK)
    {
        return false;
    }
    return bind(statement, current+1, args...);
}
bool bind(sqlite3_stmt *, int) { return true; }

Note that there is no error, the function returns true. I have debugged this with various inputs; consider this example:

if(!db.sql("INSERT INTO test (name, age) VALUES (?, ?);", "nijansen", 23)) {
    std::cerr << db.error() << std::endl;
    return 1;
}

When I debug the function call of bind, the recursion works as expected, so it passes the function the values [1] nijansen and [2] 23. The documentation of the SQLite C API says "The leftmost SQL parameter has an index of 1", so that should not be the problem. Still, in the database the result of the query is: id: 1, name: 23, age: 23. I would really appreciate a second look on this issue.

Answer 1

Unless SQLITE_TRANSIENT is passed for the fifth argument, sqlite3_bind_text does not make a copy of the string you pass in and your string is expected to live until the statement is executed. In your case, the string is destroyed immediately after sqlite3_bind_text is invoked, and you're merely witnessing one possible manifestation of undefined behavior here.

If you want sqlite3_bind_text to manage the data's lifetime on its own, change SQLITE_STATIC to SQLITE_TRANSIENT.