Monday, August 12, 2019

SQL Server - How to prevent SQL Injection in Stored Procedure

What is SQL Injection : 
SQL injection is a type of injection attack, in which inserts SQL query via the input data from client to application and it can read sensitive data from database or modified database data or execute administration operation.

sp_executesql is system stored procedure, it is used to execute the SQL script like EXEC but sp_executesql allows for SQL statement to be parameterized as instead of embedding or injecting it into SQL code so it stops any SQL Injection possibility.

Here is an example of sp_executesql how to prevent SQL Injection:

CREATE PROCEDURE dbo.usp_validate_user
     @UserName as varchar(10),
     @Password as varchar(10)
AS
BEGIN
    
     declare @sqlString as varchar(max)
     SET @sqlString = 'Select id from dbo.Users Where UserName = @UserName and password = @Password'

     EXECUTE sp_executesql @sqlString,
     N'@UserName as varchar(10),  @Password as varchar(10)'
       @UserName,  @Password

END
GO


There are some other difference between Exec and sp_executesql

sp_executesql
EXEC  
Allow for statement to be parameterized
There is no option for parameterize column
No Risk of SQL Injection
High Risk of SQL Injection
Strongly typed parameters
No Strongly typed parameters
Performance Benefits - Cache an execution plane on first run
Create an execution plan for each run

No comments:

SQL Server - Identify unused indexes

 In this blog, we learn about the index usage information (SYS.DM_DB_INDEX_USAGE_STATS) and analyze the index usage data (USER_SEEKS, USER_S...