SELECT Statement Question

Question

Dear All,

A VB.NET book discussed that the following sql statement would run and delete the systables data.

Dim query as String = "DELETE * FROM systables"

Dim SQL as String = "SELECT * FROM myTable WHERE field LIKE '" & query & "%'"

Can the WHERE clause be used as more than just a filter and act as a sub query that is executed first? I am sorry if this is such a basic question for the group.

Thanks again.

Alastair MacFarlane

Answer 1

Dim query as String = "DELETE * FROM systables"

Throw away that book quick! You can lose your job for running statements like that. I am simply shocked why a book author would suggest that.

You have to use the WHERE clause with a SELECT. You cannot use it by itself.

If you make subqueries (with or without WHERE) into derived tables, they can be used like tables:

http://www.sqlusa.com/bestpractices/derivedtables/

Any query can be made into derived table a simple way: (SELECT .... ) x , where x is a table alias.

You can even assign colum aliases to derived tables. P is the derived table.

SELECT * FROM 
(SELECT ProductID, Name, ListPrice
FROM Production.Product
WHERE ListPrice > 0 ) P (ID, ProductName, Price)
ORDER BY P.ProductName;

---

Kalman Toth SQL 2008 GRAND SLAM
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 2

try first

SELECT * FROM myTable join systables
on myTable.field = systables.field

after

delete from systables

PD: the statement DELETE not use *, directly execute DELETE FROM TABLA:

PD: systables is a user table created by you? or is SYS.Tables (system table)

Answer 3

The resulting SQL of the VB code you posted is

SELECT * FROM myTable WHERE field LIKE 'DELETE * FROM systables%'

which will not delete anything. However, consider:

Dim query as String = "' DELETE FROM systables --"

Dim SQL as String = "SELECT * FROM myTable WHERE field LIKE '" & query & "%'"

And this code will indeed delete all rows in systables. This is a typical example of SQL injection.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

Dear All,

I apologise for the confusion. If you were to join the 2 statements together like:

DECLARE @MySQL nvarchar(25);
SET @MySQL = "DELETE * FROM systables";

"SELECT * FROM myTable WHERE field LIKE '" &  @MySQL & "%'"

Apologies if the syntax is not exactly correct but would this delete the systables data? My question is whether the DELETE statement would run before the remainder of the SQL statement if it were placed after the WHERE statement? I would agree that you would never do it but would it work.

This is my interpretation of what the author meant.

Thanks again and I appreciate the reposnses and sorry if I have given you more work.

Alastair MacFarlane (SQL newbie!)

Answer 5

Erland,

Thanks for that. I was not sure that even after correcting the syntax I did'nt think the DELETE satament would run.

You have answered the question.

Thanks again.

Alastair MacFarlane