How to prevent SQL injection attacks

SQL injections are one of the most utilized web attack vectors used with the goal of retrieving sensitive data from organizations. When you hear about stolen credit cards or password lists, they often happen through SQL injection vulnerabilities. Fortunately, there are ways to protect your website from SQL injection attacks.

An SQL injection is a technique that attackers apply to insert SQL query into input fields to then be processed by the underlying SQL database. These weaknesses are then able to be abused when entry forms allow user-generated SQL statements to query the database directly.

To give you an example scenario, take a typical login form consisting of a user and email field and a password field. After the login info is submitted, it is combined with an SQL query on your web server. In PHP, the command is written in the following way:

<?php
$query = "SELECT * FROM users WHERE username = '" . $_POST['username'] . "'";
$query .= " AND password = '" . $_POST['password'] . "'";			 
?>

It is sent to the server to verify if it was given a valid username with a corresponding password. A username "james" with the "1111″ password would result in this command:

SELECT * FROM users WHERE username='james' AND password='1111'

But if they put something like "james’;—", the query would look like this:

SELECT * FROM users WHERE username='james'; -- ' AND password='1111'

In this scenario, the attacker is using SQL comment syntax. The remaining code after the double-dash (—) sequence will not run. Meaning an SQL would be:

SELECT * FROM users WHERE username='james';

It will then return user data that was entered in the password field. This move could allow the login screen to be bypassed.

An attacker can also go further by adding another Select condition, "OR 1=1″, that will result in the following query:

SELECT * FROM users WHERE username='james' OR 1=1;

The query returns a non-empty dataset for any potential login with the entire "users" table database.

The hack above showed you a significant security flaw of any site, but it is only a small example of what it could do. More advanced hacks will allow an attacker to run arbitrary statements, causing much bigger damage. This can lead to:

• Extraction of private data, such as credit cards, passports, hospital records
• Enumeration of the authentication user details, allowing these logins to be used on other websites
• A corrupted database, execution of OS commands, deleted or inserted data and destroyed operations for the entire website
• Full system compromise

If you want to know more about how the attack is happening watch this video.

With user input channels being the main vector for such attacks, the best approach is controlling and vetting user input to watch for attack patterns. Developers can also avoid vulnerabilities by applying the following main prevention methods.

The validation process is aimed at verifying whether or not the type of input submitted by a user is allowed. Input validation makes sure it is the accepted type, length, format, and so on. Only the value which passes the validation can be processed. It helps counteract any commands inserted in the input string. In a way, it is similar to looking to see who is knocking before opening the door.

Validation shouldn't only be applied to fields that allow users to type in input, meaning you should also take care of the following situations in equal measure:

• Use regular expressions as whitelists for structured data (such as name, age, income, survey response, zip code) to ensure strong input validation.
• In case of a fixed set of values (such as drop-down list, radio button), determine which value is returned. The input data should match one of the offered options exactly.

The below shows how to carry out table name validation.

switch ($tableName) {
    case 'fooTable': return true;
    case 'barTable': return true;
    default: return new BadMessageException('unexpected value provided as table name');
}

The $tableName variable can then be directly appended—it is now widely known to be one of the legal and expected values for a table name.

In the case of a drop-down list, it's very easy to validate the data. Assuming you want a user to choose a rating from 1 to 5, change the PHP code to something like this:

<?php
if(isset($_POST["selRating"]))
{
    $number = $_POST["selRating"];
    if((is_numeric($number)) && ($number > 0) && ($number < 6))
    {
        echo "Selected rating: " . $number;
    }
    else
        echo "The rating has to be a number between 1 and 5!";
}

You have added two simple checks:

1. It has to be a number (the is_numeric() function).
2. You require that $number to be bigger than 0 and smaller than 6, which leaves you with a range of 1–5.

Data that is received from external parties has to be validated. This rule applies not only to the input provided by Internet users but also to suppliers, partners, vendors, or regulators. These vendors could be under an attack and send malformed data even without their knowledge.

Parameterized queries are a means of pre-compiling an SQL statement so that you can then supply the parameters in order for the statement to be executed. This method makes it possible for the database to recognize the code and distinguish it from input data.

The user input is automatically quoted and the supplied input will not cause the change of the intent, so this coding style helps mitigate an SQL injection attack.

It is possible to use parameterized queries with the MySQLi extension, but PHP 5.1 presented a better approach when working with databases: PHP Data Objects (PDO). PDO adopts methods that simplify the use of parameterized queries. Additionally, it makes the code easier to read and more portable since it operates on several databases, not just MySQL.

This code uses PDO with parameterized queries to prevent the SQL injection vulnerability:

<?php
$id = $_GET['id'];
$db_connection = new PDO('mysql:host=localhost;dbname=sql_injection_example', 'dbuser', 'dbpasswd');
//preparing the query
$sql = "SELECT username 
            FROM users
            WHERE id = :id";
$query = $db_connection->prepare($sql);
$query->bindParam(':id', $id);
$query->execute();
//getting the result
$query->setFetchMode(PDO::FETCH_ASSOC);
$result = $query->fetchColumn();
print(htmlentities($result));

Stored procedures (SP) require the developer to group one or more SQL statements into a logical unit to create an execution plan. Subsequent executions allow statements to be automatically parameterized. Simply put, it is a type of code that can be stored for later and used many times.

