Tags: sqli insert update 

Rating: 0

`template.sql` was provided with the following contents

```
DROP TABLE users;
CREATE TABLE users (id INT AUTO_INCREMENT, name VARCHAR(55), username VARCHAR(55) UNIQUE, password VARCHAR(55), PRIMARY KEY (id));
INSERT INTO users (name, username, password) VALUES ('root', 'root', '{{env:ADMIN_PASSWORD}}');
```

We know that our goal is to either retrieve root's password or hack into root's account. After testing, the register functionality. It was evident that password parameter was vulnerable to SQL Injection on `INSERT` statement. The website displayed `name` of the user after they login. So, first idea was to retrieve from database and dump at the `name` field. Hence, our initial test payload is

```
'),((select version()),'ja','oo
```
This will register a username with `ja:oo` credentials and once you login you will see `5.7.21` which is evaluated mysql's version()

Subsequent idea was to select the password from users table for `root` and dump it. Hence, payload looks as

```
'),((select password from users ASy limit 1),'ja','oo
```

Locally, this will work fine (assuming root has the id=1). But remotely it fails. Hence, it looks the setup is bit different compared to our local setup. 200 points worth challenge; they won't give it that easy ! After subsequent attempts, it looked like `REVOKE` on `SELECT` for tables. Hence, general SELECT'ion attempts are futile.

Next idea, is to overtake root's account. Goal is to `UPDATE` the root's password. As we already know that `username` field is UNIQUE. We can utilise[ ON DUPLICATE](https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html) .

Hence, the payload is (fits the char limit too)

```
'),('','root','z')ON DUPLICATE KEY UPDATE password='l'#
```

This will look if any `username` has already `root` entry and if it finds then it UPDATE's the password to `l` . Weird Weird SQL. Then you may login with `root:l` and enjoy the flag

`midnight{dub_i_dub_i_dub_i_dubdubdub_dub_i_dub_i_dub_i_yeah_yeah}`

tl;dr [version](https://twitter.com/aaditya_purani/status/985570104707637248)