Translation (iii) Stairway to t-sql:beyond the Basics level 9:dynamic T-SQL Code

Stairway to T-sql:beyond the Basics level 9:dynamic T-SQL Code

by Gregory Larsen, 2016/07/29 (first published:2014/07/23)

Original link: http://www.sqlservercentral.com/articles/Stairway+Series/113118/

The Series

This article is part of the series: the Ladder to T-sql: Beyond the Basics

The following is a subquery from the steps it leads to T-SQL DML, Gregory Larsen, which covers more advanced T-SQL language.

And when you need to write code, create a specific TSQL code, and execute it. When you do this you are creating dynamic TSQL code. What you use to create dynamic TSQL can be simple code, or it can be complex. When writing dynamic TSQL you need to understand the possibility of dynamic code opening a SQL injection attack. In this article, I explain why dynamic TSQL is used to generate dynamic TSQL. I'll also explore SQL injection and discuss how to avoid SQL injection attacks in your dynamic TSQL code.

What is dynamic TSQL and why do you want to use it?

What exactly is a dynamic TSQL? Dynamic TSQL is likely to be different code every time you run it. This is a batch of TSQL code generation and execution. The generated code is created based on certain conditions or parameters in the batch process. When "condition or parameter different TSQL code produces different TSQL to execute."

You typically use dynamic TSQL when you want to programmatically determine what tsql you need based on the parameters and/or data of the tables in the database. The use of dynamic TSQL is endless. Here are two examples of dynamic TSQL you might want to use:

You want users to select from a drop-down list some criteria that might cause the query to run differently, such as the sort order.

The application does not know the name of the table to run until run time.

Because the TSQL language does not allow you to use the name of a particular table or column for a variable or parameter, it can be used instead of a dynamic TSQL.

To get a better understanding of dynamic TSQL Let's look at a few examples.

Create a simple dynamic TSQL

For the first example, how to create a dynamic TSQL lets us consider the following scenario. Suppose you have an application where the user interface allows the user to select a table to read from the drop-down list. Therefore, each time someone uses an interface, they can select a different table from which to return data. In this example we assume that the user interface displays the table information from the ADVENTUREWORKS2012 database and the user chooses the Adventureworks2012.sales.salesorderdetail table. The code in Listing 1 shows a method for the first 10 records returned from the Adventureworks.sales.salesorderdetail table using dynamic TSQL code.

Listing 1: A simple dynamic TSQL instance

The code in Listing 1 first declares a variable name @cdm to hold the dynamic SELECT statement and the @ table variable to be built to save the table name. Then I set the table variable for table Adventureworks.sales.salesorderdetail. To build my actual dynamic TSQL statement I can use the SET statement. This statement sets the variable cmd to the cascading string value that contains the SELECT statement and the @ table variable value. Then I execute my dynamic TSQL statement, which is contained in the @cdm variable, using the EXECUTE statement.

To further examine the dynamic TSQL in Listing 1, you can try using different code to declare the use of this table by changing the "set" table = "of the adventurework2012 table.

Handle more complex dynamic SQL Server requirements

There are also times when you need to write some more complex dynamic TSQL. One of the things I might need to do as a DBA is when I want to generate code to perform some kind of database maintenance. When I need to establish the purpose of database maintenance, I usually look at a system view and generate scripts to display and/or execute dynamic TSQL. Suppose you are a DBA that takes over the maintenance of a database and wants to delete several test tables created in the database. The table has a name that begins with the prefix "test". show that you might read the Sys.tables view and generate the corresponding DELETE statement, let's look at the code in Listing 2.

Listing 2: Dynamic Code Removal test table

The code in Listing 2 contains three different sections. The first part of the database created is called Dyna, and then creates 4 different tables, with two tables starting with "test" from, and the two tables starting with "test" I want to delete the dynamic TSQL code. The second part of the code is my dynamic TSQL code. The last part of the code cleans up by deleting the test database that I created.

If you look at the code, in section 2 you will find that the dynamic TSQL code first prints out the DELETE statement, runs it, and then deletes the test table that I created in section 1. I work through the while loop and look for different tables that start with the string "test". For each table, I found that starting with "test", I built a delete command that was stored in the variable cmd. Then, you display the DELETE statement by using a print statement, and then execute the statement immediately by using the EXECUTE statement. The last part, the third part cleans up the delete Dyna database.

