Secure Coding Practices – SQL Injection

Overview

SQL Injection is a well-known application vulnerability which is at the top in OWASP Top 10 vulnerabilities list since long time. In this post, we are going to discuss root cause of SQL Injection vulnerability and how developers can fix the code to prevent SQL Injection in their applications developed using different types of programming language.

What is SQL Injection

SQL Injection is a code injection vulnerability which occurs in applications where user-controlled data is inserted in SQL queries which are executed at database servers and output is rendered to end users. SQL Injection vulnerability affects web, thick client and mobile applications. However, root cause of the vulnerability remains the same across all types of applications – dynamic SQL query formation with user-controlled data. For example – the following SQL query includes userName and password parameters which contain user input –

statement = “SELECT * FROM users WHERE name = ‘+ userName +’ & passwd = ‘+ password +’”;

An end user with malicious intentions can provide one of the following inputs in userName to bypass the password check in the above query –

’ OR ‘a’ = ’a’ --
‘ OR ‘a’ = ‘a’ #

After including these inputs, the SQL query will become –

SELECT * FROM users WHERE name = ‘ ’ OR ‘a’ = ’a’ -- & passwd = ‘ ‘

Since userName cannot be null, a = a is an always true condition and comments the password check code, end user bypasses the application authentication and gets access to the first user account in users database table which is administrator role user in most applications cases.

How to Fix

Generic mitigation approaches for SQL Injection vulnerability are –

  1. Replace simple SQL queries to Parameterized Queries or Prepared statements with bind variables
  2. Implement whitelist validation of user inputs
  3. Limiting user permissions on the database
  4. Handle error conditions carefully to prevent information disclosure through application error messages

The challenge developers face is to find how these generic statements be enforced at code level. The next section illustrates the code level implementation for common programming languages.

Enforcement at Code Level

    1. Parameterized Queries/ Prepared Statement
      • Java/ JSP – Prepared statement in Java/ JSP are written in 3 steps – Prepare, Bind & Execute. Taking example of simple SQL query from earlier sections of this post –
SELECT * FROM users WHERE name = ‘+ userName +’ & passwd = ‘+ password +’

Prepared statement code for the query –

PreparedStatement stmt = conn.prepareStatement("SELECT * FROM users WHERE name = ? & passwd = ?");
stmt.setString(1, "userName");
stmt.setString(2, "password");
stmt.executeQuery();
      • C#.Net – Parameterized query equivalent of the simple SQL command in C#.Net –
SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM users WHERE name = @userName AND passwd = @password";
command.Parameters.AddWithValue("@userName", userName);
command.Parameters.AddWithValue("@password", password);
SqlDataReader dataReader = command.ExecuteReader()
      • PHP – Prepared statement in PHP is implemented through PHP Data Objects (PDO) –
$conn = new PDO("mysql:dbname = mysql", "root");
$stmt = $conn->;prepare("SELECT * FROM users WHERE name = ? & passwd = ?");
$params = array(array("userName"), array("password"));
foreach ($params as $param) $stmt->;execute($param);
    1. Whitelist based Input Validation
      • Java/ JSP – Creating Java class to validate userName and password from HTTP request –
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class Validator{
private Pattern pattern;
private Matcher matcher;
private static final String USERNAME_PATTERN = "^[a-z0-9]{3,15}$";
private static final String PASSWORD_PATTERN = "^[a-z0-9@#]{3,15}$";
public Validator(){
username_pattern = Pattern.compile(USERNAME_PATTERN);
username_pattern = Pattern.compile(PASSWORD_PATTERN);
}
public boolean username_validate(final String userName){
matcher = pattern.matcher(userName);
return matcher.matches();
}
public boolean password_validate(final String password){
matcher = pattern.matcher(password);
return matcher.matches();
}
}

The above code created a Java regular expression which allows only alphanumeric values in userName and @# special characters with alphanumeric values in password.

      • .Net – Validation control in Visual Studio provides extensive validation options such as – RequiredFieldValidation, CompareValidator, RangeValidator, RegularExpressionValidator etc. Here is a detailed article on implementing Validation control in ASP.Net applications. Similarly, Regex class in C# provides multiple methods to implement regular expression based input validation. Official Microsoft documentation can be found here.
      • PHP – PHP prag_match() function allows validation against a regular expression. A sample code for validating userName and password from HTML form is given below –
$username = $_POST[‘userName’];
$password = $_POST[‘password’];
$username_pattern = '/^[a-zA-Z0-9]*$/';
$password_pattern = '/^[a-zA-Z0-9@#]*$/';
preg_match($username_pattern, $username, $username_matches);
if(!$username_matches[0]) {
$username_error = "Only alphanumeric are allowed in username";
}
else {
preg_match($password_pattern, $password, $password_matches);
if(!$password_matches[0]) {
$password_error = "Only alphanumeric and @, # special characters are allowed in password";
}
}

We offer training on secure coding practices customized as per developers’ skillset and development methodologies. Please visit Application Security section in Service Offerings or Contact Us to hear more from us.