Why parameterized queries can prevent SQL injection

Many 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 .

This article is mainly about this question, perhaps you have seen this piece of content in some articles, of course, look at the same.

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

Specific details can be viewed in the article: SQL Server compilation, recompilation and execution plan reuse principle

Here, I simply represent: 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:

CREATETABLE [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_lock S = on ) on [PRIMARY]) on [PRIMARY]         

Insert some data:

INSERTinto [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:

' 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:

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{PrivateStaticString connectionString =The Data source=.;i Nitial catalog=test;integrated security=true ";Staticvoid Main (String[] args) {Login ("B","A"); 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; //adds 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 execution of SQL is as follows:

EXEC sp_executesql n' select COUNT (*) from Users where Password = @Password and UserName = @UserName ', N' @Password V Archar (1), @UserName varchar (1) ', @Password =' A ', @UserName =' B '   

EXEC sp_executesql n' select COUNT (*) from Users where Password = @Password and UserName = @UserName ', N' @Password V Archar (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 the meaning of finding (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 stat Ement_end_offset when-1 then datalength (text) ELSE STA Tement_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 Bcross apply Sys.dm_exec_sql_text (b.sql_handle) d--where a.plan_handle=b.plan_ Handle and total_logical_reads/execution_count>4000ORDER by total_elapsed_time/execution_count DESC;      

Blog Park has an article: SQL Server parameterized query where in and like implementation of the detailed

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:

Privatestatic 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 the query plan as (@UserIDvarcharMax))SELECT *From Users (NOLOCK)where UserIDIn (1,2,3,4)//not expected (@UserIDvarchar (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.Paramete Rs. 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 (max) ', @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;--

, then the SQL that executes is the following:

EXEC sp_executesql N' EXEC ('select * from Users (NOLOCK) where UserID in ('[email protected]+ ') ') ' , N' @UserID varchar (max) ', @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.

Finally, a summary: 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 it can prevent SQL injection, if the execution plan can not be reused, there is a possibility of SQL injection,
The same is true for stored procedures, because the execution plan can be reused.

Why parameterized queries can prevent SQL injection