CWE Rule 89

Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection')

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).

Rule Definition

The software constructs all or part of an SQL command using externally-influenced input from an upstream component, but it does not neutralize or incorrectly neutralizes special elements that could modify the intended SQL command when it is sent to a downstream component.

Polyspace Implementation

This rule checker checks for the issue SQL Injection.

Examples

expand all

SQL Injection

Issue

SQL Injection 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 be executed). Functions flagged by this checker include the following:

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().

Risk

SQL queries are often constructed from user inputs. However, if these inputs are directly used in the construction of an SQL database query without any sanitization, they 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 even shell commands.

For instance, an SQL query such as the following, where username and itemName are obtained from user inputs, is vulnerable to SQL injection:

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

If a 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';

resulting in the WHERE clause being 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 the recommended 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. See the following section.

Extend Checker

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

Suppose you want to specify the following:

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 to be executed. For instance, the first argument in the above signature could be the SQL query string.
Function sql_sanitize sanitizes the untrusted (tainted) data to be 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 be sanitized. For instance, the first argument in the above signature could be the data to be sanitized.

To make the SQL injection checker aware of these functions:

In a file with extension .dl, add the following:

.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
```

Example – 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)) // Noncompliant
        {
            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 query is supposed to be:

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 one is the intended query and the second one results in an unintended deletion of all items from the database.

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 takes care of escaping reserved characters such as single quotes and prevents construction of malicious SQL queries as shown earlier.

#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 it 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 the following in a file with extension .dl (for instance, sanitizeFunctions.dl):

.include "models/interfaces/sql.dl"

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

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

Check Information

Category: Data Neutralization Issues

Version History

Introduced in R2023a