SQL Server How to prevent SQL injection in dynamic sql
I have an ASP.Net MVC application that uses SQL 2012 as the database server. I have used Views stored procedures (with / without dynamic sql queries). I've heard that dynamic sql can fall victim to sql injection.
Here is one of my dynamic queries.
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?
+3
source to share
1 answer
The premise is essentially the same as in application code as it is in application code ... Never directly concatenate input as code, but instead treat it as a parameter. So if your request looks something like this:
SET @Query = 'SELECT * FROM USERS WHERE Username = @Username AND Password = @Password';
Then you can execute it with parameters with sp_executesql
:
exec sp_executesql @Query, N'@Username varchar(100), @Password varchar(100)', @Username, @Password
+7
source to share