CODEWITHSUNDEEP
t-sqlsql-injectiondynamic-sql
Chris Herring
Chris Herring

Reputation: 3665

Does EXECUTE AS protect against SQL Injection?

I have a situation where I need to do this

CREATE PROCEDURE search_sp @condition varchar(8000) AS
    SELECT * FROM tbl WHERE @condition

If I add a user to the database that only has the 'db_datareader' role and then use execute as to switch context to that user for the purposes of running the select statement, would this then protect me from SQL injection? e.g.

DECLARE @cookie varbinary(100);
EXECUTE AS USER = 'restricted__user' WITH COOKIE INTO @cookie;

DECLARE @SQL AS NVARCHAR(MAX)
SET @SQL= 'SELECT * FROM tbl WHERE ' + @condition
EXEC sp_executesql @SQL

REVERT WITH COOKIE = @cookie;

Upvotes: 0

Views: 208

Answers (2)

tpdi
tpdi

Reputation: 35181

What happens when I pass "1=1" as condition?

Now you send back all rows in 'tbl'. If table is, say, users, I now have all user names and all passwords (hopefully they're hashed and salted...).

Upvotes: 0

Jeff Ferland
Jeff Ferland

Reputation: 18322

No, it won't. This will limit the queries that could be run if injected if the "EXECUTE AS USER" is even evaluated. It won't stop something like foo' or 1 = 1; /*... which can be a case where "EXECUTE AS USER" is never reached. Properly handling the input is required.

Upvotes: 2

Related Questions