To test this code, I recommend that you run each section independently from section 1th. When you run the 1 section, review the dynamic database and verify the dynamic database in table four. Next, in section 2nd, when you run this section, you will see two messages displayed in the Messages tab in the Query Analyzer window. The two statements shown are two DELETE statements that are dynamically generated and executed. Once you have finished running the code in section 2, go back to the table to review your dynamic database. If you are using Object Explorer in SQL Server Management Studio, do not forget to refresh. Alternatively, you can select from the Sys.tables view. You should now find that only two tables exist, and two tables start with "test". Once you have completed the validation of the code in section 2nd, I will run the code in section 3rd to clear it. This code will delete the Dyna database.

This is a very simple example of how to check a row of data to generate a dynamic TSQL. As a DBA, many times it will come in handy to learn how to write TSQL code to generate TSQL code.

Avoid SQL injection

You may have heard that the dynamic tsql is evil. The evil part of dynamic TSQL is that it opens up the possibility of SQL injection attacks. SQL injection is a hacker technique that malicious users try to use to enter fields into a data table. These malicious users try to insert additional TSQL code into the Data entry field of the input field beyond the original intent. By inserting the TSQL code, you can fool the data returned by the system, get what they shouldn't have, or, worse, run additional TSQL commands on the SQL Server database to get the application running, and SQL injection attacks can insert data into database tables, delete tables, Or worse, install a new login to get system administrator privileges.

To show that dynamic TSQL is not managed properly, let me first create a database and a code table in Listing 3. I will use this database and table to demonstrate how dynamic TSQL can be susceptible to SQL injection attacks.

Listing 3: Creating databases and tables to demonstrate SQL injection attacks

The code in Listing 3 creates a database called Dyna, and then creates and populates 4 rows of data for the table.

Suppose my application data selection lets the user enter a text string containing the input text string into the screen, which is contained in ProductName, and then the application returns all existing table records. The application passes a text string that the user enters into a process called getproducts storage, and then returns the data from the stored procedure to the user. The stored procedure is getproducts encoded, as shown in Listing 4.

Listing 4: Stored procedure returns user name password

By getproducts the stored procedure in Listing 4, you can see that the stored procedure accepts a parameter "Enteredtext, which is used to create a dynamic TSQL statement stored in a variable command. The variable is then executed. (Note that this process may have been written without using dynamic SQL, where I use dynamic SQL to illustrate potential problems.) ）

To demonstrate how to use this stored procedure, let me run the code in Listing 5 to execute it.

Listing 5 Executing a stored procedure returns a user name typically

The code in Listing 5 calls the getproducts stored procedure to produce the results shown in report 1.

Report 1: Call GetUserName results from the code in Listing 5

Because the code in my stored procedure getproducts requires a parameter to generate the varchar variable command it leaves the stored procedure and begins a SQL injection attack. I can prove this by executing the getproducts stored procedure with the code in Listing 6.

Listing 6: Code exposing getproducts stored procedures are susceptible to SQL injection

If you review the code in Listing 6, you can see that I passed the string "red" with some other characters attached to my stored procedure getproducts. These extra characters through my limit query only return the product with "red" in the Product Name column with a 1 ID value. Let my stored procedure Enteredtext parameters use unedited text to allow me to inject extra characters into parameters to perform other actions that were not intended for getproducts code stored procedures.

In my last example I will give you a lossless SQL injection attack utilizing the dynamic TSQL in my mygetproducts stored procedure. Most SQL injection attacks attempt to get extra data from the system, or just want to destroy the database. To explore the problem in more depth, let's look at the code in Listing 7.

Manifest 7:sql injection to return additional data

If I run the code in Listing 7, it will generate two result sets. The first result set has 0 rows, and the second group is the text found in report 2:

Report 2: Text results when you run the code in Listing 7

If you compare the results of a normal getproduct stored procedure to the result of 1, and find 2 results, you can see that the code in Listing 7 generates some extra output columns, and my stored procedure is not originally designed to display but is due to SQL injection attacks.

