SQL Injection Prevention and Mitigation

Structured Query Language (SQL) is an ANSI standard language developed to communicate with a relational database. SQL is used by all major database systems, including MySQL, PostgreSQL, SQL Server, DB2, Oracle, and so on.

An Overview of SQL

A Relational Database Management Systems (RDBMS) is used to manage relational databases, that is, it is a database system that is structured to recognize relations among stored items of information. An RDBMS is the basis for SQL and it stores data in the form of related tables. A single database can contain hundreds of tables. This is exactly what makes a database a “database.” Quite simply, a database is nothing but a structured set of data organized into tables that database software (e.g., MySQL, Oracle, PostgreSQL, etc.) can quickly access, manage, and update.

To really understand the anatomy of SQL injection vulnerabilities, you should learn SQL (among other programming languages). A SQL injection is a type of database attack that exploits lackluster input validation techniques, stored procedures or prepared statements, and the lack of other database security controls. If the attacker can dynamically change SQL statements and execute them on the backend, then the attacker can compromise the confidentiality, integrity, and availability of information stored in the database.

When I was originally learning about the prevention of SQL injection attacks, I had trouble understanding how the attack worked (and I partily attribute this to the fact that I did not know SQL). Do yourself a favor: Set up a testing environment and go to www.mysql.com to download the latest version of MySQL Community Server onto your desktop. This is an opensource database supported by an active community of opensource developers and enthusiasts. Grab a SQL book, or enroll in an online SQL class, and learn SQL!

Unless you want to be a web developer for database driven web sites and web apps, you don’t need to be an expert at SQL to really understand how it works. Just learn the basic SQL statements to get a gist of how SQL queries a database. Practice creating your own databases and tables, learn how to add data rows, and understand how to insert, read, delete, and update your data rows. In just a few hours, you will be familiarized with popular SQL keywords, operators, functions, clauses, and wildcards.

The SQL Injection Attack

As previously stated, the SQL injection attack exploits database vulnerabilities that are associated with a web application. There are many types of SQL injection attacks, such as in-band, error-based, union-based, blind, boolean, time-based, and out-of-band attacks. But, overall, each form of a SQL injection attack shares the same objective: to steal sensitive information stored inside the database. Additionally, these attacks can also be destructive, such as deleting records or dropping tables.

SQL Injection Prevention

This section summarizes some of the most important preventative controls to protect against SQL injection attacks

Escape User Input

The number one rule in preventing SQL injection attacks is to suspect all user input; it should not be trusted in any circumstance. “Escaping” characters in user input entails “sanitizing,” or stripping all user input of invalid characters or any characters that have special meaning in SQL. All DBMSs have their own escaping functions. For example, the pg_escape_string() function can be used in PostgreSQL. Not only is this necessary to prevent SQL injection attacks, but it’s necessary to prevent all types of web application attacks, such as XSS. PHP’s mysqli_real_escape_string() is a quick example of a built-in sanitizing function in PHP/MySQL environments. This function creates a legal SQL string that can be used in a SQL statement. The given string is encoded to an escaped SQL string, taking into account the current character set of the connection. 

---

<?php
$con=mysqli_connect(“localhost”,“my_user”,“my_password”,“my_db”);

// Check connection
if (mysqli_connect_errno()) {
  echo “Failed to connect to MySQL: “ . mysqli_connect_error();
}

// escape variables for security
$firstname = mysqli_real_escape_string($con, $_POST[‘firstname’]);
$lastname = mysqli_real_escape_string($con, $_POST[‘lastname’]);
$age = mysqli_real_escape_string($con, $_POST[‘age’]);

$sql=“INSERT INTO Persons (FirstName, LastName, Age)
VALUES (‘$firstname’, ‘$lastname’, ‘$age’)”;

if (!mysqli_query($con,$sql)) {
  die(‘Error: ‘ . mysqli_error($con));
}
echo “1 record added”;

mysqli_close($con);
?>

---

 

Prepared Statements

A “prepared statement” defines the correct SQL code to use and then binds the parameters (the user input) into an already-prepared SQL query. We can create prepared statements in PHP using the PDO, as well as the built-in prepare() and bind_param() functions to send prepared statements to the database and then bind parameters to the SQL query, respectively. In essence, we use prepared statements to tell the backend database what to expect by clearly differentiating between the actual query and the user input, which protects against SQL injection attacks. User input should fit naturally rather than doing a whole bunch of wacky concatenation of user input with the query. The developer typically writes an entire query, identifies which parts come from user input, and uses question marks (“?”) or other parameters to indicate that those areas are filled by the user. This is the correct and safe way to write SQL queries because we are clearly differentiating between the query and the data input. For example:

