Why does parameterized query prevent SQL injection?

Many people know SQL injection andSQL parameterized query can prevent SQL Injection, YesWhy can injection be prevented?But not many people know it.

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 command-> compile SQL to generate execution plan-> select execution plan-> execute execution plan.

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

 

Next, let's analyzeWhy is the risk of SQL Injection 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 to"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 --", "a");} 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 comm for each piece of data. 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:Parameter Filtering, 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 FreeProccacheselect total_elapsed_time/execution_count average time, total_logical_reads/execution_count logical reads, 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) statement execution from sys. dm_exec_cached_plans guest SS apply sys. dm_exec_query_plan (. plan_handle) c, sys. dm_exec_query_stats bcross apply sys. dm_exec_ SQL _text (B. SQL _handle) d -- where. plan_handle = B. plan_handle and total_logical_reads/execution_count & gt; 4000 order by total_elapsed_time/execution_count DESC;

 

There is an article on the red/Black Alliance: SQL Server parameterized query where in and like implementation

 

This article contains the following paragraph:

 

The author has a sentence: "But there is no substantial difference between this writing method and direct SQL Execution"

Any join SQL method has the risk of SQL injection, so if there is no substantial difference, dynamic execution of SQL using exec cannot prevent SQL injection.

 

For example, the following code:

Private static void TestMethod () {using (SqlConnection conn = new SqlConnection (connectionString) {conn. open (); SqlCommand comm = new SqlCommand (); comm. connection = conn; // use exec to dynamically Execute SQL // The actual query plan is (@ UserID varchar (max) select * from Users (nolock) where UserID in, 3, 4) // unexpected (@ UserID varchar (max) exec ('select * from Users (nolock) where UserID in ('+ @ UserID +') comm. commandText = "exec ('select * from Users (nolock) where UserID in ('+ @ UserID +')"; comm. parameters. add (new SqlParameter ("@ UserID", SqlDbType. varChar,-1) {Value = "1, 2, 3, 4"}); // comm. parameters. add (new SqlParameter ("@ UserID", SqlDbType. varChar,-1) {Value = "1, 2, 3, 4); delete from Users; --"}); comm. executeNonQuery ();}}

 

The executed SQL statement is as follows:

exec sp_executesql N'exec(''select * from Users(nolock) where UserID in (''+@UserID+'')'')',N'@UserID varchar(max) ',@UserID='1,2,3,4'

The SQL statement is not parameterized.

If you set UserID"

1, 2, 3, 4); delete from Users ;--

", The executed SQL is as follows:

exec sp_executesql N'exec(''select * from Users(nolock) where UserID in (''+@UserID+'')'')',N'@UserID varchar(max) ',@UserID='1,2,3,4); delete from Users;--'

 

Do not consider adding @ UserID to prevent SQL injection. The actual executed SQL statement is as follows:

 

Any dynamic execution of SQL has the risk of injection, because dynamic means that the execution plan is not reused, and if the execution plan is not reused, basically, the meaning of the SQL statement you write is what you want to express.

This is like a blank question when I was a child. Find the user whose password is (____) and whose username is.

Whatever value you fill in, this is what I mean.

Finally, we can conclude that parameterized queries can reuse execution plans. If execution plans are reused, the semantics to be expressed in SQL statements will not change, so SQL injection can be prevented, if the execution plan cannot be reused, SQL injection may occur, and the stored procedure is the same, because the execution plan can be reused.