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 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.
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
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.
WHERE username='haider'--' and password='abc'
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.