My 2 cents (read rants) on Information security
My 2 cents (read rants) on Information security

Second Order SQL Injection Explained with Example

Introduction

Second Order SQL injection is an application security vulnerability, it occurs when user-submitted values are stored in the database, and then it gets used by some other functionality in the application without escaping or filtering the data. Usually, data from external sources (user supplied) is considered dangerous and is escaped/filtered. Developers trust the data coming from the database. This behavior is similar to a Stored Cross Site Scripting attack. Second Order SQL Injection requires more knowledge of how submitted values are later used in order to perform successfully second-order SQL injection. Still confused? follow the example below:-

Second Order SQL Injection Example

To demonstrate the vulnerability, I prepared a vulnerable user login, signup, and password change functionality. The workflow goes like this: a user registers an account using a username and password, then logs in and changes their password. It’s a very common functionality among all dynamic web applications.  Firstly, the users are required to either signup or sign in. I sign in with the username “haider” and password “asdasd”.

Second Order SQL Injection

signed in

Second Order SQL Injection

Second Order SQL Injection example
After successfully logging it, it establishes that the username “haider” authenticated properly. I created a new user with username as “haider’ –” and password “abc”. Note that, the username value “haider’ –” is actually the SQL injection payload, its not escaped or truncated, gets saved in the database as it is,  working is explained below.

Second Order SQL Injection

So basically so far we have two usernames registered. “haider” and “haider’ –” using different passwords. After registering the account, I logged in using the username “haider’ –“.

Second Order SQL Injection

Second Order SQL Injection
As the page offers password changed functionality, I changed the password from “abc” to “123”.  Note that the username is “haider’ –” so lets construct the query for password change for this username.
UPDATE users  
SET password='123'
WHERE username='haider'--' and password='abc'
Now as the username in WHERE clause is “haider’ –“,  Not that  After the query is discarded as comments, as  is used to start comments in SQL . The query ends up like:-
UPDATE users  
SET password='123'  
WHERE username='haider'

The Query results in updating the password for the user “haider”, instead of “haider –‘ hence successfully performing second order SQL injection. It works in other scenarios as well where data from database is meant to retrieved, Hopefully it explains the concept.

Recommendations for Developers

  • Never trust data, always sanitize and validate data either its from user supplied GET/POST or it originates from database.
  • Always use prepaid statements with parametraized queries, parameterized queries compels the developer to first define all the SQL code, and then pass in each parameter to the query later rather than using user input as statement. This ensure that an attacker is not able to change the intent of a query, even if SQL commands are injected by the attacker.
Subscribe
Notify of
guest
2 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Emmy

Great writeup. Can you make the app public to practise

[…] the second in a series of noob-friendly posts that I am doing on SQL injection. Earlier I explained Second Order SQL Injections with Example. So in this post, I will be explaining the HTTP header Blind SQL injection, I prepared a Demo for […]

2
0
Would love your thoughts, please comment.x
()
x