SQL injection

Data read from an untrusted source is used in the construction of an SQL query

Since R2023a

Description

This checker is deactivated in a default Polyspace^® as You Code analysis. See Checkers Deactivated in Polyspace as You Code Analysis (Polyspace Access).

This defect occurs when data read from an untrusted source such as standard input is used in the construction of an SQL query.

This defect checker detects the flow of data from an untrusted source to a function that executes an SQL query (or prepares an SQL query to execute later). The checker flags functions from the following API-s:

MySQL C API – SQL query functions such as mysql_query() and mysql_real_query().
SQLite API – SQL query functions such as sqlite3_exec() or SQL statement preparation functions such as sqlite3_prepare() and sqlite3_prepare_v2().

Untrusted sources include strings read from the standard input stdin using the fread() or fgets() function or from files opened with fopen(). Note that unlike other Bug Finder checkers, this checker skips functions not called directly or indirectly from the main function (if there is a main).

Note that the defect checker is not available in the Polyspace user interface and is disabled even if you select the value all for the option Find defects (-checkers). For the issue to be detected, the checker must be enabled explicitly using the option -checkers SQL_INJECTION.

Risk

If you construct SQL queries from user inputs but use the inputs directly in the query construction without any sanitization, the queries are vulnerable to SQL injection. A malicious user can inject code masquerading as input, resulting in:

Bypassing of logic that secures part of the database.
Execution of malicious SQL queries or shell commands.

For instance, this SQL query, where the variables username and itemName are obtained from user inputs, is vulnerable to SQL injection:

SELECT * FROM items WHERE owner = 'userName' AND itemname = 'itemName';

If a malicious user enters two inputs such as john and

anItem' OR 'a' =
          'a

, the query translates to:

SELECT * FROM items WHERE owner = 'john' AND itemname = 'anItem' OR 'a' = 'a';

The query effectively means that the WHERE clause is always true. This allows the user to access the entire database even if they do not have the requisite permissions.

Fix

Depending on the API you are using, follow these sanitization approaches to sanitize user inputs before using them in SQL queries. For instance:

MySQL C API – Escape values in the SQL query string using mysql_real_escape_string() or mysql_real_escape_string_quote().
SQLite API – Create parametrized queries using the functions sqlite3_prepare_*() with parameters and then bind the parameters using the functions sqlite3_bind_*().

You can also write your own sanitization functions to validate or clean up the user input. For information on how to make the checker aware of custom sanitization functions, see the next section.

Extend Checker

You can extend this checker by specifying your own SQL statement execution and sanitization functions.

Suppose you want to specify that:

Function sql_exec executes an SQL statement or prepares an SQL statement for execution:
```
int sql_exec
    (char*,           /* SQL query */
     sqlite3* ,       /* Database */
     int (*callback)(void*,int,char**,char**), 
     void*, 
     char**);
```
Suppose the n_exec-th argument of this function is the SQL query string. For instance, the first argument in the above signature could be the SQL query string.
Function sql_sanitize sanitizes the untrusted (tainted) data that is later used in an SQL statement.
```
int sql_sanitize(
     char *,    /* String to sanitize */
     int);      /* Length of string */
```
Suppose the n_sanitize-th argument of this function is the data string to sanitize. For instance, the first argument in the above signature could be the data to sanitize.

To make the SQL injection checker aware of these functions:

In a file with extension .dl, add this code:

.include "models/interfaces/sql.dl"

Sql.Basic.execution("sql_exec", $InParameterDeref(n_exec-1)).
Sql.Basic.sanitizing("sql_sanitize", $OutParameterDeref(n_sanitize-1)).

If n_exec and n_sanitize are both 1 (that is, the first parameters of each function are the parameters of interest), then the statements become:

.include "models/interfaces/sql.dl"

Sql.Basic.execution("sql_exec", $InParameterDeref(0)).
Sql.Basic.sanitizing("sql_sanitize", $OutParameterDeref(0)).

Specify this file using the option -code-behavior-specifications. For instance, if the file is named sqlAdditionalFunctions.dl, use the analysis option:
```
-code-behavior-specifications sqlAdditionalFunctions.dl
```

Examples

expand all

Execution of SQL Query Created from Untrusted User Input

#include <stdio.h>
#include <string.h>
#include <sqlite3.h>

