Why does parameterized query prevent SQL Injection? (1)

Many people know that SQL injection and SQL parametric query can prevent SQL injection, but not many know why it can prevent injection.

This article focuses on this issue. You may have seen this article in some articles. Of course, it is okay to check it.

First, we need to understand what SQL does after receiving a command:

For details, refer to the article: how to compile, recompile, and reuse execution plans of SQL Server.

Here, I simply say:Receive commands-> compile SQL to generate an execution plan-> select an execution plan-> execute an execution plan.

The specific steps may be different, but the general steps are shown above.

Next, let's analyze why the risk of SQL injection is caused by splicing SQL strings?

First create a table Users:

 
 

  
  
CREATE TABLE [dbo].[Users](  
  
  
 
  
  
[Id] [uniqueidentifier] NOT NULL,  
  
  
 
  
  
[UserId] [int] NOT NULL,  
  
  
 
  
  
[UserName] [varchar](50) NULL,  
  
  
 
  
  
[Password] [varchar](50) NOT NULL,  
  
  
 
  
  
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED   
  
  
 
  
  
(  
  
  
 
  
  
[Id] ASC 
  
  
 
  
  
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  
  
  
 
  
  
) ON [PRIMARY] 
 
 

Insert some data:

 
 

  
  
INSERT INTO [Test].[dbo].[Users]([Id],[UserId],[UserName],[Password])VALUES (NEWID(),1,'name1','pwd1');  
  
  
INSERT INTO [Test].[dbo].[Users]([Id],[UserId],[UserName],[Password])VALUES (NEWID(),2,'name2','pwd2');  
  
  
INSERT INTO [Test].[dbo].[Users]([Id],[UserId],[UserName],[Password])VALUES (NEWID(),3,'name3','pwd3');  
  
  
INSERT INTO [Test].[dbo].[Users]([Id],[UserId],[UserName],[Password])VALUES (NEWID(),4,'name4','pwd4');  
  
  
INSERT INTO [Test].[dbo].[Users]([Id],[UserId],[UserName],[Password])VALUES (NEWID(),5,'name5','pwd5'); 
 
 

Suppose we have a user login page, the Code is as follows:

The SQL statement used to verify user logon is as follows:

 
 

  
  
select COUNT(*) from Users where Password = 'a' and UserName = 'b'  
 
 

This code returns the number of users that both Password and UserName match. If it is greater than 1, it indicates that the user exists.

This article does not discuss password policies in SQL, nor code standards, mainly about why SQL injection can be prevented. Please do not tangle with some code or topics unrelated to SQL injection.

The execution result is displayed:

This is the SQL statement tracked by the SQL profile.

The injection code is as follows:

 
 

  
  
select COUNT(*) from Users where Password = 'a' and UserName = 'b' or 1=1—' 
 
 

Here, someone sets UserName"B 'or 1 = 1 -".

The actual executed SQL statement is changed to the following:

We can see that SQL injection is successful.

Many people knowParameterized QueryThe above injection problems can be avoided, such as the following code:

 
 

  
  
Class Program
  
  
{
  
  
Private static string connectionString = "Data Source =.; Initial Catalog = Test; Integrated Security = True ";
  
  
 
  
  
Static void Main (string [] args)
  
  
{
  
  
Login ("B", "");
  
  
Login ("B 'or 1 = 1 --", "");
  
  
}
  
  
 
  
  
Private static void Login (string userName, string password)
  
  
{
  
  
Using (SqlConnection conn = new SqlConnection (connectionString ))
  
  
{
  
  
Conn. Open ();
  
  
SqlCommand comm = new SqlCommand ();
  
  
Comm. Connection = conn;
  
  
// Add a parameter for each piece of data
  
  
Comm. CommandText = "select COUNT (*) from Users where Password = @ Password and UserName = @ UserName ";
  
  
Comm. Parameters. AddRange (
  
  
New SqlParameter [] {
  
  
New SqlParameter ("@ Password", SqlDbType. VarChar) {Value = password },
  
  
New SqlParameter ("@ UserName", SqlDbType. VarChar) {Value = userName },
  
  
});
  
  
 
  
  
Comm. ExecuteNonQuery ();
  
  
}
  
  
}
  
  
}
 
 

The actual executed SQL statement is as follows:

 
 

  
  
exec sp_executesql N'select COUNT(*) from Users where Password = @Password and UserName = @UserName',N'@Password varchar(1),@UserName varchar(1)',@Password='a',@UserName='b' 
  
  
 
  
  
exec sp_executesql N'select COUNT(*) from Users where Password = @Password and UserName = @UserName',N'@Password varchar(1),@UserName varchar(11)',@Password='a',@UserName='b'' or 1=1—' 
 
 

We can see that parameterized queries mainly do these things:

1: filter parameters. You can see @ UserName = 'B' or 1 = 1 -'

2: execution plan reuse

Because execution plans are reused, SQL injection can be prevented.

First, analyze the nature of SQL injection,

The user writes an SQL statement to indicate that the query password is a and the user name is the number of all users of B.

By injecting an SQL statement, this SQL statement indicates the number of all users (the password is a and the user name is B) or 1 = 1.

We can see that the meaning of SQL has changed. Why ?, Because the previous execution plan was not reused, the injected SQL statement was re-compiled because the Syntax Parsing was re-executed. Therefore, to ensure that the SQL semantics remains unchanged, that is, the execution plan should be reused if I want to express SQL.

If execution plans cannot be reused, there is a risk of SQL injection, because the meaning of SQL statements may change, and the expressed query may change.

You can use the following script to query execution plans in SQL Server:

 
 

  
  
DBCC FreeProccache
  
  
 
  
  
Select total_elapsed_time/execution_count average time, total_logical_reads/execution_count logical read,
  
  
Usecounts reuse times, SUBSTRING (d. text, (statement_start_offset/2) + 1,
  
  
(CASE statement_end_offset
  
  
WHEN-1 then datalength (text)
  
  
ELSE statement_end_offset END
  
  
-Statement_start_offset)/2) + 1) execute the statement from sys. dm_exec_cached_plans
  
  
Cross apply sys. dm_exec_query_plan (a. plan_handle) c
  
  
, Sys. dm_exec_query_stats B
  
  
Cross apply sys. dm_exec_ SQL _text (B. SQL _handle) d
  
  
-- Where a. plan_handle = B. plan_handle and total_logical_reads/execution_count> 4000
  
  
Order by total_elapsed_time/execution_count DESC;
 
 

Blog has an article: SQL Server parameterized query where in and like implementation