---

$stmt = $conn->prepare(“SELECT * FROM Accounts WHERE account_id = ?”);

$stmt-> bind_param(“i”, $account_id);

$stmt->execute();

$stmt->bind_result($account_id, $user_name, $account_balance);

---

 

In the above code, the $stmt variable is an example of a SQL query.” The “?” is simply the place holder for the user input that will later be stored in the $account_id variable and bound to a prepared statement using the bind_param() function. The “i” tells us that the $account_id variable should be an integer only (a form of data validation). If the code only accepts integers as acceptable user input, then any attempts to submit malicious SQL query strings will be unsuccessful. The results of this prepared statement will be bound using the $bind_results function. In other words, we bind the results to the $account_id, $user_name, and $account_balance.

Stored Procedures

These are similar to prepared statements except they are instructions actually stored by the DBMS, such as MySQL, and then called on from the web application, as opposed to being stored/prepared in code. Since these procedures are called on, this means they are stored in a defined function. If “stored procedures” sound similar to prepared statements, then you would not be far off. Stored procedures have the same purpose as prepared statements.

A stored procedure would take a very complex SQL query and store it on the database itself. Instead of using this complex code to query the database, developers can use a call command or function with the name of the stored procedure. This would prevent an attacker from seeing or manipulating the call as its being made to the data stored inside the database. Therefore, the most secure web applications will assure that all queries to the database are a stored procedure.

Whitelist Validation

Developers must determine what is considered valid, or acceptable, data in all user input before it is passed as a SQL query. That means if we expect an e-mail address, then we must enforce an e-mail address validation pattern. For instance, something@something.com. Or, if we expect an SSN as the user input, then the only acceptable input format should be xxx-xx-xxxx. As a last example, the code is expecting an integer, the user input must be an integer and not a string. For example, the preg_match() function in PHP searches a string for a pattern, returning true if the pattern exists, and false otherwise. Thus, if only letters and white spaces are considered acceptable input in the form filed, then the developer would define a regular expression that prompts an error when unacceptable characters are used.

---

if ($_SERVER[“REQUEST_METHOD”] == “POST”)  {

   $name = “”;

   $name_error_msg = “”;

   if  (empty($POST[‘name’]))  {

      $name_error_msg  = “Name is a required field.”;

}  else  {

      if  (!preg_match(“/^[a-zA-Z ]*$/”, $name))  {

         $name_error_msg = “Please use only letters and whitespaces.”;

      }

   }

}

---

 

In the example above, the developer is validating the POST request method. The first validation function is the empty() function, which is used to check whether a variable is empty or not. In this case, the validation method checks to ensure that the user has entered characters in the name field. If the user does not enter a name, then the $name_error_msg variable is displayed: “Name is a required field.” Otherwise, if the field is not empty, the user input is sanitized using the clean() function, which strips any special characters. After sanitization is completed, the code checks to see if the name is an acceptable pattern using pregmatch(), as described above. If pregmatch() returns false, the $name_error_msg echoes, “Please use only letters and whitespaces.”

The Principle of Least Privileges

All database accounts must follow the principle of least privilege, meaning accounts should only be granted the privileges necessary or required to perform their tasks, and nothing more. For example, if a user account is primarily concerned with reading data in the database, then it’s poor security to assign the account write-level access. This will help mitigate SQL injection attacks should an account be compromised.

WAFs, NGFWs, and NGIPS

SQL injection attacks can be detected/prevented using Web Application Firewalls (WAFs), Next-Generation Firewalls (NGFWs), and Next-Generation Intrusion Prevention Systems (NGIPS). For example, A FortiGuard NGFW can be configured with expressions that examine HTTP request headers that match a wide variety of malicious SQL commands. Security technologies such as these, which identify and prevent the execution of malicious SQL statements, will hopefully catch any would-be hackers.

Improper Error and Exception Handling

As previously mentioned, there are many types of SQL injection attacks, one of which is called the Error-based SQL Injection attack. In this attack, the hacker relies on error messages thrown by the database server to obtain valuable information. The type of error may, for instance, reveal the type of database or the software version, which is highly valuable information for discovering vulnerabilities. These errors may be a natural part of software, but they should be trapped and handled. From a security perspective, these errors should not divulge sensitive information; in other words, paths to resources, software version, database types, or similar types of information should not be disclosed.