[Reprint] The preparation of safe Transact-SQL

Safely write secure Transact-sqlbart Duncan
Overview of Microsoft Corporation This page to protect the security of developing SQL Server with the least privilege account for development follow the best way to protect T-SQL to learn about T-SQL commands with unique security considerations brief summary of references
There are plenty of good sources of information about how to deploy SQL Server in a secure manner. However, the target users of these resources are usually the database administrators who perform protection tasks for the applications that have been developed. In addition, there is a lot of content that discusses how to write secure. NET and ASP.net code, including. NET code to access SQL Server. However, many of these resources are concerned with data access code that runs on the application server, rather than Transact-SQL (T-SQL) code that is executed on the database. This column will focus on how to develop T-SQL code that runs safely on SQL Server.
Return to the top of the page secure SQL Server development
The first step in developing secure T-SQL is to secure the development of SQL Server. Why try to lock up an instance of SQL Server that does not save real data, and never show it to end users? This is because it forces you to write more secure T-SQL, and it is easier to protect the application when you deploy your application to production. Here are a few specific steps that you can take to quickly protect your development server:
•
In developing or testing SQL Server, you should have at least one running, up-to-date Service Pack and SQL Security patch to ensure that your customers can successfully run your application on this version of SQL Server.
•
By default, SQL Server Service Pack 3a Disables the restless feature called "Cross-database ownership chaining." When you install SP3 on a development server, if you let the Service Pack disable cross database ownership chaining, you can help verify that you are writing T-SQL code based on a secure server configuration.
•
An easy way to find out about common Security configuration issues on SQL Server is to run Microsoft Baseline safety Analyzer for that server. In addition to this approach, you can use the resources listed in the "Resources" section of this column, which provides additional steps to help you secure the development of SQL Server.

In general, the best way to secure a development server is to protect it as if it were running in a production environment. The closer you are to this goal, the more confident you will be that the code you develop can work properly in a secure production environment.
Go back to the top of the page and develop with the least privilege account
During the development process, everyone is fascinated with accounts that have sysadmin or dbo SQL Server permissions until they are converted to a lower-privileged account before deployment. There is a problem with this approach: restoring a designer's permission set to the lowest required permission set is much more difficult than writing these permission sets during the development of an application.

Since you want to decide which permissions to remove before you deploy the application, do not develop T-SQL code using the SQL sysadmin account. If you use a SQL sysadmin account, you may have the result that your application will run with a more privileged account than the required permissions. Therefore, when developing, use the account with the least privilege instead.

When you develop with such an account, you gradually increase the specific permissions granted to EXEC (execute) Some of the required stored procedures, select from certain tables, and so on. Write these GRANT statements so that you can easily deploy the same minimum permissions into a production environment without any guesswork based operations.

The same idea applies to testing. When you perform temporary tests and more complex tests, the accounts you use should have the same set of permissions and user permissions as the accounts that are used in the production environment.

