Confusing SQL error in SELECT NULL, *, NULL, NULL

Question

The Background
I was trying to solve the fourth realistic mission in hackthissite.org, and couldn't figure out exactly what SQL I should inject into the URL to retrieve the list of emails. After wasting a few hours, I gave up and looked at a solution, which gave this interesting line of code, which was to be injected after a select query:

UNION ALL SELECT NULL, *, NULL, NULL FROM email

I understand what this does and why; the hacker needs to create a query that has the same number of columns as the query it's being merged with, and shifted around the * to make sure the emails are displayed. That's not my question.

The Question
My question is why that code throws a syntax error in MySQL. After doing a few tests, I've found that apparently this is a valid query:

SELECT *, NULL, NULL, NULL FROM email

as this this,

SELECT NULL, text, NULL, NULL FROM email

but for some reason, this isn't:

SELECT NULL, *, NULL, NULL FROM email

and throws a syntax error " near '*, NULL, NULL FROM email' ". I fail to understand why this is so. It seems like the query is only valid if * is the first column requested, regardless of what the other columns are. Is this a bug? Is it a feature unique to MySQL (and the mission uses a different variation of SQL)? Or am I misunderstanding this completely?

Answer 1

It may vary by database as to how strictly you must qualify the tables when only using one table and selecting literals in addition to all the columns on that single table.

For instance in Oracle, even this is invalid: (as is the other way around)

SELECT *, null from email

However in Postgresql, yes, it is valid (both ways) http://sqlfiddle.com/#!15/20335/2/0

Qualifying columns normally comes into play to avoid an ambiguity error when you are using 2+ tables that have 1+ columns of the same name. Ambiguity errors are universal.

However as far as parsing, there is variation between databases. (when you are using just one table, and selecting all columns from that table, but literals in addition)

Answer 2

This is documented behavior:

Use of an unqualified * with other items in the select list may produce a parse error. To avoid this problem, use a qualified tbl_name.* reference

Just following the instructions. This should parse cleanly:

 select NULL, email.*, NULL, NULL from email