C - How to use variables as part of an SQL Query?

Question

Introduction

I'm attempting to incorporate variables into queries using C. I'm following this tutorial using sqlite tutorialspoint , and my first exposure to using SQL. The tutorial has shown me how to use Queries such as these:

Query

sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1; " \
     "SELECT * from COMPANY";

*So how would i go about incorporating variables into this statement, for example if i wanted to replace 1 with a variable assigned to 'ID'.

For example(My failed attempt)

sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=" + variable + ";" \
     "SELECT * from COMPANY";

I've googling around however I couldn't really find any material on using variables in sql queries using the C language syntax. How would i go about this in the correct and safe way, to incorporate variables and without making a program vulnereable to SQL injection?

Use prepared statements. You don't want to build an SQL string dynamically like that - it's ridiculously insecure. — John Bode
– John Bode, Commented Aug 23, 2017 at 14:29

Matt P · Accepted Answer · 2017-08-23 19:01:37Z

5

The C-API provides the functions sqlite3_prepare_v2 and sqlite3_bind so that you can bind parameters to prepared statements. What that means is, you can use a placeholder where you want to substitute parameters within a string.

Each placeholder is referenced by an index, so you can use as many parameters as you like (up to the compile-time limit set by SQLITE_MAX_VARIABLE_NUMBER). You then bind a parameter to the placeholder at a specified index.

There are a number of functions and methods to accomplish parameter substitution, but to get you started, here's an example which binds an integer to the 1st placeholder in an sql statement:

int rc;
sqlite3 *db;
sqlite3_stmt *stmt = NULL;
...
// here I assume you open the db, and provide any other working code as needed...
...
// the employee id number.
int id_num;
...

// create the sql statement, with a single placeholder marked by '?'.
char *sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=?";

// prepare the sql statement.
rc = sqlite3_prepare_v2(db, sql, strlen(sql)+1, &stmt, NULL);
if (rc != SQLITE_OK) {
    printf("Failed to prepare statement: %s\n\r", sqlite3_errstr(rc));
    sqlite3_close(db);
    return 1;
} 
else {
    printf("SQL statement prepared: OK\n\n\r");
}

// bind an integer to the parameter placeholder. 
rc = sqlite3_bind_int(stmt, 1, id_num);
if (rc != SQLITE_OK) {
    printf("Failed to bind parameter: %s\n\r", sqlite3_errstr(rc));
    sqlite3_close(db);
    return 1;
} 
else {
    printf("SQL bind integer param: OK\n\n\r");
}

// evaluate the prepared statement.
rc = sqlite3_step(stmt);
// other successful return codes are possible...
if (rc != SQLITE_DONE) {
    printf("Failed to execute statement: %s\n\r", sqlite3_errstr(rc));
    sqlite3_close(db);
    return 1;
}

// deallocate/finalize the prepared statement when you no longer need it.
// you may also place this in any error handling sections.
sqlite3_finalize(stmt);

...
// close the db when finished.
sqlite3_close(db)
...
// finish your code.

edited Aug 23, 2017 at 19:01

answered Aug 23, 2017 at 14:27

Matt P

2,4071 gold badge15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

CL. Over a year ago

Please do not omit sqlite3_finalize. All the error handling is horribly wrong.

Matt P Over a year ago

Very well, I've included sqlite3_finalize, originally omitted for brevity, perhaps a poor choice. The error handling seems sufficient for an example such as this.

CL. Over a year ago

This code closes a DB it never opened, and forgets to clean up the statement.

Matt P Over a year ago

@CL. This is not a complete production-level code, it is an excerpt to show how to use parameter substitution. I'm assuming that the OP has a handle on sqlite3_open.

Collectives™ on Stack Overflow

C - How to use variables as part of an SQL Query?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related