S 2.363 Protection against SQL injection

Initiation responsibility: IT Security Officer, Head of IT

Implementation responsibility: Application Developer, Administrator

A series of safeguards must be taken to prevent the use of SQL injections (see T 5.131 SQL injection) or at least to make it more difficult to use them. These safeguards cover all components of an application from the application itself to the server, and even including the database management system (DBMS).

Safeguards when programming applications

One of the most important safeguards for preventing SQL injection is the careful examination and filtering of input and parameters by the application. The application should check if the data transmitted is of the expected data type. For example, if a numerical parameter is expected, the parameter can be checked in PHP ("PHP: Hypertext Preprocessor") using the is_numeric() function. In contrast, filters must be used to ensure that special characters such as the single quote character (‘), semicolon (;), and double hyphen (--) are ignored.

Using Stored Procedures and/or Prepared SQL-Statements provides more security. These are offered by many database management systems (DBMS) and were originally designed for the purpose of optimising frequently encountered queries. The advantage of these parameterised statements is that the parameters are not directly integrated into an SQL statement. Instead, the parameters are passed to the database separate from the SQL statement. The statement and its parameters are then combined by the DBMS itself, in which case the special characters mentioned above are automatically masked out.

In order to ensure potential attackers are not provided a point of attack, special attention should be paid to ensuring that applications do not output any error messages that could be used to deduce which system is being used or to determine the structure of the underlying database.

Server safeguards

The most important security safeguard on the server is to harden the operating system. In order to ensure the minimum possible number of points of attack are offered by the system, the following safeguards are taken:

• deactivation of any unneeded services,
• deletion of all unneeded user accounts,
• installation of the relevant patches, and
• deletion of all components not needed for server functionality

Furthermore, the use of an Application Level Gateway (ALG) (see S 5.117 Integration of a database server into a security gateway) should be considered. ALGs can monitor the data exchanged between the web browser and the application at the application level and prevent harmful data from reaching the server.

Another security safeguard is the use of Intrusion Detection Systems (IDS) and Intrusion Prevention Systems (IPS). An IDS analyses the data traffic transmitted over a network and detects potentially harmful data. The analysis techniques used to accomplish this are divided into the categories of Misuse and Anomaly Detection. Misuse Detection tries to recognise already known attack patterns. Anomaly Detection follows an approach in which the permissible behavioural patterns are learned in order to identify deviations from these patterns as attacks. While an IDS is able to recognise attacks and issue warnings, an IPS is additionally able to react accordingly. The reaction may consist in blocking the connection, rejecting data, or changing data, for example.

For higher security requirements, it should be examined if the use of IDS and/or IPS is suitable.

Database safeguards

The database should also be hardened in addition to the operating system. Hardening a database means the following, for example:

• deletion of unneeded stored procedures,
• deactivation of any unneeded services,
• deletion of unneeded user accounts and unneeded default accounts,
• installation of the relevant patches.

For hardening purposes, a special account should be created and used to access the database. This account should be granted the minimum level of access rights possible.

Furthermore, sensitive data such as passwords should only be stored in encrypted form in the database wherever possible.

Many manufacturers now offer tools referred to as vulnerability scanners that are able to examine applications as well as databases for security gaps, for example possible SQL injections.

Example of a basic procedure for creating secure code when using PHP and MySQL:

In PHP, the mysql_real_escape_string() function prevents special characters from being passed to a MySQL database. The function masks the special characters, for example quotation marks, contained in the string passed and therefore prevents SQL injections.

Do not use the following syntax:

• $query = "SELECT * FROM users
  
  WHERE username=
  
  '" . $_POST['username'] . "'
  
  AND password=
  
  '" . $_POST['password'] . "'";

This syntax should be used instead:

• $query = "SELECT * FROM users
  
  WHERE username=
  
  '" . mysql_real_escape_string($_POST['username']) . "'
  
  AND password=
  
  '" . mysql_real_escape_string($_POST['password']) . "'";

Example of secure code when using ASP with ADO and SQL Server:

The use of prepared statements for the example above would appear as follows in this case:

• $query = "SELECT * FROM users WHERE username=?
  
  AND password=?"
  
  Set cmd = Server.CreateObject("ADODB.Command")
  
  cmd.CommandText = query
  
  cmd.CommandType = adCmdText
  
  Set param = cmd.CreateParameter("",adVarChar, adParamInput,
  
  nMaxUsernameLength, strUsername)
  
  cmd.Parameters.Append
  
  Set param = cmd.CreateParameter("",adVarChar, adParamInput,
  
  nMaxUsernameLength, strPassword)
  
  cmd.Parameters.Append
  
  Set rs = cmd.Execute()

In this case, it must be noted that the code examples provided above are only intended to illustrate the basic approach for preventing SQL injection.

Review questions:

• Does the application carefully check and filter input and parameters before these are forwarded to the database system?
• Are stored procedures and/or prepared SQL statements used?
• Is there a guarantee that no error messages are output externally allowing for conclusions to be made regarding the used system or the structure of the underlying database?