SQL Injection: Poor Validation

Relying on HTML, XML, and other types of encoding to validate untrusted input might allow an attacker to modify the statement's meaning or to execute arbitrary SQL commands.

The use of encoding functions such as mysql_real_escape_string() will prevent some, but not all SQL injection vulnerabilities. Relying on such encoding functions is equivalent to using a weak deny list to prevent SQL injection and might allow the attacker to modify the statement's meaning or to execute arbitrary SQL commands. Since it is not always possible to determine statically where input will appear within a given section of dynamically interpreted code, the Fortify Secure Coding Rulepacks may present validated dynamic SQL data as "SQL Injection: Poor Validation" issues, even though the validation may be sufficient to prevent SQL Injection within that context.

SQL injection errors occur when:

1. Data enters a program from an untrusted source.



2. The data is used to dynamically construct a SQL query.

Example 1: The following example demonstrates how the configuration of the database can alter the behavior of mysqli_real_escape_string(). When the SQL mode is set to "NO_BACKSLASH_ESCAPES" the backslash character is treated as a normal character, and not an escape character[5]. Since mysqli_real_escape_string() takes this into account, the following query is vulnerable to SQL injection as " is no longer escaped to \" due to the database configuration.

  mysqli_query($mysqli, 'SET SQL_MODE="NO_BACKSLASH_ESCAPES"');
  ...
  $userName = mysqli_real_escape_string($mysqli, $_POST['userName']);
  $pass = mysqli_real_escape_string($mysqli, $_POST['pass']);
  $query = 'SELECT * FROM users WHERE userName="' . $userName . '"AND pass="' . $pass. '";';
  $result = mysqli_query($mysqli, $query);
  ...

If an attacker leaves the password field blank and enters " OR 1=1;-- for userName the quotation marks will not be escaped and the resulting query is as follows:

  SELECT * FROM users
  WHERE userName = ""
  OR 1=1;
  -- "AND pass="";

Since OR 1=1 causes the where clause to always evaluate to true and the double hyphens cause the rest of the statement to be treated as a comment, the query becomes logically equivalent to the much simpler query:

  SELECT * FROM users;

One traditional approach to preventing SQL injection attacks is to handle them as an input validation problem and either accept only characters from an allow list of safe values or identify and escape a list of potentially malicious values (deny list). Checking an allow list can be a very effective means of enforcing strict input validation rules, but parameterized SQL statements require less maintenance and can offer more guarantees with respect to security. As is almost always the case, implementing a deny list is riddled with loopholes that make it ineffective at preventing SQL injection attacks. For example, attackers may:

- Target fields that are not quoted
- Find ways to bypass the need for certain escaped metacharacters
- Use stored procedures to hide the injected metacharacters

Manually escaping characters in input to SQL queries can help, but it will not make your application secure from SQL injection attacks.

Another solution commonly proposed for dealing with SQL injection attacks is to use stored procedures. Although stored procedures prevent some types of SQL injection attacks, they fail to protect against many others. Stored procedures typically help prevent SQL injection attacks by limiting the types of statements that can be passed to their parameters. However, there are many ways around the limitations and many interesting statements that can still be passed to stored procedures. Again, stored procedures can prevent some exploits, but they will not make your application secure against SQL injection attacks.