Sep 10 2021

SQL injection

Sep 10 2021

An example of vulnerability
How to search for potential vulnerabilities
How to fight SQL injections
Additional resources

SQL injections are attacks through which a hacker performs various unauthorized actions with a database. They can affect both data and the database structure. To perform SQL injection, an attacker passes strings with malicious commands as an input data. An application vulnerable to SQL injections doesn't check these strings and inserts them into the query template. As a result, a query is formed that performs the actions defined by an attacker. From the SQL syntax point of view, this query is correct.

These vulnerabilities pose a serious threat. In OWASP ASVS they belong to categories 5.3.4 and 5.3.5. In the OWASP Top Ten 2017 list they are classified as A1 (Injection). In Common Weakness Enumeration SQL injections take the CWE-89 position.

An example of vulnerability

If you want to see the SQLI capabilities, look at the following example:

void ProcessRequest(HttpRequest request) 
{ 
  string name = request.Form["name"];

  string sql = $"SELECT * FROM Users WHERE name='{name}'";
  ExecuteReaderCommand(sql);

  .... 
}

void ExecuteReaderCommand(string sql) 
{
  using (var command = new SqlCommand(sql, _connection))
  { 
    using (var reader = command.ExecuteReader()) { .... } 
  } 
  .... 
}

We assume that the 'name' parameter contains the user name whose data is received from the database and further processed. At the same time the program doesn't modify the values in the database — the given fragment is supposed to only read them.

This code fragment is vulnerable to SQL injections because it uses unverified external data to form a query to the database. As a result, an attacker would pass a query string to the application. This string launches the execution of various unauthorized operations.

For example, the following value can be written in the 'name' parameter:

'; DELETE FROM Users WHERE name != '

The following SQL command is a result of inserting this string into the query template:

SELECT * FROM Users WHERE name='';
DELETE FROM Users WHERE name != ''

If the value of the 'name' column is set for each user, the query above deletes all records from the 'Users' table.

How to search for potential vulnerabilities

A search for code fragments that may be vulnerable to this attack type is one of the crucial tasks in ensuring an application's vulnerability to SQL injections. To detect these vulnerabilities, it's important to find and inspect all the places where database queries are formed and executed. Many developers use tools that perform taint analysis. PVS-Studio is one of these tools.

Let's return to one of the previous examples:

void ProcessRequest(HttpRequest request) 
{ 
  string name = request.Form["name"];

  string sql = $"SELECT * FROM Users WHERE name='{name}'";
  ExecuteReaderCommand(sql);

  .... 
}

void ExecuteReaderCommand(string sql) 
{
  using (var command = new SqlCommand(sql, _connection))
  { 
    using (var reader = command.ExecuteReader()) { .... } 
  } 
  .... 
}

When performing taint analysis, PVS-Studio detects a potential vulnerability here and issues the following warning: V5608 Possible SQL injection inside method. Potentially tainted data in the first argument 'sql' is used to create SQL command.

Thus, the static analyzer automatically detects the presence of potential code vulnerabilities.

How to fight SQL injections

The recommended way to deal with SQL injections is parameterized queries. The developer substitutes the external data into the query template not directly, but through a specialized API. The query formed this way is safe, since the external data is transformed before the actual substitution.

The ways of working with the parametrized queries may differ in various programming languages. However, the basic idea remains unchanged. For example, in C# we can form the query with parameters the following way:

String userName = Request.Form["name"];

using (var command = new SqlCommand()
{
  Connection = _connection,
  CommandText = "SELECT * FROM Users WHERE UserName = @userName",
  CommandType = System.Data.CommandType.Text
})
{
  var userNameParam = new SqlParameter("@userName", userName);
  command.Parameters.Add(userNameParam);
            
  using (var reader = command.ExecuteReader()) { .... }
}

This approach ensures the security of using external data when a program forms a query to the database. However, there are other methods of fighting SQL injections. For example, you can use a special system for validating external data. It detects the fragments of SQL commands in them or converts data into a secure form via various functions. In some cases you can use simpler approaches, like checking the types of passed values.