Another advantage of using the least privileged account during development is that you can avoid accidentally writing code that requires dangerous or overly privileged permissions. For example, suppose you need to interact with third-party COM components in T-SQL. One way to do this is to send a SQL batch command that invokes sp_OACreate and sp_OAMethod directly to manipulate the COM object. The above approach works well in the development environment where applications connect to SQL Server using the sysadmin account. However, when you try to prepare an application that has already been developed for production deployment, you will find that the method does not work if you use a lower-privileged account. In order for the application to function correctly in a production environment using a non-sysadmin account, you must explicitly grant EXECUTE permissions for sp_OACreate. Consider if a user eventually finds a way to execute arbitrary code using the application login and use this permission to instantiate a similar Scripting.FileSystemObject COM object against SQL Server, what security risks will arise?
Return to the top of the page follow the best way to protect T-SQL
Defending a series of security vulnerabilities called "SQL injection" is critical. In general, you will use multiple layers of protection against SQL injection attacks:
•
Performs validation of user-supplied input (for example, force data type and maximum string length).
•
Escapes a sequence of characters that may have special meaning to the database engine. In T-SQL, the two most commonly used strings in an injection attack are single quote characters (') and a sequence of annotation characters (-).
•
In a T-SQL statement, do not inline a user-supplied value. Instead, use preprocessing statements and parameterization.

SQL injection attacks are described in detail elsewhere, so I don't spend a lot of time discussing the details of the problem here. However, it is important to emphasize that SQL injection problems are not limited to T-SQL queries built at the application level. SQL injection problems can occur whenever a T-SQL query is executed in part by a user-supplied value build. This means that a stored procedure that builds a query string internally and executes the query through the EXEC () command or sp_executesql stored procedure can also be attacked. See the Resources section for resource links that provide examples of various types of SQL injection attacks, as well as tips for protecting your code from these attacks.

Another best practice is to avoid granting permissions on base tables. For queries that you want users to be able to execute, you should package them in a stored procedure and grant execute permission only to those stored procedures. If you follow this guide, even if the user manages to skip your application and log on directly to the database, they will not be able to evade any data validation, auditing, business rules, or row-level security restrictions that you have built in the stored procedure.
Back to the top of the page understand T-SQL commands with unique security considerations
There are some T-SQL commands and extensions that have their own unique security considerations. One of these is sp_oacreate and its associated system process series (such as sp_OAMethod, Sp_oaproperty, etc.). Previously, we have studied a potential security issue that can be created by granting the application login direct access to these procedures. To avoid this problem, you should never write application code that calls the sp_OA procedure directly, but package all references to these procedures in your own T-SQL stored procedure and grant only access to these wrapper stored procedures. Also, do not allow application code to pass the name of a COM object or method as a string that can be called unconditionally by the wrapper process.

Another built-in SQL Server extension with a unique set of security risks is xp_cmdshell. This system stored procedure can run any executable file or system command. For some obvious reasons, the EXEC permission on xp_cmdshell is only a sysadmin user by default and must be displayed for other users to be granted this permission. If you need an application to run a particular command or utility on SQL Server, be aware that you do not build an xp_cmdshell directly accessible content in your application. Such risks are similar to the risk of direct access to sp_OACreate. Once an account has been granted xp_cmdshell EXEC privileges, the account not only performs the specific commands that you want it to access, but also executes hundreds of operating system commands and other executable files. Similar to sp_OACreate, always package xp_cmdshell calls in another stored procedure to avoid granting EXECUTE permissions directly on the xp_cmdshell.

You should also avoid concatenating any user-supplied string parameters or the string arguments supplied by the application with the commands that will be executed through xp_cmdshell. If this requirement is not met, you must understand that there is a potential code injection attack (at least in SQL Server) specifically for xp_cmdshell. Take the following stored procedure as an example:

CREATE PROCEDURE usp_dofilecopy @filename varchar (255) asdeclare @cmd varchar (8000) SET @cmd = ' copy \ rc hare\ ' + @filena Me + ' \\dest hare\ ' exec master.dbo.xp_cmdshell @cmdGOGRANT exec on usp_dofilecopy to Myapplogin

By packaging the xp_cmdshell call in your own stored procedure and granting EXEC permission only for the usp_dofilecopy stored procedure, you have blocked the user from calling xp_cmdshell directly to execute arbitrary commands. However, insert the following shell command as an example:

EXEC usp_dofilecopy @filename = ' & del/s/q \\dest hare\ & '

Using this @filename parameter, the string to be executed will be copy \ RC hare\ & del/s/q \\dest hare\ & \\dest Hare. The and number (&) is processed by the operating system command interpreter as a command delimiter, so the string will be CMD. EXE is treated as three unrelated commands. The second command (DEL/S/q \\dest hare\) will attempt to delete all files in the \\dest Hare. By exploiting a shell command in the stored procedure to insert a vulnerability, the user can still execute arbitrary operating system commands. One way to defend against this type of attack is to package the command string in a T-SQL function, as shown below. This user-defined function adds a shell escape character (^) to escape any & characters or other characters that have special meaning.

--function:fn_escapecmdshellstring--Description:returns A escaped version of a given string--with carets (' ^ ') added In front of the special-command shell symbols. --Parameter: @command_string nvarchar (4000)--create FUNCTION dbo.fn_escapecmdshellstring (@command_string nvarchar ( 4000)) RETURNS nvarchar (4000) asbegin DECLARE @escaped_command_string nvarchar (4000), @curr_char nvarchar (1), @curr_ Char_index int SELECT @escaped_command_string = n ', @curr_char = N ', @curr_char_index = 1 while @curr_char_index <= L EN (@command_string) BEGIN SELECT @curr_char = SUBSTRING (@command_string, @curr_char_index, 1) IF @curr_char in ('% ', ' &L t; ', ' > ', ' | ', ' & ', ' (', ' ', ' ^ ', ' ' ', ' ^ ', ' ' ') BEGIN SELECT @escaped_command_string = @escaped_command_string + N ' ^ ' S Elect @escaped_command_string = @escaped_command_string + @curr_char SELECT @curr_char_index = @curr_char_index + 1 End RE TURN @escaped_command_stringEND

The following is a stored procedure that eliminates the command shell Insertion Vulnerability:

CREATE PROCEDURE usp_dofilecopy @filename varchar (255) asdeclare @cmd varchar (8000) SET @cmd = ' copy \ rc hare\ ' + dbo.fn_ Escapecmdshellstring (@filename) + ' \\dest hare\ ' EXEC master.dbo.xp_cmdshell @cmd

The third T-SQL command set with unique security considerations is the command for those that are allowed to execute dynamically built queries: EXEC () and sp_executesql. The risk of SQL injection attacks is not the only reason to avoid dynamic SQL. Any queries that execute dynamically through these commands will run in the security context of the current user, rather than in the context of the owner of the stored procedure. This means that using dynamic SQL may force you to give users permission to access the base table directly. Take the following stored procedure as an example:

CREATE PROC Dbo.usp_retrievemyuserinfo as SELECT * from UserInfo WHERE UserName = user_name ()

This procedure restricts the current user so that it cannot view data from any other user. However, if the SELECT statement in this procedure is executed through dynamic EXEC () or through sp_executesql, you must grant the user a direct SELECT permission on the UserInfo table because the dynamically executing query runs in the security context of the current user. If users can log on to the server directly, they can use this permission to skip the row-level security provided by the stored procedure to view the data for all users.
Back to the first summary of the page
In summary, the following recommendations will help you develop T-SQL code that runs safely in SQL Server:
•
Protect the security of your development SQL Server as if it were a production server. This helps ensure that you develop secure code and also helps you define the minimum set of permissions that your application needs to run correctly.
•
Use a SQL Server account with minimal permissions when you are developing and testing T-SQL. Do not use the sysadmin or dbo accounts.
•
Be very careful about stored procedures that allow T-SQL to execute arbitrary external code, such as sp_OACreate and xp_cmdshell. If you must use these extensions, be sure to consider their unique security vulnerabilities.
•
Follow the best ways to protect T-SQL development, including: passing user-supplied data to explicit parameters, writing code that avoids SQL injection attacks, avoiding unnecessary dynamic SQL, granting access to stored procedures, and not granting direct access to base tables.
•
Secure T-SQL to make a secure application. Use the following resources to ensure that your server is configured securely and that you have a secure database client application.
Back to the top of the page reference
SQL Server Security Resource Page

Building Secure asp.net applications:authentication, Authorization, and secure communication

SQL Server SP3 security Features and best practices:secure multi-tier deployment