How to Protect your Database from SQL injection
What is SQL?
SQL is a (Structured Query Language) domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). It is particularly useful in handling structured data where there are relations between different entities/variables of the data. SQL offers two main advantages over the older read/write APIs like ISAM or VSAM: first, it introduced the concept of accessing many records with one single command; and second, it eliminates the need to specify how to reach a record, e.g. with or without an index.
How does SQL injection work?
In order to run malicious SQL queries against a database server, an attacker must first find an input within the web application that is included inside of an SQL query.
In order for an SQL Injection attack to take place, the vulnerable website needs to directly include user input within an SQL statement. An attacker can then insert a payload that will be included as part of the SQL query and run against the database server.
The following server-side pseudo-code is used to authenticate users to the web application.
# Define POST variables
uname = request.POST[‘username’]
passwd = request.POST[‘password’]
# SQL query vulnerable to SQLi
sql = “SELECT id FROM users WHERE username=’” + uname + “’ AND password=’” + passwd + “’”
# Execute the SQL statement
The above script is a simple example of authenticating a user with a username and a password against a database with a table named users, and a username and password column.
The above script is vulnerable to SQL Injection because an attacker could submit malicious input in such a way that would alter the SQL statement being executed by the database server.
What’s the worst an attacker can do with SQL?
SQL is a programming language designed for managing data stored in an RDBMS, therefore SQL can be used to access, modify and delete data. Furthermore, in specific cases, an RDBMS could also run commands on the operating system from an SQL statement.
Keeping the above in mind, when considering the following, it’s easier to understand how lucrative a successful SQL Injection attack can be for an attacker.
- An attacker can use SQL Injection to bypass authentication or even impersonate specific users.
- .One of SQL’s primary functions is to select data based on a query and output the result of that query.
- An SQL Injection vulnerability could allow the complete disclosure of data residing on a database server.
- Since web applications use SQL to alter data within a database, an attacker could use SQL Injection to alter data stored in a database. Altering data affects data integrity and could cause repudiation issues, for instance, issues such as voiding transactions, altering balances and other records.
- SQL is used to delete records from a database. An attacker could use an SQL Injection vulnerability to delete data from a database. Even if an appropriate backup strategy is employed, deletion of data could affect an application’s availability until the database is restored.
- Some database servers are configured (intentional or otherwise) to allow arbitrary execution of operating system commands on the database server. Given the right conditions, an attacker could use SQL Injection as the initial vector in an attack of an internal network that sits behind a firewall.
How to protect your Database from SQL injection?
- Never trust user input
The first rule of thumb about user input is “don’t trust and verify,” which effectively means all forms of user input should be considered malicious unless proved otherwise. This accounts not only for simple input boxes such as text areas and text boxes but for everything else as well — such as hidden inputs, query string parameters, cookies and file uploads.
Just because the browser’s user interface doesn’t allow the user to manipulate an input, it doesn’t mean that it can’t be tampered with. Simple tools such as Burp Suite enable users to capture HTTP requests and modify anything, including hidden form values, before submitting them to the server. And if you think yourself clever by Base64 encoding your data, it can easily be decoded, modified and re-encoded by malicious users.
2. Validate input strings on the server side
Validation is the process of making sure the right type of input is provided by users and to neutralize any potential malicious commands that might be embedded in an input string. For instance, in PHP, you can use the mysql\_real\_escape\_string() to escape characters that might change the nature of the SQL command.
An altered version of the previously-mentioned login code would be as follows:
$username = mysqli_real_escape_string($con, $_POST[‘username’]);
$password = mysqli_real_escape_string($con, $_POST[‘password’]);
$sql_command = “select * from users where username = ‘” . $username; $sql_command .= “‘ AND password = ‘” . $password . “‘”;
This simple modification would protect your code against the attack that was presented by adding an escape character (\) in front of the single quotes that were intentionally added by the malicious user.
A note on validation: If you’ve added client-side validation functions, well done. But don’t rely on it as a defensive measure against SQL injection attacks. While client-side functions might make it a notch harder to send malicious input to your server, it can easily be circumvented with a few browser tweaks and tools such as the one just mentioned. Therefore, you need to complement it with server-side validation.
Some programming platforms, such as ASP.NET, include built-in features that will automatically evaluate user input for malicious content on page postbacks. But they can be circumvented by hackers with enough nerves and subtlety, so you should nonetheless run user input through your own security check procedures. You can never be too cautious.
3. Use command parameters
A better alternative to escaping would be to use command parameters. Command parameters are defined by adding placeholder names in SQL commands, which will later be replaced by user input. ASP.NET has a very intuitive and easy-to-use set of APIs for this purpose.
The following code, written in C#, shows how you can use the command parameters to protect your website against SQL injection:
SqlCommand cmd = new SqlCommand (“SELECT * FROM users WHERE username=@username AND password=@password”,con);
SqlParameter username = new SqlParameter(); username.ParameterName = “@username”; username.value = txtUsername.Text; cmd.Parameters.Add(username);
SqlParameter password = new SqlParameter(); password.ParameterName = “@password”; password.value = txtPassword.Text; cmd.Parameters.Add(password);
You start by creating a SqlCommand object and using the @parameter_name paradigm in the command string where user input should be inserted.
You then create instances of SqlParameter objects, in which you insert the user input, instead of directly concatenating it with the command string.
Finally, you add the SqlParameter object to the SqlCommand object’s Parameters collection, which will replace the parameters with the provided input. ADO.net takes care of the rest.
In PHP, the equivalent is prepared statements, which is a bit more involved than its ASP.net counterpart. You can explore it here.
4. Explicitly cast your input
This tip is for languages such as PHP, which are weakly typed, which means you do not usually define data types for variables, and the language automatically takes care of converting different data types between each other.
Explicit casts can act as a shortcut to escaping input where non-string types are involved. So, if you’re expecting the user to input an int for the age parameter, you can ensure the safety of the input with the following code in PHP:
$age = (int)$_POST[‘age’];
Take note that this snippet only validates the input’s type, not its range. So you’ll have to run other code to make sure the user doesn’t enter a negative age — or an unrealistic one such as 1300.
Also, another best practice is to avoid using single quotes in SQL commands where a non-string input is involved. So instead of using the following code …
$sql_command = “select * from users where age = ” . $age;
… it would be a bit safer to use the following one:
$sql_command = “select * from users where age = ‘” . $ age. “‘”;
This is how you can protect your database from SQL injection.