DB4 Pract (Insert)

You’re a trained “pentester.” Companies often hire you to perform penetration tests and report vulnerabilities in their data systems. Not too long ago, you were hired by a small enterprise who needed you to run such a test on a SQLite database: one which powers their modest-traffic website.

To succeed in this covert operation, you’ll need to…

• Alter the password of the website’s administrative account.
• Erase any logs of the above password change recorded by the database.
• Add false data to throw the company off of your trail. And now a golden opportunity has presented itself: you’ve maneuvered your way into the company premises, just in time to see a software engineer leave their desk. The engineer’s connection to the database is still open. You estimate you have 5 minutes before they come back. Ready?

Download the database: dont-panic.db

Run the following commands (cd Downloads, sqlite3 dont-panic.db) in your terminal to open the database. You can open the terminal on your mac by space+enter and write 'Terminal'.

cd Downloads

sqlite3 dont-panic.db

sqlite> .tables
items      orders     user_logs  users

sqlite> select * from users limit 5;
id  username  password
--  --------  --------------------------------
1   admin     02c77002a0c646684b3325959fe147b2
2   emily33   44bf025d27eea66336e5c1133c3827f7
3   zad28     185f20cc5599d33a1a94eb426112c78a
4   mario17   ccce73b2ef30ecf08c9cf0e802fa9149
5   ezra2     dbbe590bcc018ea53ca04caf89e0a98c

sqlite> select * from user_logs limit 5;
id  type    old_username  new_username  old_password  new_password
--  ------  ------------  ------------  ------------  --------------------------------
1   insert                admin                       e10adc3949ba59abbe56e057f20f883e
2   insert                emily33                     44bf025d27eea66336e5c1133c3827f7
3   insert                zad28                       185f20cc5599d33a1a94eb426112c78a
4   insert                mario17                     ccce73b2ef30ecf08c9cf0e802fa9149
5   insert                ezra2                       dbbe590bcc018ea53ca04caf89e0a98c

---

Schema

Afraid there’s not much time to explain the database’s schema. Remember you can access a SQLite database’s schema with .schema.

Specification

Write a sequence of SQL statements to achieve the following:

• Alter the password of the website’s administrative account, admin, to instead be “oops!” (UPDATE).
• Erase any logs of the above password change recorded by the database (DELETE).
• Add false data to throw others off your trail. In particular, to frame emily33, make it only appear—in the user_logs table—as if the admin account has had its password changed to emily33’s password (INSERT).

Also keep in mind that passwords are usually not stored “in the clear”—that is, as the plain characters that make up the password. Instead they’re “hashed,” or scrambled, to preserve privacy. Given this reality, you’ll need to ensure the password to which you change the administrative password is also hashed. Thankfully, you know that the passwords in the users table are already stored as MD5 hashes. You can generate quickly generate such hashes from plaintext at md5hashgenerator.com.

Clock’s ticking!

---

Solutions

1. Alter the password of admin to hashed 'oops'. First visit md5hashgenerator.com and hash the word "oops", then%

UPDATE users
SET password='02c77002a0c646684b3325959fe147b2'
WHERE username='admin';

1. Delete logs of password change from user_logs

DELETE FROM user_logs
WHERE type='update' AND old_username='admin';

1. Add false data to user_logs, admin password was changed from 'oops' to emily33's password (hashed).

INSERT INTO user_logs (type, old_username, new_username, old_password, new_password)
VALUES ('update', 'admin', 'admin', 'e10adc3949ba59abbe56e057f20f883e', '44bf025d27eea66336e5c1133c3827f7');