Abstract

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

10.1 Introduction

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]:

• Unauthorized access to sensitive data.
• Backdoor entry in the database system.
• Modify and delete the sensitive data.

Example of SQL injection attack

• Retrieving hidden data
• UNION attacks
• Blind SQL injection
• Subverting application logic

There are two main reasons why the SQL injection is also a problem which are as follows:

• Some web developers are not aware of the SQL injection attack which can make the website vulnerable.
• If we provide security in our network, hackers are looking for a new attack on that system and new vulnerabilities are also found in the system.

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.

10.1.1 Types of Vulnerabilities

Types of vulnerabilities in SQL injection are as follows: [1, 3].

• Type 1 Vulnerability: In this type of vulnerability, we can check the suspicious input for malicious activity in the website with the help of the input validation. Suspicious input may permit a malicious code to be executed many times without proper and exact verification on the original intention.
• Type 2 Vulnerability: In this type of vulnerability, there is the difficulty in the characterization in the different data types which we used in the programming language for the web development.
• Type 3 Vulnerability: In this type of vulnerability, any process delay in the analysis stage till the runtime stage as the present variables are measured despite the source code using an expression to achieve the attack.
• Type 4 Vulnerability: In this type of vulnerability, there is improper definition of the datatype while designing.

10.1.2 Types of SQL Injection Attack

Different types of SQL injection attacks are as follows: [1, 2, 7]

• Tautology: By passing authentication and data extraction in which an attacker injects the code in one or more conditional statements.

  Example: Select * from student where std_id=” or ‘6=6’;

• Logically incorrect queries: Information extraction from the database, identify the injectable patterns, and performing the database fingerprinting.

  Example: Select accounts from student where login=’’ AND pass=’’;

• Union Query: By passing the authentication and data extraction in which an attacker exploits the vulnerability parameter to change the data set by using the union operator.
  Example: Select * from student where std_name=’abc’ union select * from academic where id=’421’ ‘pass=’2=2’;
• Stored Procedure: By using the built-in procedures to perform the malicious action in the database.
  Example: Select accounts from student where login= ‘abc’ AND pass=’’; SHUTDOWN;

• Piggybacking queries: By appending the malicious query to the legitimate query in the database.
  Example: Select * from emp where name= ‘abc’; drop table emp;
• Inference: It can enable the attacker to change the behavior of the application or database.
• Alternate Encodings: In this attack, an intruder can modify the injection query via using alternate encoding, such as hexadecimal, ASCII, and Unicode. Example: Select accounts from student where login= “AND pin=0; exec (char(0x73687574646f776e)).

10.1.3 Impact of SQL Injection Attack

There are various impacts of SQL injection attacks, which are given below [3, 5]:

• Impact to Confidentiality: Attacker can steal the sensitive information such as user credentials, organization secrets.
• Impact to Integrity: Attacker can update, delete, and insert the malicious data in the database, which can make the database vulnerable.
• Impact to Authentication and Authorization: Attacker can take unauthorized access of the data in the database by stealing the authorized user credential.

10.2 Objective and Motivation

My objective and motivation for the chapter on SQL injection attacks are as follows:

• To give better understanding of how we can implement the SQL injection attack by using MySQL database server and SQL queries.
• This chapter provides countermeasures information regarding the SQL injection attack.
• This chapter provides information regarding the different types of SQL injection statements.

• This chapter provides knowledge of the SQL injection with the help of the flowchart or process flow of the SQL injection attack.

10.3 Process of SQL Injection Attack

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.

10.4 Related Work

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.

10.5 Literature Review

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].

10.6 Implementation of the SQL Injection Attack

10.6.1 Access the Database Using the 1=1 SQL Injection Statement

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.

10.6.2 Access the Database Using the ““=’’’’ SQL Injection Statement

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.

10.6.3 Access and Upgrade the Database by Using Batch SQL Injection Statement

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.

10.7 Detection of SQL Injection Attack

Detection mechanism for the SQL injection attack is as follows:

• Use the single quote alphabet ‘ and check for errors and anomalies.
• By using SQL-specific command that evaluate the value at the starting point to a different value and then check for the difference in the responses.
• Use the Boolean values such as OR 1=1 and OR 1=2 and then check the difference in their result.
• Use the large amount of data for time delays embed with an SQL query, and check for contrast in the time taken to respond [1, 4].

10.8 Prevention/Mitigation from SQL Injection Attack

• Don’t use dynamic SQL: Use prepared statements, parameterized queries or stored procedures whenever needed. But don’t overuse it or we can say that at every time don’t use the dynamic SQL statement.
• Update and patch: Apply the patches and updates in a timely manner when available to prevent the exploitation of the vulnerabilities in the web applications and in the database.
• Use appropriate privileges: We should give limited access to those who are working on the web application which can limit the attack or illegal activity in the web application to a certain extent.

• Enforcement at the coding level: We can use the object-relational mapping libraries to avoid the use of SQL code.
• Escaping: A simple method is that to prevent SQL injection we have to avoid the use of characters that have the special meaning in SQL queries such as (‘), (‘’), x00, etc.
• Trust no one: We must not trust each and every data and their values. We have to filter out the user data by context [1, 2].

10.9 Conclusion

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.

References

1. 1. Rai, Sunny, and Bharti Nagpal. “Detection & Prevention of SQL injection Attacks: Development of the Decade.” 3^rd International Conference on Reliability, Infocom Technologies and Optimization (ICRITO) (Trends and Future Directions), Oct 8-10, 2014.
2. 2. Alwan, Zainab S., and Manal F. Younis. “Detection and prevention of SQL injection attack: A Survey.” International Journal of Computer Science and Mobile Computing 6.8 (2017): 5-17.
3. 3. Thiyub, Rua Mohamed, Musab AM Ali, and Farooq Basil. “The impact of SQL injection attacks on the security of databases.” Proceedings of the 6^th International Conference of Computing & Informatics. 2017.
4. 4. Caesarano, Arif Roid, and Imam Riadi. “Network Forensics for Detecting SQL Injection Attacks Using NIST Method.” International Journal Cyber-Security Digital Forensics 7.4 (2018): 436-443.
5. 5. Gupta, Himanshu, Subhash Mondal, Srayan Ray, Biswajit Giri, Rana Majumdar, Ved P. Mishra. “Impact of SQL Injection in Database Security.” International Conference on Computational Intelligence and Knowledge Economy (ICCIKE), pp. 296-299. IEEE, 2019.
6. 6. Basit, Nada, Abdeltawab Hendawi, Joseph Chen, and Alexander Sun. “A learning platform for SQL injection.” Proceedings of the 50^th ACM Technical Symposium on Computer Science Education. 2019.
7. 7. Hlaing, Zar Chi Su Su, and Myo Khaing. “A detection and prevention technique on sql injection attacks.” In 2020 IEEE Conference on Computer Applications (ICCA), pp. 1-6 IEEE, 2020.
8. 8. Chen, Ding, Qiseng Yan, and Jun Zhao. “SQL Injection Attack Detection and Prevention Techniques Using Deep Learning.” In Journal of Physics: Conference Series, vol. 1757 no. 1, p. 012055. IOP Publishing, 2021.