Mohit Kumar
NSUT East Campus Formerly Ambedkar Institute of Advanced Communication Technologies and Research, Delhi, India
In recent years database security is very much needed to defend against different attacks. In this chapter we will discuss the practical implementation of the SQL injection attack by using the MySQL database server in which we understand how an attacker can compromise the database security by using the SQL injection statements embedded with the normal SQL queries. This chapter also discusses the detection and prevention mechanism from the SQL injection attack and how to protect our database from this type of attack and also gives a better understanding of the SQL injection statements.
Keywords: SQL injection, SQL injection vulnerability
SQL injection is a type of attack in which an attacker can exploit the web security vulnerability with the help of SQL queries the particular application makes to its database. It can allow the attacker to view the data in an unauthorized manner such as users’ data, data that the application itself is able to access. In this attack an attacker can modify and delete the data from the database. If the SQL injection attack is successful it can lead to the following [2, 6]:
Example of SQL injection attack
There are two main reasons why the SQL injection is also a problem which are as follows:
With the advent of mobile phones, smartphones, and tablets, etc., which run on the Android-based, Java-based and IOS-based operating system, a large amount of the data in those devices are stored in the database which is called as the SQLite database. As it is also the database which is used to store handheld device information it is also vulnerable to SQL injection attack. So, it is important to understand that the web applications, mobile applications and desktop applications and those devices which are connected to the database are also the targets of the SQL injection attack, and it can also steal the personal information of the user and use it for personal purposes as well [3].
In this chapter we are going to implement the SQL injection attack by using SQL injection statements with the SQL queries on the MySQL database server and understand the working of the SQL injection attack and also understand how an attacker implements the SQL injection attack with the SQL statements. This chapter also provides knowledge about the detection and prevention countermeasures of the SQL injection and provides the proper security to our information system.
Types of vulnerabilities in SQL injection are as follows: [1, 3].
Different types of SQL injection attacks are as follows: [1, 2, 7]
Example: Select * from student where std_id=” or ‘6=6’;
Example: Select accounts from student where login=’’ AND pass=’’;
There are various impacts of SQL injection attacks, which are given below [3, 5]:
My objective and motivation for the chapter on SQL injection attacks are as follows:
In this section we show the flowchart of the SQL injection in Figure 10.3.1 which is given below and discuss how an attacker can compromise the computer system, SQL server, and database using the SQL injection technique.
In the paper author [1] presented a detailed study on proposed methods and tools for detection and prevention of SQL injection attacks in the last decade and discussed the effectiveness of the detection and prevention mechanism.
In the paper author [2] presented the classical and modern types of SQL injection attack and displayed the different existing techniques and tools which can be used to detect and prevent an SQL injection attack as well as other cyberattacks.
In the paper author [3] proposed a technique called CombinedDetect based on two methods named as JavaScript and PHP coding to detect malicious SQL query and separate the normal data and malicious data and prevent an SQL injection attack.
In the paper author [4] implemented the detection of the SQL injection attack using the NIST method in network forensics in which firstly it created SQL injection scenarios and after that created the log file using the snort tool rule. The snort tool then mitigated the SQL injection attack by alerting the system using email. The result was analyzed with the help of user acceptance testing.
In the paper author [5] projected an approach to mitigate the SQL injection attack and maintain the database security by using a hybrid encryption mechanism in the form of Advanced Encryption Mechanism (AES) and Elliptical Curve Cryptography (ECC) in which AES at login phase prevents unauthorized access to the databases and ECC is used to encode the database so that no one can access the database without the key.
In the paper author [6] presented the web application system in which users can learn and practice SQL injection attacks. Basically this system is designed for students to become familiar with the SQL injection attack. In this system it contains 12 levels of SQL vulnerabilities which an attacker can exploit and compromise the database security.
In the paper author [7] presented an approach which detects the SQL injection attack in two steps. First, one creates lexicon, and the second step tokenizes the input query statement. Each token was detected to predefined words lexicon to prevent the SQL injection attack.
In the paper author [8] proposed an SQL injection detection method by using deep learning framework on the basis of comprehensive domestic and international research. This method can improve accuracy and also reduce the false alarm rate.
In the literature review we will discuss the different techniques or methods of the SQL injection attack and understand how we can compromise the system vulnerability using the SQL injection attack.
Incorrectly filtered escape characters
In this type of SQL injection, when the escape characters input is not filtered in the user input and pass it to the SQL query, this will result in the query alteration in the database by the end-user application [2].
The above SQL code is used to extract the records of the specified username from its table of users. If we replace the “username” entity in an incorrect way by the unauthorized user then the attacker gets the data from the database. An example of the malicious attack is shown in the diagram below [2].
Most SQL statements implement multiple statements on the SQL server but it can help the attacker to modify the queries and data and make the database more vulnerable, which is shown in the diagram below [2].
Blind SQL injection
In this type of SQL injection, the website vulnerability is visible to the attacker but the attacker cannot view the result of the attack. This type of attack has traditionally been considered time-intensive because a new statement needed to be crafted for each bit recovered, and depending on its structure, the attack may consist of many unsuccessful requests. Recent advancements have allowed each request to recover multiple bits, with no unsuccessful requests, allowing for more consistent and efficient extraction [2].
Conditional responses
This type of attack is an example of blind SQL injection which can evaluate the logical SQL queries in the database. For example, an attacker can load the URL https://books.example.com/review?id=5 OR 1=1 which can result in the query given below.
If the above statement in the diagram shows the result as the original SQL statement then the website is vulnerable to the SQL injection attack because the query passed through successfully as the legal SQL statement.
The attacker also can also reveal the version of the MySQL and other information by using the string
"https://books.example.com/review?id=5 AND substring(@@version, 1, INSTR(@@version, ‘.’) - 1)=4"
which can be used by the attacker to fulfill its goal and access more information from the SQL server and find other vulnerability for the another SQL injection attack [2].
Second order SQL injection
In this type of attack malicious SQL queries are hidden in the input values which are stored as a valid SQL statement and then it is executed the SQL queries stored as valid SQL queries. This type of attack needs more knowledge of the input values and how these values will be used for the attack. It will be difficult for the investigator to detect this type of SQL injection statement. The investigator can use the web automated tools for the detection of this type of attack and find out the evidence [2].
SQL Injection and Domain Name Service Hijacking
In this type of attack, the attacker can embed the SQL query in a DNS request and capture it and make its way onto the internet [2].
Step 1) Create database and create table student inside the database and insert the values in the table student by the authorize user.
Step 2) Access the table by the authorized user by using the SQL statement given below:
“select * from student;”
Step 3) Access the table content by the unauthorized user by using the 1=1 sql statement which is given as: “select * from student where userid = ‘2’ or 1=1;” and give unauthorized access to the attacker.
Step 1) Create database and create table student inside the database and insert the values in the table student by the authorized user.
Step 2) Access the table by the authorized user by using the SQL statement given below:
“select * from student;”
Step 3) Access the table content by the unauthorized user by using the ‘’’’=’’’’ sql statement which is given as: “select * from student where firstname = “ ” or “ ”= “ ” and password = “ ” or “ ”= “ ”;” and give unauthorized access to the attacker.
Step 1) Create database and create table student inside the database and insert the values in the table student by the authorized user.
Step 2) Access the table by the authorized user by using the SQL statement given below:
“select * from student;”
Step 3) Update the table content by the unauthorized user by using the batched sql statement which is given as “select * from student where userid=’3’; update student set firstname = ‘rst’ where userid=’1’;” and update the content of the student table database and when we view the data using “select * from student;” the content is updated.
Step 4) Delete the table content by the unauthorized user by using the batched sql statement which is given as “select * from student where userid=’3’; delete from student where userid=’1’;” and delete the content of the student table database, and when we view the data using “select * from student;” the content is deleted.
Step 5) Drop the table content by the unauthorized user by using the batched sql statement which is given as “select * from student where userid=’3’; drop table student;” and delete the content of the student table database, and when we view the data using “select * from student;” the table is dropped.
Detection mechanism for the SQL injection attack is as follows:
In this chapter we have studied and discussed the SQL injection and types of SQL injection, as well as the methodology of how an attacker executes an SQL injection attack and its practical implementation. We have also offered some detection and prevention steps about how we can provide database security from SQL injection. In future, many methodologies will be needed to determine how an attacker implements an SQL injection attack and how we can detect and prevent such an attack. Also in future we will be concerned about the weakness in SQL server database, and also deal with poor database functionality and irregularity in updating the patches in the database security. For these concerns more techniques and methodologies will emerge and be implemented to understand how an attacker can implement an SQL injection attack; more prevention and detection mechanisms will also emerge in the near future.