Sqlite Injection Cheat Sheet

Posted : admin On 1/29/2022

SQLite cheat sheet lists the most common SQLite statements that help you work with SQLite more quickly and effectively.

Managing databases

Attach another database to the current database connection:

Optimize the database:

Managing Tables

Rename a table:

SQLite devs saw the exploitability of this and turned it off. However, some custom libraries have it enabled - for example, one of the more popular Windows ODBC drivers. To make this even better, this particular injection works with UNC paths - so you can remotely load a nasty library over SMB (provided the target server can speak SMB to the. This article is also available as a download, SQL injection attacks: A cheat sheet for business pros (free PDF). SQLite bug impacts thousands of apps, including all Chromium-based browsers (ZDNet). Some useful syntax reminders for SQL Injection into MySQL databases This post is part of a series of SQL Injection Cheat Sheets. In this series, I’ve endevoured to tabulate the data to make it easier to read and to use the same table for for each database backend. SQL Injection (POST/Select) SQL Injection (AJAX/JSON/jQuery) SQL Injection (CAPTCHA) SQL Injection (Login Form/Hero) SQL Injection (Login Form/User) SQL Injection (SQLite) SQL Injection (Drupal) SQL Injection - Stored (Blog) SQL Injection - Stored (SQLite) SQL Injection - Stored (User-Agent) SQL Injection - Stored (XML) SQL Injection - Blind.

Add a new column to a table:

Drop an existing column in a table:

Drop a table and its data:

Template

Managing indexes

Delete an index:

Create an expression index:

Querying Data

Query all data from a table

Mysql Sql Injection Cheat Sheet

Query data from the specified column of a table:

Query unique rows

Query rows that match a condition using a WHERE clause.

Rename column in the query’s output:

Query data from multiple tables using inner join, left join

Count rows returned by a query:

Sort rows using ORDER BY clause:

Group rows using GROUP BY clause.

Filter group of rows using HAVING clause.

Changing Data

Insert a row into a table:

Insert multiple rows into a table in a single statement:

Update all rows in a table:

Update rows that match with a condition:

Postgresql Sql Injection Cheat Sheet

Delete rows specified by a condition:

Search

Search using LIKE operator:

Injection

Search using full-text search:

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.

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.

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

Injection

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.

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.