I came across this gem the other day. Fortunately, I ran it locally first.
There are two tables. Person and AlienPerson. Lets create the tables and seed with some data.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
IF(OBJECT_ID('Person')) IS NOT NULL DROP TABLE Person GO CREATE TABLE Person ( PersonID INT IDENTITY(1,1) PRIMARY KEY, Name VARCHAR(MAX), Age INT null ) INSERT INTO Person (Name, Age) VALUES ('john', 11) INSERT INTO Person (Name, Age) VALUES ('Susy', 54) INSERT INTO Person (Name, Age) VALUES ('QBert', 23) INSERT INTO Person (Name, Age) VALUES ('Bernie', 75) INSERT INTO Person (Name, Age) VALUES ('Frank', 34) INSERT INTO Person (Name, Age) VALUES ('Julie', 66) INSERT INTO Person (Name, Age) VALUES ('tom', null) GO IF(OBJECT_ID('AlienPerson')) IS NOT NULL DROP TABLE AlienPerson GO CREATE TABLE AlienPerson ( AlienPersonID INT IDENTITY(1, 1) PRIMARY KEY, Age INT null ) INSERT INTO AlienPerson (Age) VALUES (12) |
Try to run a bad select statement:
|
1 2 3 4 5 6 7 |
--This will not be interpreted SELECT PersonID FROM AlienPerson WHERE age = 12 /* Msg 207, Level 16, State 1, Line 1 Invalid column name 'PersonID'. */ |
Here’s the baddie. This will delete all data in the Person table:
|
1 2 3 4 |
DELETE FROM Person WHERE PersonID IN ( --same as above query that was not interpreted!!!!! SELECT PersonID FROM AlienPerson WHERE age = 12 ) |
|
1 |
(7 row(s) affected) |
To get this to happen, the following must be true:
1. The SELECT field in the sub-query must be named the same as the field in the outer WHERE clause.
2. The inner WHERE clause must reference a field in the outer table. Otherwise, the DELETE will not run.
Using:
Microsoft SQL Server 2012 – 11.0.5343.0 (X64)
May 4 2015 19:11:32
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 10586: )