Some queries regarding SQL injection

Question

[I'm not really sure if I should be posting this on SO or here. I think my questions might be answered if I have had a good experience with MySQL. But, I don't. So probably, most of you will definitely want to tell me to read a good SQL book (if so, do tell me which books to read as the answer to my questions won't be directly answered in any book. So, I'd definitely want to read a book which answers such questions). However, currently I want to get my hands dirty with web app pen-testing and I can't do so without directly diving into it. Also, experience and knowledge comes with practice. :) So please bear with me.]

I am new to Web app pen-testing and am practicing SQL injection on Mutillidae. I'm at the user info login page and I gave the input as ' or 'a' = 'a. Worked. I got all the records from the database.

Now I want find out how many columns are there in the database and the column types (just for practice). I wanted to try out the "ORDER BY" method and the "UNION SELECT 1, null,null--" method metioned on the OWASP's testing for SQL injection web page. So I gave my input as ' or 'a' = 'a' ORDER BY 3-- (please note there is a space after --. I'm not sure how to put it in the pre-formatted text) as there were 3 fields returned in my previous attack. Worked again. So I played around with the query. I tried 1,2 instead of 3 in the 2nd query and they worked fine. The only change was that the order of the records returned were different. However the query also worked when I gave the input query as ' or 'a' = 'a' ORDER BY 4-- and ' or 'a' = 'a' ORDER BY 5--. However, I still see 3 fields in the output. Also, the query didn't work when I gave ' or 'a' = 'a' ORDER BY 6-- or any higher number than 6. Instead I got an error. My questions:

1. Why does the query work when I give 4 or 5 as the input? Does that mean there are 5 columns? If so, why do the output records contain only 3 fields?

2. Why does the query not work when I give any value >= 6?

3. I also noticed that the space is necessary after the --. If I don't give a space, the SQL injection won't work. Why do I need a space?

4. Why isn't a ; (semi-colon) necessary in my injected query. As far as I know MySQL, it doesn't execute the query unless I give it a semi-colon. However, my query executes here perfectly without a semi-colon. Why?

Answer 1

Why does the query work when I give 4 or 5 as the input? Does that mean there are 5 columns? If so, why do the output records contain only 3 fields?

Yes, the query would fail if you want to sort by a column which doesn’t exist. So as column position 5 works but 6 doesn’t, there are only 5 columns selected. Why only 3 of them are visible to the user is up to the developer. Maybe he/she selected all columns (e. g., *) instead of just the necessary ones or only the 3 are meant to be visible to the user.

---

Why does the query not work when I give any value >= 6?

See above.

---

I also noticed that the space is necessary after the --. If I don't give a space, the SQL injection won't work. Why do I need a space?

MySQL requires a whitespace or control character after -- as stated on Comment Syntax:

In MySQL, the “-- ” (double-dash) comment style requires the second dash to be followed by at least one whitespace or control character (such as a space, tab, newline, and so on). This syntax differs slightly from standard SQL comment syntax, as discussed in Section 1.8.2.5, “'--' as the Start of a Comment”.

---

Why isn't a ; (semi-colon) necessary in my injected query. As far as I know MySQL, it doesn't execute the query unless I give it a semi-colon. However, my query executes here perfectly without a semi-colon. Why?

The semicolon is only required as separator between statements. It is also used to terminate the statement input on the mysql console.