Sqlite Injection Cheat Sheet

Posted : admin On 1/3/2022

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is the most used database engine in the world. SQLite is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day. What is SQL Injection? SQL injection attacks, also called SQLi attacks, are a type of vulnerability in the code of websites and web apps that allows attackers to hijack back-end processes and access, extract, and delete confidential information from your databases.

  • SQLite Tutorial
  • Advanced SQLite
  • SQLite Interfaces
  • SQLite Useful Resources
  • Selected Reading
Sqlite3 injection cheat sheet

If you take user input through a webpage and insert it into a SQLite database there's a chance that you have left yourself wide open for a security issue known as SQL Injection. In this chapter, you will learn how to help prevent this from happening and help you secure your scripts and SQLite statements.

Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a SQLite statement that you will unknowingly run on your database.

Never trust user provided data, process this data only after validation; as a rule, this is done by pattern matching. In the following example, the username is restricted to alphanumerical chars plus underscore and to a length between 8 and 20 chars - modify these rules as needed.

To demonstrate the problem, consider this excerpt −

The function call is supposed to retrieve a record from the users table where the name column matches the name specified by the user. Under normal circumstances, $name would only contain alphanumeric characters and perhaps spaces, such as the string ilia. However in this case, by appending an entirely new query to $name, the call to the database turns into a disaster: the injected DELETE query removes all records from users.

There are databases interfaces which do not permit query stacking or executing multiple queries in a single function call. If you try to stack queries, the call fails but SQLite and PostgreSQL, happily perform stacked queries, executing all of the queries provided in one string and creating a serious security problem.

Preventing SQL Injection

You can handle all escape characters smartly in scripting languages like PERL and PHP. Programming language PHP provides the function string sqlite_escape_string() to escape input characters that are special to SQLite.

Although the encoding makes it safe to insert the data, it will render simple text comparisons and LIKE clauses in your queries unusable for the columns that contain the binary data.

Noteaddslashes() should NOT be used to quote your strings for SQLite queries; it will lead to strange results when retrieving your data.

Here we will learn what is SQLite injection attack? and how to prevent sqlite injection attacks with examples.

SQLite Injection Attacks

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.

SQLite Injection Attacks Example

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.

Pentestmonkey Sql Injection Cheat Sheet

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

Sqlite

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.

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.

Sqlite Sql Injection Cheat Sheet

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.

SQLite Injection Attacks Example2

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.

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.