2

Hey i'm trying the hacker101 CTF's im still pretty new at this stuff

The CTF answer to the flag i'm trying to understand is https://github.com/testerting/hacker101-ctf/tree/master/micro-cms_v2/flag0

The important part of the error you get when you put a single ' in the username field if cur.execute('SELECT password FROM admins WHERE username=\'%s\'' % request.form['username'].replace('%', '%%')) == 0:

The solution to the flag is to input this into a username field

' UNION SELECT '123' AS password#

This is the statement I don't understand. I googled "SQL AS keyword" and I got something saying something along these lines, "The as keyword is a alias for a column or table" But '123' is a string, right? I got then got fustrated and moved on to the other thing I don't understand about the statement, the # at the end of password. I then also googled the "sql pound symbol" and I got it was referencing a temp table, which again, I don't really get..

Thanks!

3

The SQL statement executed when you input ' UNION SELECT '123' AS password# is:

SELECT password FROM admins WHERE username='' UNION SELECT '123' AS password#'

The pound sign is there so that the closing ' from the original format string, which is now trailing at the end of the query, does not tigger a syntax error. This is a MySQL extension to the SQL language and will not work on many other SQL servers, e.g., on PostgreSQL. There the standard -- is required.

The UNION keyword combines the result sets from two or more SELECT statements (by chaining them with UNION keywords). The first result, that from SELECT password FROM admins WHERE username='', is empty unless there is a row with an empty (but not NULL) username. The second one is the result set from SELECT '123' AS password, which is a single-row table with a single column called password. The AS password is not strictly necessary since MySQL uses the column names from the first statement when performing the union.

In SQL, selecting constants like '123' or constant expressions like LENGTH('123') replicates their value over the rows of the result set. If there is nothing in the query that produces more than one row, the result contains a single row.

  • Thank you for the in depth explanation, what I was looking for. – leak Oct 5 '19 at 15:15
2

In MySQL, # represents the start of a comment. This means the rest of the line will not be parsed as SQL. This is a common technique in SQL injection to force the trailing SQL after the injected SQL to be ignored.

In this case, by putting ' UNION SELECT '123' AS password# in the username field, it makes the command that's executed:

SELECT password FROM admins WHERE username='' UNION SELECT '123' AS password

This returns a row with '123' as the password column (regardless of the username provided), allowing you to log in with any username and the password '123'.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.