SQL Injection from possible improper neutralization of special elements used in an SQL command

This checker flags SQL injection vulnerabilities. When a SQL statement is created by using unvalidated input, it is possible for a malicious user to inject a string and execute arbitrary SQL statements with the privileges of the attacked application on the database used by this application.

Vulnerability and risk

When input to code is not validated properly, an attacker can craft the input in a form that will allow the attacker to execute arbitrary SQL statements. With this sort of opportunity, an attacker could:

  • read confidential data in the application's database
  • modify data in the application's database
  • execute arbitrary commands such as deleting all the information in the database

Mitigation and prevention

To avoid this issue, it is best to

  • use only constant strings when creating SQL statement
  • use safe libraries to create parameterized SQL statements that use inputs
  • add validation code before using inputs in a SQL statement

Vulnerable code example 1

The following is an example containing a SQL injection vulnerability by using the SQLite C/C++ API.

1  void cwe_89_example_2(sqlite3 *database)
2  {
3      const char *userName;
4      const char *itemName;
5      char *sql;
6      gets(userName);
7      gets(itemName);
8      strcat(sql, "SELECT * FROM items WHERE owner = '");
9      strcat(sql, userName);
10     strcat(sql, "' AND itemname = '");
11     strcat(sql, itemName);
12     strcat(sql, "'");
13     char *errMsg = 0;
14     sqlite3_exec(database, sql, NULL, 0, &errMsg);
15 }

Klocwork produces a SQL injection report for line 14, indicating that an unsafe SQL query string has been used as a command. A SQL injection in this case could happen using the input `“name'; DELETE FROM items; --”` for `itemName`. This is due to not properly escaping reserved characters in SQL queries such as single-quote characters in the `itemName` string. In this case, assuming the user name is `'wiley'`, the sql string is parsed and executed as the following 3 statements:'

SELECT * FROM items WHERE owner = 'wiley' AND itemname = 'name';

The execution of the `DELETE` statement will delete all entries from the `'items'` table in the database.

Fixed code example 1

Constructing SQL queries by using unvalidated user input and not escaping reserved characters is dangerous. For this reason, you should use other functions from the SQLite C/C++ API for constructing a prepared statement object from the sql query, and binding user input to the parameters of the sql query, where proper handling of reserved characters is done. For example, you could construct a prepared statement object by using `sqlite3_prepare_v2` and bind the parameters of the query from the user input by using the `sqlite3_bind*` functions. This approach will correctly escape the reserved SQL query characters (for example, single-quote characters), which will prevent the construction and execution of the `"DELETE from items"` command. This can be done as follows:

1  void cwe_89_example_2_fixed(sqlite3 *database)
2  {
3      const char *userName;
4      const char *itemName;
5      const char *sql = "SELECT * FROM items WHERE owner = (?) AND itemname = (?)";
6      gets(userName);
7      gets(itemName);
8      sqlite3_stmt *statement;
9      if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) != SQLITE_OK) {
10         printf("sqlite3_prepare_v2 failure: %s", sqlite3_errmsg(database));
11         return;
12     }
13     if (sqlite3_bind_text(statement, 1, userName, -1, SQLITE_TRANSIENT) != SQLITE_OK) {
14         printf("sqlite_3_bind_text unable to bind argument 1: %s", sqlite3_errmsg(database));
15         sqlite3_finalize(statement);
16         return;
17     }
18     if (sqlite3_bind_text(statement, 2, itemName, -1, SQLITE_TRANSIENT) != SQLITE_OK) {
19         printf("sqlite_3_bind_text unable to bind argument 2: %s", sqlite3_errmsg(database));
20         sqlite3_finalize(statement);
21         return;
22     }
23     if (sqlite3_step(statement) != SQLITE_DONE) {
24         printf("sqlite_3_step failure: %s", sqlite3_errmsg(database));
25     }
26     sqlite3_finalize(statement);
27 }

Security training

Application security training materials provided by Secure Code Warrior.


This checker can be extended through the Klocwork knowledge base. See C/C++ knowledge base reference for more information.