This is a working SQL-injection. What could the server side query string look like?

Question

This question is about a small detail in a larger school assignment. I have been struggling with this little problem for too long.

ABCTF arranged a Capture-the-Flag competition which was held earlier this year. One of the problems in this competition, was an SQL injection. This is a URL to the task description, and this is a URL to the login form which was to be injected. There is a published solution for this problem, and it looks like this:

Username: -1' union select 1,1,1,1 #

Password: 1

I need to figure out what the backend query string might look like. The simplest, most naive assumption would be

String query = "SELECT FROM user WHERE username = '" + inputA + "' AND password = '" + inputB + "';";
execute(query);

But this would produce

String query = "SELECT FROM users WHERE username = '-1' union select 1,1,1,1 #' AND password = '1';";
execute(query);

The most glaring problem with this, is that the quotes are unbalanced. Furthermore, I don't understand what output the SELECT statement would produce. As I've understood it, the "#" character is used to denote temporary tables in MS SQL Server, like "#temporary_table". I've played with other, simple statements as well, but nothing that would make sense.

Does anyone have an idea to what the backend query could look like? Any suggestion is appreciated.

Answer 1

The link states that the solution involves adding a whole new fake line via union where every value is a 1. The -1 as a username tries to make sure the query doesn't return any valid data as server side languages usually only compare the first row. An example would be the following code.

password = execute("SELECT password FROM users WHERE username = '{$username}'");
if( password != $password ) fail();
pass();

Which results in

password = execute("SELECT password FROM users WHERE username = '-1' union select 1,1,1,1 #'");
// no username called -1 means that the only row returned is the artificial row, so password = 1
if( password != 1 ) fail();
pass();

Keep in mind that there are millions of ways this code would work, ranging from only SQL to vulnerabilities in the web application. However, I found this approach most realistic.

Answer 2

In MySQL, the # symbol can be used as a comment marker. Quote marks in comments don't have to balance.

Not all SQL instances run on Microsoft databases.