14

Given the statement:

const char *sql = "INSERT INTO FooTable (barStr) VALUES (?)";

is the following use of sqlite3_bind_text (and related sqlite3_bind_* functions) sufficient to prevent SQL injection attacks?

sqlite3 *db; 
sqlite3_stmt *dbps;

int dbrc = sqlite3_open([dbFilePath UTF8String], &db); 
if (dbrc) { 
    // handle error
    return; 
} 

dbrc = sqlite3_prepare_v2 (db, sql, -1, &dbps, NULL);

sqlite3_bind_text(dbps, 1, [userContent UTF8String], -1, SQLITE_TRANSIENT);

dbrc = sqlite3_step(dbps);
if (SQLITE_DONE != dbrc) {
    // handle error
}

sqlite3_finalize (dbps); 
sqlite3_close(db);
Bradley Dwyer
  • 8,102
  • 5
  • 32
  • 28

2 Answers2

13

Yes, if you only pass the user supplied data to sqlite3_bind_* functions, then you are safe from SQL injection attacks (these attacks assume that you dynamically build your query string and don't quote/escape the user supplied data correctly).

cmeerw
  • 7,176
  • 33
  • 27
  • 3
    Can you point me to some documentation that confirms that the `sqlite3_bind_text` function is safe from SQL injection attacks? I haven't found any hint in the documentation that binding would filter some characters? (http://www.sqlite.org/c3ref/bind_blob.html) – Bruno Bieri Jun 09 '17 at 15:08
0

This answer tells the same process before a SQL server runs a SQL query.

sqlite3_prepare_v2 should compile the SQL query into some kind of AST, with placeholders to be replaced by bound data(integers, float numbers, texts, dates, etc.).

And since the query has already been compiled into AST, the structure of the query is determined before data binding. When binding data, the SQL server considers user content as plain data, and just replace the placeholder AST node with it. As a result, the SQL will be executed as programmers expected.

karlbsm
  • 347
  • 4
  • 9
  • This answer is my assumption and has no direct source. (though I am pretty sure it should be right). If you find any mistakes or have any useful information, please let me know. – karlbsm Dec 04 '20 at 11:13