Second Order SQL Injection Explained with Example

Introduction

Second Order Sql injection is an application 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 behaviour is similar to Stored Cross Site Scripting attack. Second Order Sql Injection requires more knowledge of how submitted values are later used in order to perform successful 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. Lets say a user registers, then logins and changes password. Its very common functionality among all dynamic web applications.  Firstly , the users are required to either signup or signin. I signin with username “haider” and password “asdasd”.

Second Order SQL Injection

signed in

Second Order SQL Injection

Second Order SQL Injection
After successfully logging it, it establishes that the username “haider” authenticates 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

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. Second Order SQL injection 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.

Leave a comment

Your email address will not be published. Required fields are marked *

fifty four − = 48

One thought on “Second Order SQL Injection Explained with Example”