Stop SQLite Injection Attacks in Their Tracks (SQLite C Tutorial)

What if a hacker could modify or delete your app’s database without needing to log in? That’s possible if you write poor code. Today I’m going to show you code that’s vulnerable to a SQL injection attack, and also how to fix it.

Here’s the offending code. It’s the kind of code that you’d write when just getting started with database programming:

    printf("Enter username (and push enter):\n");
    char username[100];
    if(gets_s(username, sizeof(username))) {
        printf("Looking up user %s\n", username);

        char *errMsg = NULL;
        char sql[1024];
        snprintf(sql, sizeof(sql), "SELECT * FROM users WHERE username is '%s';", username);
        bool userExists = false;
        retCode = sqlite3_exec(db, sql, checkUserExists, &userExists, &errMsg);
        if (retCode != SQLITE_OK) {
            fprintf(stderr, "SQL error: %s\n", errMsg);
            sqlite3_free(errMsg);
        } else {
            if(userExists) {
                printf("Welcome back %s\n", username);
            } else {
                printf("User %s doesn't exist.\n", username);
            }
        }
    }

The code asks the user for their username, and then puts that straight into a SELECT query using snprintf(). And just like that, there’s a gigantic security hole in your program.

The program works just fine, provided that users behave themselves. However, enter the following at the prompt, and bad things happen:

'; DROP TABLE 'users

No prizes for guessing what happens next. The program appears to work, but the users table has been nuked. It no longer exists.

How SQL Injection Works

The flaw in the code above, is inserting the user’s input directly into a SQL query string. Here’s what the query string looks like with the injection attack above:

SELECT * FROM users WHERE username is ''; DROP TABLE 'users';

This string contains two SQL queries:

• A username lookup (for an empty name, and
• A query to DROP the users table.

SQL database systems will execute both queries, and BOOM! Users gone.

Never ever ever ever pass user input directly into a SQL query string. It’s a gigantic security hole.

Preventing SQL Injection Attacks

How do we fix this? There are a few techniques, the best of which is to use something called “prepared statements.” As a bonus, prepared statments are faster because the database can pre-compile them. Prepared statements separate the base query from the input data. This way the data can’t be interpreted as a SQL query.

Here’s how to do this with SQLite. First, stop using sqlite3_exec() for anything with user input. Use sqlite3_prepare_v2() instead.

Here’s the fixed code:

    printf("Enter username (and push enter):\n");
    char username[100];
    if(gets_s(username, sizeof(username))) {
        printf("Looking up user %s\n", username);

        char *errMsg = NULL;
        sqlite3_stmt *lookupUserStmnt;
        const char *sql = "SELECT * FROM users WHERE username = ?;";

        if (sqlite3_prepare_v2(db, sql, -1, &lookupUserStmnt, NULL) != SQLITE_OK) {
            fprintf(stderr, "Failed to prepare user lookup statement.\n");
            goto CLEANUP;
        }

        sqlite3_bind_text(lookupUserStmnt, 1, username, -1, SQLITE_STATIC);

        bool userExists = false;
        while (sqlite3_step(lookupUserStmnt) == SQLITE_ROW) {
            const unsigned char *firstName = sqlite3_column_text(lookupUserStmnt, 3);
            if(!firstName) { firstName = "mysterious person"; }
            printf("Welcome back %s (%s)\n", firstName, username);
            userExists = true;
        }
        if(!userExists) {
            printf("User %s doesn't exist.\n", username);
        }

        sqlite3_finalize(lookupUserStmnt);
        lookupUserStmnt = NULL;
    }

The code above uses the same SQL query string. However, this time we use sqlite3_prepare_v2() to prepare the query, and sqlite3_bind_text() to insert the user provided username into the query. This separates the query from the input data, so the database won’t execute user input as a query.

Attempting the SQL injection with the new code fails. The users table remains intact. A bit of an anti-climax, but that’s exactly what we want. Safe code.

Download the Source Code

The source code (and much more) is available in the Kea Campus, to Creator & higher tier members.