SQL (Structured Query Language) is a popular programming language for managing data kept in relational databases. However, the databases can be breached when an attacker adds SQL statements that attempt to corrupt, delete, extract, or delete the data held in the databases.
With the current rise of SQL injection cases, learning how to protect your databases is critical for achieving your cyber security goals. You can also improve your skills of shielding yourself from this type of attack by watching how professionals do it.
For example, Darren Rainey, who is from the U.K. and has more than four years of experience in cyber security, usually livestreams the measures he employs to safeguard systems from various types of attacks, including SQL injection.
How does an SQL injection take place?
An SQL injection occurs when a hacker “injects” a malicious SQL statement into another statement, causing the database to carry out unintended actions. Such type of injections usually affect applications that formulate SQL statements from user actions such as the values users input on the form of a website.
The main type of SQL injection attacks is error-based attacks. They take place when attackers compromise unsanitized inputs.
If a developer fails to sanitize inputs by eliminating needless characters from inputted data, an attacker can insert wrong values and cause harm to the database.
For example, here is the PHP code of a login web form having username and password fields.
?php
$my_username=$_POST[‘username’];
$my_password=$_POST[‘password’];
$my_sql_query=”SELECT * FROM users WHERE username=’”.$my_username.”‘ AND user_password=’”.$my_password”‘;”;
?>
The above command would then be sent to a database server to determine correspondence with the data stored, before allowing or denying a user access.
Let’s say that a user inputs “computer” as username and “comp123” as the password, it would lead to the following command.
$my_sql_query=”SELECT * FROM users WHERE username=’”.$computer.”‘ AND user_password=’”.$comp123″‘;”;
However, the above code is vulnerable to attacks. If an attacker inserts ‘ or ‘a’=’a ‘or’ in the password field, then the variable $my_password will have the value ‘ or ‘a’=’a ‘or’.
In that case, the resulting command will look like the following, which usually leads to a non-empty dataset.
$my_sql_query=”SELECT * FROM users WHERE username=’”.$computer.”‘ AND user_password=” or ‘a’=’a’;”;
Consequently, since the statement a=a is always true, the attacker may be granted entry without having valid login credentials.
How to protect your database
- Sanitize inputs
An important technique you should use to safeguard your database from SQL injection attacks is to sanitize input strings. If you sanitize the user input on the server side, you will remove any potential harmful commands and ensure that users offer only the right type of input.
For example, in PHP you can use the mysqli\_real\_escape\_string() function for escaping the characters that could alter the characteristics of the SQL command.
Here is the improved version of the code mentioned above.
?php
$my_username= mysqli_real_escape_string$_POST([‘username‘]);
$my_password= mysqli_real_escape_string($_POST[‘password‘]);
$my_sql_query=”SELECT * FROM users WHERE username=‘”.$my_username.”‘ AND user_password=’“.$my_password“‘;”;
?>
With the improvements, the web form would be safeguarded when an attacker includes an escape character (\) in front of single quotes in the fields.
You can also sanitize user inputs by ensuring that numeric or alphanumeric fields lack symbol characters and removing whitespace and new line characters before sending them for processing on the server-side.
Furthermore, you should ensure that user inputs are validated to keep to the guidelines set for length, syntax, and type. For example, if users are providing email addresses in a form, they should be filtered to allow only the characters that constitute an email address.
- Restrict database permissions
You should avoid giving users excessive privileges. When your application is connecting to a database, ensure that the users are granted only the necessary privileges for that purpose.
This way, you will lower the effects of any SQL injection attacks that could compromise the security of your database.
For example, if you are using the Microsoft SQL server, you could limit database permissions as follows.
deny select on sys.tables to sqldatabasepermit;
deny select on sys.packages to sqldatabasepermit;
deny select on sys.sysobjects to sqldatabasepermit;
- Use parameterized queries
With this simple and effective technique, you can segregate the data provided by the users from the code powering the application.
As such, the two will not interact with one another directly, allowing you to minimize the effects of SQL injection attacks.
Importantly, you should avoid revealing database error information to users. If attackers get hold of the error messages, they can use them to exploit the security of your database.
Conclusion
SQL injection is one of the common types of attacks hackers use to compromise systems. Therefore, you need to stay vigilant and appropriately guard your IT infrastructure from this type of attack.
The techniques listed in this article are simple and may not offer you full protection from this type of attack. That is why you need to learn from the cyber security experts on how to reinforce your protection measures from SQL injections.
Working as a cyber security solutions architect, Alisa focuses on application and network security. Before joining us she held a cyber security researcher positions within a variety of cyber security start-ups. She also experience in different industry domains like finance, healthcare and consumer products.
