SQL error in SELECT NULL, *, NULL, NULL
Background
I was trying to solve the fourth realistic mission on hackthissite.org and couldn't figure out exactly what SQL I should enter into the URL to fetch the list of emails. After wasting a few hours, I gave up and looked at a solution that presented this interesting line of code that should have been entered after selecting a query:
UNION ALL SELECT NULL, *, NULL, NULL FROM email
I understand what it does and why; the hacker needs to create a query with the same number of columns as the query it is connected to, and shift around * to make sure the emails are displayed. This is not my question.
Question
My question is why this code generates a syntax error in MySQL. After doing some tests, I found that this appears to be the correct query:
SELECT *, NULL, NULL, NULL FROM email
like this,
SELECT NULL, text, NULL, NULL FROM email
but for some reason this is not the case:
SELECT NULL, *, NULL, NULL FROM email
and it throws the syntax error "near" *, NULL, NULL FROM email. "I don't understand why this is. It looks like the query is only valid if * is the first column requested, no matter what the other columns are Is this a bug? Is this unique to MySQL (and the mission uses a different SQL variant)? Or am I completely misunderstanding it?
source to share
This is the documented behavior :
Using an unqualified * with other items in the select list may produce a parsing error. To avoid this problem, use a qualified tbl_name. * reference
Following the instructions. This should be parsed cleanly:
select NULL, email.*, NULL, NULL from email
source to share
Depending on how strictly you have to classify tables, you can only use one table and select literals in addition to all columns in that separate table.
For example, in Oracle, even this is not allowed: (as it happens the other way around)
SELECT *, null from email
However in Postgresql yes it does (both ways) http://sqlfiddle.com/#!15/20335/2/0
Qualifying columns usually come into play to avoid an ambiguity error when you use 2+ tables that have 1+ columns of the same name. Ambiguity errors are universal.
However, as far as parsing is concerned, there is a difference between databases. (when you only use one table and select all columns from that table, but also literals)
source to share