So, whenever you need to execute the query, instead of writing it over and over, you can just call the stored procedure.

Here is a process of creating a stored procedure in MySQL server. For example, you have a table like this:

CREATE TABLE `salary` (
		  `empid` int(11) NOT NULL,
		  `sal` int(11) DEFAULT NULL,
		  PRIMARY KEY (`empid`)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Suppose there is an employee who needs to get the aggregated data on the corporate salary from that table. First, you need to create a user 'tr':

CREATE USER 'tr'@'localhost' IDENTIFIED BY 'mypass';

That user will only need an EXECUTE privilege to the schema where the table resides:

grant execute on hris.*  to tr@`%`

The SP is created as follows:

DELIMITER $$
        
CREATE PROCEDURE `avg_sal`(out avg_sal decimal)
BEGIN
    select avg(sal) into avg_sal from salary;
END

To call an SP from a PHP application, you can use PDO:

$db_connection = new PDO('mysql:host=localhost;dbname=hris', 'tr', 'mypass');
		$query = $db_connection->exec('call avg_sal(@out)');
		$res = $query->query('select @out')->fetchAll();
		print_r($res);

The $res will display the average salary as per the user's request. Then, the user can carry out the output process with PHP.

SP now connects the user (the employee) and the table (salary), which the user has no direct access to, making it an essential asset in database security.

Always use character-escaping functions for user-supplied input provided by each database management system (DBMS). This is done to make sure the DBMS never confuses it with the SQL statement provided by the developer.

For example, use the mysql_real_escape_string() in PHP to avoid characters that could lead to an unintended SQL command. A modified version for the login bypass scenario would look like the following:

$db_connection = mysqli_connect("localhost", "user", "password", "db");
		$username = mysqli_real_escape_string($db_connection, $_POST['username']);
		$password = mysqli_real_escape_string($db_connection, $_POST['password']);
		$query = "SELECT * FROM users WHERE username = '" . $username. "' AND password = '" . $password . "'";

Previously, your code would be vulnerable to adding an escape character (\) in front of the single quotes. However, having this small alteration will protect against an illegitimate user and mitigate SQL injection.

Don't connect your application to the database using an account with root access. This should be done only if absolutely needed since the attackers could gain access to the whole system. Even the non-administrative accounts server could place risk on an application, even more so if the database server is used by multiple applications and databases.

For that reason, it's better to enforce least privilege on the database to defend the application against SQL injection. Ensure that each application has its own database credentials and that those credentials have the minimum rights the application needs.

Instead of trying to determine which access rights you should take away, focus on identifying what access rights or elevated permissions your application needs. If a user only needs access to some parts, you could create a mode that strictly serves this function.

One of the best practices to identify SQL injection attacks is having a web application firewall (WAF). A WAF operating in front of the web servers monitors the traffic which goes in and out of the web servers and identifies patterns that constitute a threat. Essentially, it is a barrier put between the web application and the Internet.

A WAF operates via defined customizable web security rules. These sets of policies inform the WAF what weaknesses and traffic behavior it should search for. So, based on that information, a WAF will keep monitoring the applications and the GET and POST requests it receives to find and block malicious traffic.

The value of a WAF comes in part from the ease with which policy modification can be enforced. New policies can be added in no time, enabling rapid rule implementation and fast incident response.

WAFs provide efficient protection from a number of malicious security attacks such as:

• SQL injection
• Cross-site scripting (XSS)
• Session hijacking
• Distributed denial of service (DDoS) attacks
• Cookie poisoning
• Parameter tampering

Along with these benefits, a WAF also offers:

• Automatic protection from varying known and unknown threats, with not only strong default policies but also fixes for your specific WAF architecture
• Real-time application security monitoring and robust HTTP traffic logging that lets you see what's happening instantly

Considering the benefits, even beyond preventing SQL injection attacks, a WAF should always be considered a part of web security defense in-depth strategy.

Prevention techniques such as input validation, parametrized queries, stored procedures, and escaping work well with varying attack vectors. However, because of the large variation in the pattern of SQL injection attacks they are often unable to protect databases.

Therefore, if you want to cover all bases, you should apply the abovementioned strategies in combination with a trusted WAF. The primary WAF benefit is that it provides protection for custom web applications that would otherwise go unprotected.

WAF technology:

• Maximizes the detection and catch rate for threats by stopping them before they reach a web server.
• Helps attain industry standards for known vulnerabilities through blacklisting.
• Promptly fixes vulnerabilities through whitelisting.
• Ensures protection through the ease of use without having to change the application itself.

If you need more detailed control, higher security and lower latency, then the Positive Technologies WAF can help:

• PT AF does not interfere with the performance of your virtual infrastructure.
• We offer innovative solutions for mitigating SQL injections, XSS, DDoS, zero-day attacks (to name a few), and the threat triaging is done in an automated manner.
• There are multiple deployment options, including cloud, hardware, or virtual appliance.
• Our WAF solution also helps you cost-effectively satisfy PCI requirements.

When it comes to configuring, PT AF takes the time to get to the heart of what matters — because it's our mission to ensure that our clients' businesses are never interrupted.

As a leading provider of security solutions, we are committed to continuous improvement, research, and practical penetration testing. Test your website today with PT BlackBox Scanner: it's simple, accurate, and free.