How to insert any string in sqlite3 in c

Question

I have to insert a string into a sqlite data base my command ..

Err=sqlite_exec(DB, "create table tbl5(TEXT varchar(100));", xCallback, (void*)"First Test", &ErrMsg);

Err=sqlite_exec(DB, "insert into tbl5 values  ('some string');", xCallback, (void*)"First Test", &ErrMsg); 

works fine but when I want to put s="some string" ie

Err=sqlite_exec(DB, "insert into tbl5 values  (s);", xCallback, (void*)"First Test", &ErrMsg); 

then this is not working so how to add variable then It is not working so how to insert variable in sqlite database thank u

Answer 1

Don't use sprintf() but sqlite3_mprintf(). Here is the documentation.

char s[20] = "some string";
char* query = sqlite3_mprintf("insert into tbl5 values ('%q');", s);

Otherwise you have a risk of SQL injection.

The resulting query string should be freed using sqlite3_free().

Also note the '%q' instead of the usual '%s'.

Answer 2

Other than the suggestions already given, you can also use prepared statements with bound parameters (this is also useful if you intend to repeat the statement several times with different parameters). see the sqlite3_prepare_v2 and sqlite3_bind_* for more information

sqlite3_stmt *stmt;

// Create a prepared statement.
Err = sqlite3_prepare_v2(DB, "insert into tbl5 values (?)", -1, &stmt, NULL);
if (Err != SQLITE_OK)
{
    //...
}

// Bind our string to the statement.
Err = sqlite3_bind_text(stmt, 1, "some string", -1, SQLITE_TRANSIENT);
if (Err != SQLITE_OK)
{
    //...
}

// Execute the statement.
Err = sqlite3_step(stmt);
if (Err != SQLITE_DONE)
{
    //...
}

// Free the prepared statement.
Err = sqlite3_finalize(stmt);

Answer 3

You could use sprintf to create a formatted string.

char s[20] = "some string";
char query[100];
sprintf(query, "insert into tbl5 values (%s);", s);

It's up to you to make sure query is big enough.