CODEWITHSUNDEEP
sql-serversql-injection
tarzanbappa
tarzanbappa

Reputation: 4958

SQL Server How to prevent sql injection in dynamic sql

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?

Upvotes: 3

Views: 1511

Answers (1)

David
David

Reputation: 218828

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

Upvotes: 7

Related Questions