Why parameterized queries can prevent SQL injection

Most people know about SQL injection, and they know that SQL parameterized queries can prevent SQL injection and why it is not known by many to prevent injection.

First: We want to understand what SQL does when it receives an instruction:

here, the simple representation is: receive instructions, compile SQL build execution plan, select Execution plan, execute plan.

It may be a little different, but the approximate steps are as shown above.

then let's analyze why splicing SQL strings leads to the risk of SQL injection.

first create a table for users:

CREATE TABLE [dbo]. [Users] (    [Id] [uniqueidentifier] NOT NULL,       [USERID] [int.] NOT NULL,       [UserName] [varchar] () NULL,       [Password] [varchar] () 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 ');

Let's say we have a user login page with the following code:

Verify that the user is logged on with the following sql:

Select COUNT (*) from Users where Password = ' a ' and UserName = ' B '

This code returns the number of users that both password and username match, and if it is greater than 1, then the user exists.

This article does not discuss password policies in SQL, and does not discuss code specifications, mainly about why it is possible to prevent SQL injection, and ask some students not to tangle with some code, or the topic unrelated to SQL injection.

You can see the results of the execution:

This is the SQL statement for SQL profile tracking.

the injected code is as follows:

Select COUNT (*) from Users where Password = ' a ' and UserName = ' B ' or 1=1-'

Here someone set username for "B ' or 1=1–".

The actual execution of SQL becomes the following:

It is clear that SQL injection was successful.

Many people know that parameterized queries can avoid the injection problems that occur above, such as the following code:

Class Program {private static string connectionString = "Data source=.;i    Nitial catalog=test;integrated security=true ";           static void Main (string[] args) {Login ("B", "a");       Login ("B ' or 1=1--", "a"); } private static void Login (string userName, string password) {using (SqlConnection conn = new sqlconn Ection (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.var                 Char) {Value = password}, new SqlParameter ("@UserName", SqlDbType.VarChar)            {Value = UserName},}); Comm.           ExecuteNonQuery (); }    }}

The actual execution of SQL 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 the Users where Password = @Password and UserName = @UserName ', N ' @Password varchar (1), @UserName varchar (one) ', @Password = ' a ', @UserName = ' B ' or 1=1 —‘

You can see that parameterized queries do these things mainly:

1: Parameter filter, 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 wrote a SQL to indicate that the lookup password is a, and that the user name is the number of all users of B.

by injecting SQL, this SQL now represents a lookup (the password is a, and the user name is B) or the number of all users of 1=1.

You can see that the semantics of SQL have changed, why has it changed? Because the previous execution plan was not reused because the injected SQL statement was recompiled because the parsing was re-executed. So to ensure that the SQL semantics are the same, that is, I want to express the meaning of SQL is what I want to express, not after the injection of other meaning, you should reuse the execution plan.

If the execution plan cannot be reused, then there is a risk of SQL injection because the semantics of SQL may change and the queries expressed may change.

The following script can be used to query the execution plan in SQL Server:

DBCC freeproccacheselect 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) statement execution from Sys.dm_exec_cached_ Plans across 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;

in this article there is a paragraph:

Here The author has a sentence: "But this writing and direct to the SQL execution is no substantial difference"

any way to stitch SQL has the risk of SQL injection, so if there is no substantial difference, then using EXEC to execute SQL dynamically does not 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 execute SQL        //actual execution of query plans for (@UserID varchar (max)) SELECT * from Users (NOLOCK) where UserID in (1,2,3,4)        // Not expected (@UserID varchar (max)) EXEC (' SELECT * from Users (NOLOCK) where UserID in (' [email protected]+ ') ')        Comm. CommandText = "EXEC (' select * from the Users (NOLOCK) where UserID in (' [email protected]+ ') ')";        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 following SQL is executed:

EXEC sp_executesql n ' EXEC (' select * from Users (NOLOCK) where UserID in (' [' [email protected]+ ') ') ', N ' @UserID varchar (ma x) ', @UserID = ' 1,2,3,4 '

You can see that the SQL statement does not have a parameterized query.

If you set the UserID to "1,2,3,4"); Delete from users;--, the following SQL is executed:

EXEC sp_executesql n ' EXEC (' select * from Users (NOLOCK) where UserID in (' [' [email protected]+ ') ') ', N ' @UserID varchar (ma x) ', @UserID = ' 1,2,3,4); Delete from users;--'

don't assume that adding a @userid represents the ability to prevent SQL injection, actually executing the following sql:

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, there is basically no guarantee that the SQL you write will mean what you want to say.

This is like a child's fill in the blanks, find the password is (____) and the user name is (____) of the user.

Whatever value you fill in, that's what I'm saying.

at the end of the sentence: Because the parameterized query can reuse the execution plan, and if the execution plan is reused, SQL will not change the semantics of the expression, so you can prevent SQL injection, if you can not reuse the execution plan, it is possible to have SQL injection, stored procedures are the same reason, Because the execution plan can be reused.