Second Order SQL Injection Explained with Example

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:-

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”.

signed in

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.

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’ –“.

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:-

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.