Here we will learn what is SQLite injection attack? and how to prevent sqlite injection attacks with examples.
In SQLite injection means injecting some malicious code to gain access to other databases while accepting the input from web application.
Suppose we have registration page where the user needs to enter username but instead of that if he enters SQLite statement then it will run on our database and return the data based on his query statement.
The basic idea for SQLite injection attacks is to get secure information from your database and to perform some vulnerable actions like updating existing records information or delete/drop tables in the database, etc.
Generally, these SQLite injection attacks can happen whenever your application relies on user input to construct the SQLite query statements. So while taking the input from users we need to validate that data before we send it to the database by defining pattern validations or accepting the input parameters in standard way.
Now we will see how SQLite injection attacks can happen and how we can prevent it with examples for that create table emp_master in your database using the following queries.
CREATE TABLE emp_master
(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT,
last_name TEXT,
salary NUMERIC,
dept_id INTEGER);
INSERT INTO emp_master
values (2,'Shweta','Jariwala', 19300,2),
(3,'Vinay','Jariwala', 35100,3),
(4,'Jagruti','Viras', 9500,2),
(5,'Shweta','Rana',12000,3),
(6,'sonal','Menpara', 13000,1),
(7,'Yamini','Patel', 10000,2),
(8,'Khyati','Shah', 500000,3),
(9,'Shwets','Jariwala',19400,2),
(12,'Sonal','Menpara', 20000,4);
Now run following query to check the records of emp_master table.
sqlite> SELECT * FROM emp_master;
emp_id first_name last_name salary dept_id
---------- ---------- ---------- ---------- ----------
2 Shweta Jariwala 19300 2
3 Vinay Jariwala 35100 3
4 Jagruti Viras 9500 2
5 Shweta Rana 12000 3
6 Sonal Menpara 13000 1
7 Yamini Patel 10000 2
8 Khyati Shah 50000 3
9 Shwets Jariwala 19400 2
12 Sonal Menpara 20000 4
Suppose in our web application we are accepting input from the user to show user details during that time if he enters query like as shown below then it will return all the records from table irrespective of employee id.
12 or 1 = 1
The above input will form the query like as shown below.
SELECT * FROM emp_master WHERE emp_id = 12 or 1=1;
Here in above query WHERE 1=1 is always returning true and in OR operator one operand TRUE means, the whole condition will return it as TRUE then it will return all the records from employee table irrespective of employee id like as shown below.
SELECT * FROM emp_master WHERE emp_id = 12 or 1=1;
emp_id first_name last_name salary dept_id
---------- ---------- ---------- ---------- ----------
2 Shweta Jariwala 19300 2
3 Vinay Jariwala 35000 3
4 Jagruti Viras 9500 2
5 Shweta Rana 12000 3
6 Sonal Menpara 13000 1
7 Yamini Patel 10000 2
8 Khyati Shah 49900 3
9 Shwets Jariwala 19400 2
12 Sonal Menpara 20000 4
This way hacker can easily get all the sensitive information just by injecting some piece of code.
To prevent this type of SQLite injection we need to accept the user input as a string and then perform the operation on the database like as shown below.
SELECT * from emp_master where emp_id='12 OR 1=1';
Whenever we run the above query it will return employee details whose employee id matches with “12 OR 1=1” this is one of the ways to avoid SQLite injection attacks.
We will see another example of an SQLite injection attack using batch statements.
Generally, the SQLite support semicolon separated statements which are called as batched statements like as shown following.
SELECT * FROM person WHERE SSID=?;DROPTABLE emp_master;
The above statement will return all the records from the person table and delete the table called emp_master.
In our application we are taking the input from user to show the user details based on userid that code will be like as shown below.
txtSSId = getRequestString("SSID");
txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;
Here instead of entering userid the hacker can use batch of SQLite statements to inject malicious code like as shown following.
12;DROP TABLE emp_master
Our application will create a valid SQLite statement like as shown below.
SELECT * FROM person WHERE UserId=12;DROP TABLE emp_master
The above statement will return a record from the person table where userid = 12 and it will delete emp_master table.
To prevent this kind of SQLite injection we need to consider user input as a string then our SQLite statement will be like as shown below.
SELECT * FROM person WHERE UserId = '12; DROP TABLE emp_master'
The above SQLite statement will check for person details whose UserId = '12; DROP TABLE emp_master' and return only matched records.
This way we can prevent SQLite injection attacks in applications.