Dynamic SQL-reprint Learning

Dynamic SQL

Bad:
1. dynamic SQL is very dangerous, because dynamic SQL generally constructs your entire SQL statement based on user input, that is, we usually see some parameters involved in the SQL statement, average hackers will also
Using this empty box for injection is my simplest understanding of injection. I used some code written in hand to change the structure of the original SQL statement.
2. Dynamic statements are difficult to implement. quotation marks and parameter conversion Errors often occur.

Good news:
It can process query tasks that cannot be processed by static SQL statements. for example, when you convert rows to columns, you do not know which columns you want to convert to columns. in this case, you don't need to worry about using dynamic resources.

How to execute dynamic statements: exec (execute) and sp_executesql
The latter provides input/output interfaces. The latter is more likely to reuse the execution plan. The latter can write safer code;
Therefore, in no special case, we generally use sp_executesql... Avoid
Basic knowledge about the application of these two passwords in dynamic Syntax: (http://blog.csdn.net/roy_88/article/details/3020586)

-- Dynamic statement syntax /*********************************** **************************************** **************************************** * *********************************** dynamic statement Syntax: exec/sp_executesql syntax Organizer: Chinese style (Roy) Date: 2008.06.06 *************************************** **************************************** **************************************** * *****************************/dynamic statement Syntax: -- change the query table in method 1 to dynamic select * From sysobjects exec ('select ID, name from sysobjects ') exec sp_executesql n' select ID, name from sysobjects '-- if n is added as Unicode -- Method 2: When field name, table name, database name, and so on are used as variables, dynamic SQL declare @ fname varchar (20) set @ fname = 'id' exec ('select' + @ fname + 'from sysobjects where' + @ fname + '= 5') Declare @ s varchar (1000) set @ s = n'select' + @ fname + 'from sysobjects where' + @ fname + '= 5' exec sp_executesql @ s -- an error will be reported: declare @ s nvarchar (1000) -- changed to nvarchar set @ s = n'select' + @ fname + 'from sysobjects where' + @ fname + '= 5' exec sp_executesql @ s -- succeeded -- Method 3: input parameter declare @ I int, @ s nvarchar (1000) set @ I = 5 exec ('select ID, name from sysobjects where id = '+ @ I) set @ s = 'select ID, name from sysobjects where id = @ I 'exec sp_executesql @ s, n' @ I int ', @ I -- add n to the input parameter here -- Method 4: output parameter declare @ I int, @ s nvarchar (1000) set @ s = 'select @ I = count (1) from sysobjects '-- use exec ('Clare @ I int' + @ s + 'select @ I') -- add the entire statement to the string for execution -- use sp_executesql exec sp_executesql @ s, n' @ I int output', @ I output -- N select @ I -- Method 5 must be added to the output parameter here: Input and Output -- use sp_executesql declare @ I int, @ con int, @ s nvarchar (1000) set @ I = 5 select @ s = 'select @ con = count (1) From sysobjects where ID> @ I 'exec sp_executesql @ s, n'@ con int output, @ I int', @ con output, @ I select @ con -- use exec declare @ I int, @ s nvarchar (1000) set @ I = 5 select @ s = 'descare @ con int select @ con = count (1) From sysobjects where ID> '+ rtrim (@ I) + 'select @ con 'exec (@ s)

Notes:

 
1. Exec

A. Do not use case or some functions such as rtrim () and quotename () in Exec () brackets (). This is not allowed

[C-sharp]
View plaincopyprint?

1. Declare @ schemaname as nvarchar (128), @ tablename as nvarchar (128 );
2. Set @ schemaname = n 'dbo ';
3. Set @ tablename = n'order details ';
4. Exec (n' select count (*) from'
5. + Quotename (@ schemaname) + N'. '+ quotename (@ tablename) + N ';');

 
-- Errors will occur here.

B. The local variables defined in the batch processing cannot be accessed in dynamic statements. This is the so-called exec does not provide interfaces.
 

[C-sharp]
View plaincopyprint?

1. Declare @ I as int;
2. Set @ I = 10248;
4. Declare @ SQL as varchar (52 );
5. Set @ SQL = 'select * From DBO. orders where orderid = @ I ;';
6. Exec (@ SQL );

/*
The scalar variable "@ I" must be declared ".
*/
--- The processing method is to concatenate this variable into a string.

[C-sharp]
View plaincopyprint?

1. Declare @ I as int;
2. Set @ I = 10248;
4. Declare @ SQL as varchar (52 );
5. Set @ SQL = 'select * From DBO. orders where orderid ='
6. + Rtrim (@ I) + N ';';
7. Exec (@ SQL );

 
------ Unfortunately ----- this operation is not safe, and after variables are connected in series, SQL creates a new execution plan for each unique query string.
-- For example, if you @ I 10348 10349 10897 and so on, it will generate three execution plans, which is a waste of costs.

C. Advantages of Exec
Exec previously supports longer code than sp_executesql. Because one is varchar (8000) and the other is nvarchar (4000), and exec (@ S1 + @ S2 + @ S3) here
Every variable in the brackets can be as huge as varchar (8000). Unfortunately, the appearance of sql2005 varchar (max) and nvarchar (max) completely changed this awkwardness.
Tedious method. A nvarchar (max) can contain 2 billion characters .... is 2 billion... even nvarchar (max) requires million RMB .. do you still need to splice them ?..... --|
So try to use Max to define it for friends who use 05.


2. sq_executesql

A. It provides a powerful excuse-to avoid the problem of concatenating variables, you can also output the look
Declare @ I as int;
Set @ I = 10248;
Declare @ SQL as nvarchar (46 );
Set @ SQL = 'select * From DBO. orders where orderid = @ OID ;';
----- Please note that the above @ OID should not be directly uploaded directly after being converted into a string.
Exec sp_executesql
@ Stmt = @ SQL, -- this is equivalent to a query body in the stored procedure, which is what follows the
@ Params = n' @ OID as int', -- this is equivalent to the input parameter Declaration of the stored procedure, which is something before the
@ OID = @ I; -- this is equivalent to the parameter list after you call the stored procedure.
------ Here, if you assign @ ID three different values 10348 10349 10897, it only produces one execution plan ~ Only one is not three !!! Cost efficiency

B. It can be checked through input parameters to prevent SQL injection.
In my understanding, it is determined by the length type of the input parameter.

3. Interaction between the session environment and dynamic statements
The following example illustrates the problem:

[C-sharp]
View plaincopyprint?

1. Use northwind;
2. Declare @ dB as nvarchar (258 );
3. Set @ DB = quotename (n'pubs ');
4. Exec (N 'use' + @ dB + ';');
5. -- Although the database name is changed in the dynamic batch processing, the db_name () function does not affect the external batch processing.
6. Select db_name ();
7. Go
8. /*
9. ---------------
10. Northwind
11. */
12. Use northwind;
13. Declare @ dB as nvarchar (258 );
14. Set @ DB = quotename (n'pubs ');
15. Exec (N 'use' + @ dB + N' select db_name ();');
16. -- The db_name () function is called in the internal processing of dynamic batch processing, which is of course effective.
17. Go
18. /*
19. -----------------
20. Pubs
21. */
22. Use northwind;
23. Declare @ dB as nvarchar (258 );
24. Set @ DB = quotename (n'pubs ');
25. Exec (N 'use' + @ dB + N'; Exec (''select db_name ();'');');
26. -- Db_name () is called internally in the dynamic batch processing, which indicates that dynamic processing can affect its internal level.
27. /*
28. -----------------
29. Pubs
30. */

 
----- To sum up, dynamic batch processing is only useful at the level of dynamic processing itself and its internal level, but it cannot affect the external batch processing ---------

4. Dynamic statements and temporary tables

[C-sharp]
View plaincopyprint?

1. Use tempdb
2. Go
3. Exec ('create table # (a int) insert # select 1 ')
4. -- Temporary tables created inside a dynamic statement are invisible outside
5. Select * from #
6. /*
7. The object name '#' is invalid.
8. */
9. Use tempdb
10. Go
11. Exec ('create table # (a int) insert # select 1; Exec (''select * from #'')')
12. -- The temporary table created inside the dynamic statement is visible at the internal level of the dynamic statement, including its own level.
13. /*
14. A
15. -----------
16. 1
17. */

5. Dynamic statements and temporary variables
Use tempdb
Go
Exec ('maid @ s int select isnull (@ s, 1 )')
-- Executable
Exec ('Clare @ s int; Exec (''select isnull (@ s, 1 )'')')
-- Temporary variables created inside a dynamic statement are invisible at the internal level.
/*
The scalar variable "@ s" must be declared ".
*/

6. SQL Injection
In layman's terms, it is To concatenate malicious code into dynamic statements to do bad things---|

A. Client Input:
Create Table DBO. Users
(
Username varchar (30) not null primary key,
Pass varchar (16) not null
);

Insert into users (username, pass) values ('user1', '123 ');
Insert into users (username, pass) values ('user2', '123 ');
Go

-- The following code is usually used in client programs to query and verify the user's identity.
/*
SQL = "select count (*) as CNT from DBO. Users where username = '"_
& Inputusername & "'and pass ='" & inputpass &"';"

Inputusername = "user1"
Inputpass = "123"
*/
-- Generate the following sentence through the above input
Select count (*) as CNT from DBO. Users where username = 'user1' and pass = '20180101 ';
-- If you are a hacker, enter the user name and password below.
/*
Inputusername = "'or 1 = 1 --"
Inputpass = ""
*/
-- Its query statement becomes like this
Select count (*) as CNT from DBO. Users where username = ''or 1 = 1 -- 'and pass = '';
-- Okay, now you don't need to be safe and terrible ~
Go

B. Server Input:
Use northwind;
Go
-- Create a stored procedure to query records in the orders table by passing in the order number
If object_id ('dbo. usp_getorders ') is not null
Drop proc DBO. usp_getorders;
Go

Create proc DBO. usp_getorders
@ Orders as varchar (1000)
As

Declare @ SQL as nvarchar (4000 );

Set @ SQL = 'select orderid, customerid from DBO. orders where orderid in ('
+ @ Orders + ');';

Exec sp_executesql @ SQL;
Go
-- Enter this OK normal query
Exec DBO. usp_getorders '2014, 10248,102 ';
-- Enter this --
Exec DBO. usp_getorders '--';
-- An error will pop up.
/*
MSG 102, Level 15, state 1, line 1
'(' There is a syntax error nearby.
*/
-- A dynamic statement is found here, and a value can be entered after the left bracket. If you enter a value, it returns a null value and only has the table structure.
Exec DBO. usp_getorders '-1 )--';
/*
Orderid customerid
---------------------

*/
-- Concatenate the following strings to search for your sysobjects records that you do not want others to see.
Exec DBO. usp_getorders '-1) Union all select ID, name from sysobjects --';
-- Even more terrible: If you input some very bad statements to destroy them, then you will be miserable.
Exec usp_getorders '-1) Update DBO. MERs set phone = '000000'' Where customerid = ''alfki ''--';
-- The above statement will change the phone number of your customers table customerid = ''alfki ''.

------ About SQL Injection more information, see shuige finishing http://topic.csdn.net/u/20081205/09/3dd06076-bcbe-45d4-998c-8999fdbe6fae.html