How to insert any string in sqlite3 in c

Question

Hi 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

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.

Answer 2

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 3

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_OK)
{
    //...
}

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