I am completely new to SQL and wanted to solve this problem. Tried reading the information on w3schools and they do not show any syntax with -- in the beginning. Please try and understand that I am a complete noob at this point and need some insights.
If there are any hints which could help me out, all I need is some more information to attempt it again and I will update this thread again and will keep on doing that till I get to the answer.
Question
Professor Lockhart claims that his chamber has the best security measures in place, and constantly brags about it to Professor Flitwick. Flitwick, getting annoyed with this, decides to use his intellect and put the chamber's defenses to the test. He begins looking for security vulnerabilities in his chamber. Pretty soon, he discovers that there is a flaw in the screening mechanism of the chamber, and it does not correctly screen all the people trying to enter (i.e., it does not sanitize SQL inputs, which means it is prone to SQL injection attacks).
The chamber entry door works very similarly to a website that contains a login form. On entering the username + password details, the user gains access to the chambers. The chamber's code for obtaining the user details (which is used for verification), based on information entered in the login form is the following:
user_details = "SELECT * FROM users WHERE username='" + uname + "' AND password='" + passwd + "'"
where uname is the username entered in the login form, and passwd is the corresponding password.
The above is prone to SQL injection attacks, as we can set the passwd field to something like:
password' OR 1=1
As a result, the database server runs the following SQL query:
SELECT * FROM users WHERE username='username' AND password='password' OR 1=1'
Leading to the second part of the AND clause being always evaluated as True, even if the password is wrong since 1=1 always. This enables anyone to get past the screening mechanism, without even knowing the right password.
Using the above login form, what will be the input to the password field form so that Professor Flitwick can delete Professor Lockhart's own record from the database, thereby barring him from entering his own chambers?
Note: Each - in the code snippet below denotes a blank of 1 character. Fill in the blanks. Use standard SQL syntax (single quotes for strings, etc). Assume Professor Lockhart's username is 'Lockhart' (without the quotes). Do not use backticks (`) anywhere.
Replace the dashes appropriately. Do not enter whitespace as a character anywhere in the blanks.
echo "-- DELETE FROM ----- WHERE -------------------;"
I tried attempting it this way:
echo " DELETE FROM users WHERE username='Lockhart';"
echo "'' DELETE FROM users WHERE username=Lockhart'';"
echo "DELETE FROM users WHERE username='Lockhart';"
You're almost there - all you're missing is one key part - terminate the select statement.
Let's summarize the question to only the essential parts:
user_details = "SELECT * FROM users WHERE username='" + uname + "' AND password='" + passwd + "'"
The requirement: Delete a record for a the user 'Lockhart'.
The tools: SQL Injection, Standard SQL syntax.
The template to fill: "-- DELETE FROM ----- WHERE -------------------;"
The first thing you need to do is end the select statement, since you can't do a select and a delete in a single statement - this means that you first need to close the string literal using an apostrophe ('), and then end the statement using the standard SQL statement terminator which is a semicolon (;).
Then, you want to complete the delete statement, which you've done correctly in your attempts - no problem there.
So the answer you should put in is this:
-- DELETE FROM ----- WHERE -------------------;
'; DELETE FROM users WHERE username='Lockhart';