// Function to insert names into database
void insertIntoDatabase(void)
{
    sqlite3* db;
    char query[256] = "";
    char name[256];
    char* error_message;
    int nameLength;

    sqlite3_open("userCredentials.db", &db);

    fread(name, 1, 128, stdin);

    strcat(query, "INSERT INTO privatenames (name) VALUES ('");
    strcat(query, name);
    strcat(query, "');");


    if (sqlite3_exec(db, query, NULL, NULL, &error_message))
        {
            sqlite3_free(error_message);
        }
}

In this example, the function sqlite3_exec() creates an SQL query by concatenating parts of an SQL command with a user input. The final query is the following:

INSERT INTO privatenames (name) VALUES ('name')

A malicious user can pass SQL commands inside the user input so that the overall SQL query performs additional operations (other SQL operations or shell commands). For instance, if the user passes an input like this:

john'); DELETE FROM privatenames;--

The SQL query becomes:

INSERT INTO privatenames (name) VALUES ('john');
DELETE FROM privatenames;
--');

The -- results in the remainder of the line being commented out. The resulting SQL query consists of two statements: the first statement is the query that you intended and the second one results in an unintended deletion of all items from the database.

The event list on the Result Details pane shows how tainted data propagates into an SQL query. For instance, in this example:

The first event points to where the tainted data is obtained.
The second event points to where the tainted data flows to another variable.
The third event points to where the tainted data is eventually used.

Click on an event to navigate to the corresponding location in the source code.

Event list shows how tainted data propagates to an SQL query

Correction – Use Parametrized Queries

To avoid possible SQL injection, prepare a parametrized query using sqlite3_prepare_v2() and then bind the parameter to the user input using sqlite3_bind_text(). The binding escapes reserved characters such as single quotes and prevents construction of malicious SQL queries.

#include <stdio.h>
#include <string.h>
#include <sqlite3.h>

// Function to insert names into database
void insertIntoDatabase(void)
{
    sqlite3* db;
    sqlite3_stmt *st;

    char query[256] = "";
    char name[256];
    char* error_message;

    sqlite3_open("userCredentials.db", &db);

    fread(name, 1, 128, stdin);

    strcat(query, "INSERT INTO privatenames (name) VALUES (?);");

    if (sqlite3_prepare_v2(db, query, -1, &st, &error_message))
        {
            //Error handling
        }

    if (sqlite3_bind_text(st, 1, name, strlen(name), NULL))
        {
            //Error handling
        }

    if (sqlite3_step(st))
        {
            //Error handling
        }
}

Correction – Write Custom Sanitization Function

You can write your own sanitization function to validate or sanitize the input before using the input to construct an SQL query. In this example, the function sanitizeString() performs some basic sanitization such as removing semicolons from the input string.

#include <stdio.h>
#include <string.h>
#include <sqlite3.h>

void sanitizeString(char *, int);

// Function to insert names into database
void insertIntoDatabase(void)
{
    sqlite3* db;
    char query[256] = "";
    char name[256];
    char* error_message;
    int nameLength;

    sqlite3_open("userCredentials.db", &db);

    fread(name, 1, 128, stdin);

    nameLength = sizeof(name)/sizeof(char);
    sanitizeString(name, nameLength);

    strcat(query, "INSERT INTO privatenames (name) VALUES (");
    strcat(query, name);
    strcat(query, ");");


    if (sqlite3_exec(db, query, NULL, NULL, &error_message))
        {
            sqlite3_free(error_message);
        }
}

void sanitizeString(char *name, int len)
{
    for (int i = 0; i < len; i++)
        {
            // Remove semicolons
            if(name[i] == ';')
                {
                    name[i] = ' ';
                }
            // Other sanitization of string
        }
}

To make the Polyspace analysis aware of the nature of your sanitization function:

Specify this code in a file with extension .dl (for instance, sanitizeFunctions.dl):

.include "models/interfaces/sql.dl"

Sql.Basic.sanitizing("sanitizeString", $OutParameterDeref(0)).

Specify this option with the analysis:
```
-code-behavior-specifications sanitizeFunctions.dl
```
See also -code-behavior-specifications.

Result Information

Group: Security

Language: C | C++

Default: Off

Command-Line Syntax: SQL_INJECTION

Impact: High

Version History

Introduced in R2023a