Raspberry Pi Internet of Things - part 16

Preventing injection attacks to the database

https://xkcd.com/327

In part 15 of this series, I discussed how I use a SQLite database to track sensor history. Although I have been successfully using the SQLite library in my projects for some time, I only realized after my last article that I had left the app vulnerable to one of the most common hacker exploits, the SQL Injection attack. What makes this more frustrating is that I even authored an article on this very problem three years ago. I guess I forgot.

This attack has been around for quite a while with an amazing history all its 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.

By definition, an injection attack occurs when external data, such as user input can cause changes in code execution.

For our example, let’s use the DEVICE_DATA table that I described in the last article. I described how we can use the REST API to retrieve historical data on one of the sensors.

GET /history/RAIN_SENSOR

This REST request gets turned into the following SQL query.

select * from DEVICE_DATA WHERE NAME = 'RAIN_SENSOR';

But let’s imagine that a malicious attacker does something,

GET /history/%27%3B%20DROP%20TABLE%20DEVICE_DATA%3B%20--%20%0A

which decodes to a variable name of:

'; DROP TABLE DEVICE_DATA; -- 

Thus, the following SQL gets executed:

select * from DEVICE_DATA WHERE NAME = ''; DROP TABLE DEVICE_DATA; --;

How do you prevent this?

Bad guys are gonna do bad-guy stuff. In reality, preventing these SQL injection attacks requires implementing multiple security layers.

But let’s focus on the method we can apply in our code: precompiled SQL statements and parameterized queries. Simply put, the best strategy is to prevent strings outside our code from entering the SQL system.

But this is not always practical; sometimes, we need to form a query with external unsanitized data. In this case, we can use a technique known as Binding Values to Prepared Statements.

Consider the following code snippet.

sqlite3_stmt* stmt = NULL;
string sql;
string key = "'; DROP TABLE DEVICE_DATA; --";  // trouble

sql = string("SELECT DATE, VALUE FROM DEVICE_DATA WHERE NAME = ?;");

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

sqlite3_bind_text(stmt, 1, key.c_str(), -1, SQLITE_STATIC);

sqlite3_step(stmt);
sqlite3_finalize(stmt); // always clean up   

What this does is to replace the value for NAME with the ‘?’ 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 the execution of the parameter values, and of course, the SELECT will fail to find a variable. If we were inserting a string into the table, the value would end up with the following entry:

'; DROP TABLE DEVICE_DATA; --

This is somewhat better than having the code executed.

Protecting Complex Queries.

While updating my code, I found another example worth discussing. The piotserver config file has provisions for creating variables that only store the most recent value. For instance, let’s say we want to track how often the server has been restarted. We would place the following JSON in the config file:

"values": [
  {
    "data_type": "INT",
    "initial.value": 0,
    "key": "START_COUNT",
    "title": "number of boots",
    "tracking": "track.latest"
  }
],
"sequence": [
  {
    "enable": true,
     "trigger": {
      "event": "startup"
    },
    "steps": {
      "action": [
        {
          "cmd": "EVAL",
          "expression": "START_COUNT := START_COUNT + 1"
        }
      ]
    }
  }
]

This creates the variable START_COUNT, initializes it to zero, and increments the count at each startup. Setting the “tracking” to “track.latest” instructs the database to store only the most recent value.

Imagine we would like to set START_COUNT to 3 with a timestamp of 1751905094. We should avoid using an SQL update command to prevent multiple entries in the DEVICE_DATA table for START_COUNT, as it only updates existing entries. Instead, we should execute the following SQL statement.

BEGIN;

DELETE FROM DEVICE_DATA WHERE NAME = ‘START_COUNT’;

INSERT INTO DEVICE_DATA (NAME,DATE,VALUE)
      VALUES (‘START_COUNT’ 1751905094, 3);

COMMIT;

However, as I discovered while coding, this is considered four separate statements, and the SQLite bind functions only work on one statement at a time. So my code snippet turned out to be like this:

string key = "START_COUNT"; 
time_t time =  1751905094;
string value = "3";

sqlite3_stmt* stmt = NULL;

sqlite3_exec(_sdb, "BEGIN;", NULL, NULL, NULL);

sqlite3_prepare_v2(_sdb, "DELETE FROM DEVICE_DATA WHERE NAME = ?;", -1,  &stmt, NULL);
sqlite3_bind_text(stmt,   1, key.c_str(), -1, SQLITE_STATIC);
sqlite3_step(stmt);
sqlite3_finalize(stmt); // always clean up  

sqlite3_prepare_v2(_sdb, "INSERT INTO DEVICE_DATA (NAME,DATE,VALUE) VALUES(?,?,?);", -1,  &stmt, NULL);
sqlite3_bind_text(stmt,   1, key.c_str(), -1, SQLITE_STATIC);
sqlite3_bind_double(stmt, 2, time);
sqlite3_bind_text(stmt,   3, value.c_str(), -1, SQLITE_STATIC) 
sqlite3_step(stmt);
sqlite3_finalize(stmt); // always clean up  

sqlite3_exec(_sdb, "COMMIT;", NULL, NULL, NULL);

In truth, I wrapped the statements with proper checking; If you want the gory details, feel free to examine the actual code for the saveUniqueValueToDB function in the GitHub repository.

I should have known better.

I keep realizing that writing these articles also serves as a form of code review for me. Somehow, I let this vulnerability slip by, and luckily, I caught it before any damage was done.