SQL Server How to prevent sql injection in dynamic sql

Question

I have an ASP.Net MVC application which use SQL 2012 as the database server. I have used Views,Stored Procedures (With/Without dynamic sql queries). I 've heard that dynamic sql can be a victim of sql injection.

Here is one of my sample dynamic query..

DECLARE @Username AS Varchar(100);
DECLARE @Password AS Varchar(100);

SET @Username = 'user1';
SET @Password = '123';

DECLARE @Query AS VARCHAR(MAX);

SET @Query = 'SELECT * FROM USERS WHERE Username ='+ @Username+ ' AND Password = '+@Password+';

EXEC(@Query)

How can I write this query preventing sql injection?

Kudos to you for taking the time to make this right!!! Far too many people just blindly toss that kind of code into production and their site gets hijacked. — Sean Lange
– Sean Lange, Commented Jul 10, 2015 at 20:17

David · Accepted Answer · 2015-07-10 19:06:11Z

7

The premise is essentially the same in SQL as it is in application code... Never directly concatenate input as code but instead treat it as a parameter. So if your query is something like this:

SET @Query = 'SELECT * FROM USERS WHERE Username = @Username AND Password = @Password';

Then you can execute it with parameters using sp_executesql:

exec sp_executesql @Query, N'@Username varchar(100), @Password varchar(100)', @Username, @Password

answered Jul 10, 2015 at 19:06

David

221k42 gold badges245 silver badges337 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server How to prevent sql injection in dynamic sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related