📜 Description

The purpose of this challenge is to perform an SQL injection that changes the email address CatchMe@gmail.com to McLovin@gmail.com for the fake McLovin user.

The challenge allows us to insert in the $pass variable.

Source Code:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

--run
CREATE TABLE superbad(
    id INTEGER PRIMARY KEY,
    country varchar(10),
    email varchar(30) UNIQUE,
    username varchar(30) UNIQUE,
    password varchar(70)
); -- I got the username first McLooovin!!
--run
INSERT INTO superbad(country, email, username, password) VALUES('USA', 'CatchMe@gmail.com', 'McLovin', '*******');

--run
INSERT INTO superbad(country, email, username, password) VALUES('Hawaii', 'McLovin@gmail.com', 'McLovin', $pass)

--return
SELECT * FROM superbad;

This code create an SQLite TABLE named superbad with 5 attributs :

• id INTEGER PRIMARY KEY
• country varchar(10),
• email varchar(30) UNIQUE,
• username varchar(30) UNIQUE,
• password varchar(70)

UNIQUE means that the table can’t have two users with same email or same username.

1

[0-9]|'|"|`|\s` chars are replaced by _IfYouCanGetADigit_YouCanGetACharacter_

🕵️ Proof of Concept

In SQLite and many other DMBS, It is possible to add after a query, a rule that allows if there is a conflict on an attribute (in our case, if username or email already exists in the table) to perform an operation on the line that is in conflict

In SQLite, it looks like this:

INSERT INTO superbad(country, email, username, password) VALUES(‘Hawaii’, ‘McLovin@gmail.com’, ‘McLovin’, $pass) ON CONFLICT(username) DO UPDATE SET email=”value”

Unfortunately, we cannot enter “, nor space.

How to bypass:

We can bypass space using /**/ in SQlite

To craft the string ‘McLovin@gmail.com‘ we can use the value of existing columns like username who contains McLoving wich is the beginning of our string. We now have to concat username with “@gmail.com”, this value is in the email columns but there is “CatchMe” so we can use the function substr.

substr() function allows part of a chain to be extracted

So we have to extract the value of email ( “CatchMe@gmail.com” ) from the 8th char to the end.

substr(chain, beginning, length)

substr(email,8,10) will extract “@gmail.com” of “CatchMe@gmail.com”

Unfortunately we cannot use [0-9] cause of the regex but we can use the value of the column id wich is an integer and is equal to 1 and we can use length(columns_name) wich will return an integer.

length() is used to calculate the length of a string

length(username) = 7

id = 1

substr(email,length(username)+id ,length(username)+id+id+id) is the same as substr(username,8,10)

username||substr(email,length(username)+id ,length(username)+id+id+id) will give use the following string “McLovin@gmail.com”

Final Payload :

1

sqlite_version())/**/ON/**/CONFLICT/**/(username)/**/DO/**/UPDATE/**/SET/**/email=username||substr(email,length(username)+id,length(username)+id+id+id)--

Result :

🔐 Remediation

SQL injection can be prevented by using parameterized queries (also known as prepared statements) instead of string concatenation within the query. The following code is vulnerable to SQL injection because the user input is concatenated directly into the query :

String query = “SELECT * FROM products WHERE category = ‘"+ input + “'"; Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(query); This code can be easily rewritten in a way that prevents the user input from interfering with the query structure:

PreparedStatement statement = connection.prepareStatement(“SELECT * FROM products WHERE category = ?"); statement.setString(1, input); ResultSet resultSet = statement.executeQuery(); Parameterized queries can be used for any situation where untrusted input appears as data within the query.

Conclusion

The challenge was really interesting as it demonstrated the utility of Built-In Scalar SQL Functions to bypass some rules.

Thanks to the author for this awesome challenge!