Confusing SQL Error In SELECT NULL, *, NULL, NULL

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

Solution 1:

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

Solution 2:

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)