CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Kevin M
Kevin M

Reputation: 5496

Delete Query inside Where clause

Is there any possibility to write delete query inside Where clause.

Example: 

Select ID,Name From MyTable Where ID IN(Delete From MyTable)

It may be crazy, but let me explain my situation. In our reporting tool, we are supporting to enter SQL where query. We will use our own Select and From Clause query and combine the user's where query input.

Example:

Select ID,Name From MyTable Where ("Query typed by user")

Here, user can type any kind of where query filter..

If he types like ID=100 our final query becomes like this 

Select ID,Name From MyTable Where (ID=100)

One of our customer asked us what will happen if anyone type the delete query as where query filter. he feels this may be the security hole..so we have tried that kind of possibility in our dev environment. But the sql returns error for the following query.

Select ID,Name From MyTable Where ID IN(Delete From MyTable)

So finally, my question is, is there any other possibility to write Delete Query inside Where clause or Select clause.. If it possible, how can I restrict it?

Upvotes: 0

Views: 1758

Answers (3)

Vulcronos
Vulcronos

Reputation: 3456

Yes. They can run a delete. They can type:

 1 = 1; DELETE FROM MY_TABLE;

Or even worse in some ways, (since you should have backups):

1 = 0 UNION SELECT SOCIAL_SECURITY_NUMBER, CREDIT_CARD_NUMBER, OTHER_SENSITIVE_DATA FROM MY_SENSITIVE_TABLE;

Now, in your case its hard to validate. Normally if you are just passing a value to filter on you can use parameterised sql to save yourself. You however also need to let the user select a column. In cases like these, usually we use a drop down to allow the user to select a predefined list of columns and then validate the column name server side. We give the user a text box to enter the value to match and then parameterise that.

Upvotes: 2

ZeRaTuL_jF
ZeRaTuL_jF

Reputation: 592

I believe what your customer is talking about is SQL injection, as long as you have taken appropriate methods to block other queries from running after your select statement is done, then you should have no problem in letting them type whatever it is that you want. From my experience there is no way to delete anything when you are doing a select statement. Just make sure you have query terminator characters so they don't write something like the following.

select column1,column2, from myTable where ID in (1,2); delete from my table

this would be a valid worry from your customer if you aren't taking proper steps to prevent sql injection from happening.

You could have your SQL reporting tool just not have update, or delete permission and just have it have Read permission. However, it is up to you guys have you handle your sql injection security.

Upvotes: 0

Perfect28
Perfect28

Reputation: 11317

It's not quite possible. But he can do something like this : 

Select ID,Name From MyTable Where (ID=100); (DELETE FROM MyTable Where 1 = 1)

by using ID=100); (DELETE FROM MyTable Where 1 = 1 instead of ID=100

Upvotes: 1

Related Questions