How can I set a variable placeholder inside a String in C

Question

How can I set a variable placeholder inside a String in C?

For example, set ID from a variable int id = 1234.

sql = "INSERT INTO REV_ENTITY (ID, NAME, AGE, ADDRESS, SALARY)" \
          "VALUES (99, 'John Doe', 25, 'Rich-Mond ', 65000.00 );";

UPDATE

I would like to have a final String that has the variable value in it.

This, as suggested doesn't work:

sql = "INSERT INTO REV_ENTITY (ID,NAME,AGE,ADDRESS,SALARY)" \  
    "VALUES (%d, 'REV', 25, 'Rich-Mond ', 65000.00 );";  

rc = sqlite3_exec(db, printf(sql, 999), callback, 0, &zErrMsg);

I want something like this in Java:

String string = String.format("A string %s", aVariable);

`snprintf`? Or use the database-specific API variable binding (which just about *all* database API's have)? — Some programmer dude, Apr 25 '18 at 07:53
Take a look at [SQLite prepared statements](https://www.sqlite.org/c3ref/stmt.html). Prepare a statement then bind the parameters. Building your own sql in runtime is a bad idea that can lead to SQL injection vulnerabilities. — interjay, Apr 25 '18 at 08:15
Possible duplicate from [this question](https://stackoverflow.com/questions/7629501/how-to-sanitize-sql-queries-in-c?lq=1). — Edouard Thiel, Apr 25 '18 at 08:21
Not exactly (it's about mysql). Couldn't someone who used sqlite before write a *good* answer here please? ;) — , Apr 25 '18 at 08:23
Possible duplicate of [C - How to use variables as part of an SQL Query?](https://stackoverflow.com/questions/45840798/c-how-to-use-variables-as-part-of-an-sql-query) — Matt P, Apr 25 '18 at 20:12

medalib · Accepted Answer · 2018-04-25T17:43:24.173

Besides snprintf mentioned in the other answer, you can use the char *sqlite3_mprintf(const char*,...) function from the sqlite3 API. It uses the sqlite printf built-in function and it allocates memory for the string using sqlite3_malloc64(). If everything goes well, it returns a pointer to the string , otherwise it returns NULL:

int id = 999;
char *sql;
sql = sqlite3_mprintf("INSERT INTO REV_ENTITY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (%d, 'REV', 25, 'Rich-Mond ', 65000.00 )", id);
if (sql != NULL) {
    rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
    if (rc != SQLITE3_OK)
        /* Do some error handling. */
    sqlite3_free(sql);
}

Unlike printf functions family, sqlite3_mprintf does not have the luxury to report if the format is not correlated with the arguments. So, if it happens that you use GCC compiler, adding the following code can be useful:

extern char *sqlite3_mprintf(const char *format, ...) __attribute__ ((format (printf, 1, 2)));

The other solution recommended in comments, is to use the sqlite3 prepare, step and finalize functions:

int id = 999;
sqlite3_stmt *stmt = NULL;
char *sql = "INSERT INTO REV_ENTITY (ID,NAME,AGE,ADDRESS,SALARY) " \
            " VALUES (?, 'REV', 25, 'Rich-Mond ', 65000.00 )";
sqlite3_prepare(db, sql, strlen(sql), &stmt, NULL);
/* Bind id. */
sqlite3_bind_int(stmt, 1, id);
if (sqlite3_step(stmt) == SQLITE_DONE) {
    printf("Insertion success\n");
} else {
    fprintf(stderr, "Insertion error\n");
}
/* Finalize and destroy statement. */
sqlite3_finalize(stmt);

bestestefan · Answer 2 · 2018-04-25T08:13:26.947

-1

use snprintf, pseudocode looks like this:

int idValue = 1234;
snprintf(buffer, bufferLength, "insert bla bla VALUES (%d, 'John Doe', 25, 'Rich-Mond ', 65000.00 )", idValue);
sqli_execute(buffer);

in your case it will look like:

//initialize sql variable before sprintfing into it
snprintf(sql, maximumSqlBufferLength "INSERT INTO REV_ENTITY (ID, NAME, AGE, ADDRESS, SALARY) VALUES (%d, 'John Doe', 25, 'Rich-Mond ', 65000.00 );", id);

edited Apr 25 '18 at 08:13

answered Apr 25 '18 at 08:09

bestestefan

852
6
20

1

Generally speaking avoid `sprintf`, prefer `snprintf` to avoid buffer overflow. – Edouard Thiel Apr 25 '18 at 08:12
1

I don't know the sqlite API, but I strongly assume it has parametrized / prepared statements. In this case, your answer is really bad advice. – Apr 25 '18 at 08:13
@FelixPalmen it's exactly what OP asked for - he didn't ask for parametrized queries, also by example given by him I can assume he wanted way to insert value of variable on given position in string – bestestefan Apr 25 '18 at 08:14
1

just googled it: https://www.sqlite.org/c3ref/bind_blob.html --- **never** use your own "sql string building" if you have a decent API for binding parameters. SQL injections are just around the corner! – Apr 25 '18 at 08:15
@FelixPalmen that's true, but look at OPs example, he clearly wants sprintf-like function ```I want something like this in Java: String string = String.format("A string %s", aVariable);``` – bestestefan Apr 25 '18 at 08:16
1

A good answer should give good advice. This is bad advice, IMHO. OPs java code of course is just as bad. – Apr 25 '18 at 08:17

How can I set a variable placeholder inside a String in C

2 Answers2