Preventing SQLite Injection attacks

A quick followup on my persistent database article.

https://xkcd.com/327

It was brought to my attention by a old friend and outright smart fellow that there was a serious security vulnerability in my previous post. I described how to use SQLite on the Raspberry Pi. What I forgot to mention is that even in your home projects you are still vulnerable to one of the most common hacker exploits, the SQL Injection attack.

By definition an injection attack occurs when external data, such as user input can cause changes in code execution. For example, lets look at the case when code prompts for a username that is used in a parameter to a SQL statement:

let sql  = "SELECT * FROM users WHERE NAME ='\(username)';" 

Now let’s see what happens when the user types in the following for username

'; DROP TABLE users; -- 

Probably not our intended result.

The funny thing is that this attack has been around for quite a while with an amazing history all it’s own. As far as I can tell it was first documented by the security researcher “rain.forest.puppy” in the December 1998 issue of Phrack magazine.

So how do we make it stop?

There are a number of strategies you can apply to mitigate SQL injection attacks and rather than to list them here, I’ll just say Google is your friend and leave it as a web searching exercise for the reader. I will though address a one method that is specific to apps using the SQLite C API.

Assuming we created a table with:

CREATE TABLE IF NOT EXISTS DEMO_DATA(NAME PRIMARY KEY,VALUE);

And we use the following C++ code snippet:

string prop = "prop1";
string val = "some value";
	
string sql = string("REPLACE INTO DEMO_DATA (NAME, VALUE)")
		+ "VALUES ('" + prop + "','" + val + "');";

sqlite3_stmt* stmt = NULL;
sqlite3_prepare_v2(_sdb, sql.c_str(), -1,  &stmt, NULL);
		
sqlite3_step(stmt);

This should result in adding a table entry into DEMO_DATA

 prop1 | some value

But lets instead change the line for val to read

string val = "'; DROP TABLE users; --";

Probably not what we expected.

Note that while this is not generally a issue when we have precompiled SQL statements. The problem occurs when we have to add in external unsanitized data. To work around this we can use a technique known as Binding Values To Prepared Statements. Consider the following snippet.

string prop = "prop1";
string val = "'; DROP TABLE users; --";

string sql = string("REPLACE INTO DEMO_DATA (NAME, VALUE)")
		+ "VALUES (?,?);";

sqlite3_stmt* stmt = NULL;
sqlite3_prepare_v2(_sdb, sql.c_str(), -1, &stmt, NULL);

sqlite3_bind_text(stmt, 1, prop.c_str(), -1, SQLITE_STATIC);
sqlite3_bind_text(stmt, 2, val.c_str(), -1, SQLITE_STATIC);

sqlite3_step(stmt);

I have replaced the values for prop and val with ‘?’ character, which indicates to the sqlite3_prepare that these are parameters that we will fill in with the corresponding sqlite3_bind functions. The binding prevents execution of the parameter values, and what we end up with is a table with the following entry:

prop1 | '; DROP TABLE users; --

Somewhat better than having the code executed.

Many tanks.

So many thanks to SLH for reminding me about this. I really appreciated the feedback and have updated the sqldemoserver sample code on GitHub to be a bit more resilient to these things.