My example in Listing 7 is still not a destructive use of SQL injection, but it does allow me to take advantage of the @ enteredtext parameter of the stored procedure for all columns of the Customer table to return data. To achieve this, I added "*" from the product; Select*from product. Notice that two dashes ("-") are added at the end of the appended string. This allows me to annotate the characters or code that the stored procedure might contain after the parameter.

Listing 8: Destroying the TSQL Injection execution command

In Listing 8, I added a DELETE statement to the @ e-mail parameter. In this example, I deleted the Customer table. If I run the code in Listing 8, it will delete the Customer table.

How to deal with SQL injection attacks

No one wants their code to be compromised by a SQL injection attack. In order to deal with SQL injection attacks, you should consider the following points when developing your TSQL code application:

The best way to avoid SQL injection attacks is to not use dynamic SQL

Edit special characters for user input parameters, such as semicolons and comments

Let your parameters, as long as you need to support user input data

If you have to use dynamic SQL, then use parameterized SQL to perform tsql using SP_EXECUTE to perform dynamic TSQL instead of executing.

Enhanced security allows only minimal rights to execute dynamic TSQL.

If your application needs to build some code, including dynamic TSQL and then using parameterized TSQL is a good way of anti-SQL injection. In Listing 9, I've provided an example of how I can modify my getusername stored procedure using parameterized TSQL.

Listing 9: Using parameterized TSQL

In Listing 9, I changed my getproducts stored procedure using sp_executesql to perform my dynamic TSQL. In this modified stored procedure, I made the following changes:

Changing the string @ cmd no longer includes the value of the Enteredtext variable in the command string. Instead I introduced a user to the text with a name of @ enteredparm.

Added a set statement to set the variable @ wildcardparm at the beginning to increase the wildcard character (%) and increase the Enteredtext parameter at the end.

Changed how string cmd was executed. Instead of executing the string with the EXEC statement, I used the program sp_executesql.

By making these two changes, the text entered by the user is now executed as a parameter-driven query. This way, the user can no longer try to inject more TSQL code into my getproduct stored procedure. To verify this, run the four different commands shown in Listing 5, 6, 7, and listing 8. But since I have deleted my product table, I first need to recreate it with the data. For this I need to run the code first in Listing 9.

Listing 9: Creating and populating a Customer table

After running listing 9 to recreate the product table, I can run listings 5, 6, 7, and 8 to prove that I resolved the SQL injection attack. When you run these different commands, you will find that only listing 5 returns the data. The reason others do not return data is that dynamic TSQL generation is now looking for a value injection that contains additional user input values ProductName, which of course does not conform to the column values in the product table for any product.

Summarize

No one wants to see a SQL injection attack. Of course, the best solution to make sure it doesn't happen is to not use dynamic SQL code in your application. If your application does require dynamic SQL, this article will show you how to minimize the risk associated with SQL injection. The next time you write dynamic SQL, be sure to take steps to avoid the possibility of SQL injection attacks.

Questions and Answers

In this section, you can understand how well you understand SQL injection by answering the following questions.

Question 1:

What's the best way to avoid SQL injection attacks (the best way)?

A Do not deploy TSQL code using dynamic TSQL

b Edit user input dynamic TSQL data for special characters that allow SQL injection attacks

c Let the user input parameters of the dynamic TSQL as short as possible

D using parameterized TSQL code

Question 2:

What can users do with SQL injection (select all applications)?

A returns data that the application does not intend to let the user select

b inserting data into a table that the application does not intend to use

C Delete a table

D system administrator privileges to provide a new account

E above all

Question 3:

If you are deploying dynamic TSQL code that is contained in a variable, these two methods of execution are best used to reduce your risk of SQL injection attacks?

Exec

sp_executesql

Answer:

Question 1:

The correct answer is a. The best way to avoid SQL injection is to not allow dynamic TSQL code in your application.

Question 2:

The correct answer is E, all above. With SQL injection, a malicious user can perform many different SQL operations. Such a command can be performed depending on the right of the account used to run the dynamic TSQL command. If the application's account has administrator rights, the SQL injection attack can be what the user wants to do.

Question 3:

The correct answer is B. Using sp_executesql you can input data using parameters from your user to your parameterized TSQL code.

This article is part of a description of the access to the T-SQL staircase: Beyond basic knowledge

Translation (iii) Stairway to t-sql:beyond the Basics level 9:dynamic T-SQL Code