SQL Server - How to avoid SQL Injection? Stop using Dynamic Query with EXEC(@Query). Now.

A resource widely used in database systems and routines, Dynamic Query consists of assembling a string with T-SQL commands to be executed. This string is assembled based on string concatenation and possible validations with IFs according to certain scenarios.

After the final string has been constructed, it is processed by the EXECUTE command (or EXEC, for those more intimate... lol) and whatever is in this string will be executed in the database.

It is very important to note that dynamic query is not specific only to queries created from SQL Server. A system can do the same thing, creating a string variable in C# source code, for example, assembling the string and then sending that string to the database. This is also a dynamic query, but created within the application and to the database, it will just be an ad-hoc query that the application is sending.

Basic example:

DECLARE 
    @Objeto VARCHAR(128) = 'sys.objects',
    @Nome_Objeto VARCHAR(128) = 'syscerts',
    @Query VARCHAR(MAX)

-- Monta a nossa query dinâmica    
SET @Query = 'SELECT * FROM ' + @Objeto + ' WHERE [name] = ''' + @Nome_Objeto + ''''

-- Mostra na tela a query final depois de montada
SELECT @Query

-- Executa o comando no banco
EXEC(@Query)

Result:

As you can see, the possibilities are endless using Dynamic Query. From making joins and defining the columns returned by a SELECT to controlling filters in WHERE. A really cool example from a performance point of view would be this one (and it happens a lot):

Original query:

DECLARE 
    @Schema_ID INT = 4,
    @Nome_Objeto VARCHAR(128) = 'syscerts',
    @Type VARCHAR(10),
    @object_id INT

SELECT *
FROM sys.objects
WHERE (@Schema_ID IS NULL OR [schema_id] = @Schema_ID)
AND (@Nome_Objeto IS NULL OR [name] = @Nome_Objeto)
AND (@Type IS NULL OR [type] = @Type)
AND (@object_id IS NULL OR [object_id] = @object_id)

This bunch of @variabel IS NULL OR [column] = @variavel in the query's WHERE filter can often cause a performance problem in the query, as SQL will have to do several validations in the filter to return the data. A possible solution to this would be to use dynamic query:

DECLARE 
    @Schema_ID INT = 4,
    @Nome_Objeto VARCHAR(128) = 'syscerts',
    @Type VARCHAR(10),
    @object_id INT,
    @Query VARCHAR(MAX)


-- Monta a base da nossa query dinâmica 
SET @Query = 'SELECT * FROM sys.objects WHERE 1=1'


-- Aplica os filtros dinâmicamente
IF (@Nome_Objeto IS NOT NULL)
    SET @Query += ' AND [name] = ''' + @Nome_Objeto + ''''

IF (@Type IS NOT NULL)
    SET @Query += ' AND [type] = ''' + @Type + ''''

IF (@Schema_ID IS NOT NULL)
    SET @Query += ' AND [schema_id] = ' + CAST(@Schema_ID AS VARCHAR(10))

IF (@object_id IS NOT NULL)
    SET @Query += ' AND [object_id] = ' + CAST(@object_id AS VARCHAR(10))


-- Mostra na tela a query final depois de montada
SELECT @Query

-- Executa o comando no banco
EXEC(@Query)

Execution result:

If we analyze the dynamically generated final query and the original query, full of OR, we see that the dynamic query that is executed is much simpler than the original query. And this is reflected in the execution plan and execution cost as well:

In other words, the dynamic query has a justifiable applicability YES, and is very useful in N scenarios, but we need to be very careful with its use so as not to expose our environments to SQL Injection attacks.

After this brief summary about dynamic query, I will now present a brief summary about SQL Injection for you. This cracking technique consists of exploiting dynamic queries performed in the database to execute malicious commands in the database, which can range from showing confidential, confidential and sensitive data to deleting data and files on the database server.

If you are thinking that this technique must be very complex and elaborate, you are very mistaken. It is actually quite simple to use and an attacker can use it in text fields in an application, such as a Login screen for example, which sends a dynamic query to the bank in order to check whether the username and password entered are correct.

Example 1 – Logging into the system without authorization

Imagine a scenario with the following table in your bank, used for user registration and authentication in a very simple way:

CREATE TABLE dbo.Usuarios (
    Id_Usuario INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    Ds_Email VARCHAR(200) NOT NULL,
    Ds_Senha VARCHAR(100) NOT NULL
)

INSERT INTO dbo.Usuarios
VALUES('joaozinho@microsoft.com', 'joao')

And now imagine that your C# application has the following code to validate the email and password:

try
{

    using (var conn = new SqlConnection(dadosConexao))
    {

        conn.Open();

        using (var cmd = new SqlCommand())
        {

            cmd.CommandType = CommandType.Text;
            cmd.Connection = conn;

            /* Olha a nossa query dinâmica aqui :) */
            var dsQuery = "SELECT 1 FROM dbo.Usuarios WHERE Ds_Email = '" + email + "' AND Ds_Senha = '" + senha + "'";
                    
            cmd.CommandText = dsQuery;
            
            var retorno = cmd.ExecuteScalar();
            
            if (retorno != null && retorno = "1")
                Login.autenticaUsuario();
            else
                Login.retornaErro();

        }
    }

}
catch (Exception e)
{
    Retorno.Erro("Erro : " + e.Message);
}

What will arrive at the bank is the query that the application created. If a malicious user tries to carry out a SQL Injection attack, using a simple approach, such as placing the string “‘OR ‘x’=’x’–” in the user field and clicking the application’s “Login” button, this is the query that will arrive in the database:

SELECT * FROM dbo.Usuarios WHERE Ds_Email = '' OR 'x'='x'--' AND Ds_Senha = ''

Result: The attacker was able to log into the system

And believe me. They exist LOTS of strings like this to perform SQL Injection attacks.

Example 2 – Deleting and viewing data

In the same example above, on a simple login screen, note that a simple string like “‘; TRUNCATE TABLE dbo.Usuarios;–” can cause major disruptions in the life of a DBA by simply DELETING all data from the system's Users table.

Of course, the attacker still needs to discover the name of the system tables and, in the scenario above, this task ends up being complicated as the query does not return much data, just validation. But if this is on a query screen, for example, where the returned data is shown in a table on the screen, then we have an ideal scenario to carry out an attack when using a string like “‘; SELECT name, name, name, name FROM sys.tables; –” in a search field, for example, which we know is showing 4 columns on the screen.

Query that ends up being sent to the database:

SELECT cpfcnpj, FirstName, [Uid], ID 
FROM dbo.Tabela 
WHERE cpfcnpj = ''; SELECT name, name, name, name FROM sys.tables; --'

Result:

And then the attacker now has the name of the tables that exist in the database.. Which makes the “joke” much more interesting now, as he can delete data from any of these tables or view this data through the system itself. What if he could send the table data by email? Hmmmm..

Using the string “‘; EXEC msdb.dbo.sp_send_dbmail @recipients = ‘[email protected]’, @subject = ‘Teste’, @query = ‘SELECT * FROM Usuarios’ –“, the attacker can use DatabaseMail installed on the database server to send the results of a query to himself and view the database data:

In other words, the idea here is to show how dangerous SQL Injection attacks are. That's because I'm not even going to demonstrate how to use xp_cmdshell to execute commands at the operating system level on the server. You can even download ransomware remotely and run it on the server. All this through a simple search field in the system.

I hope you were shocked by the devastating impact that SQL Injection can have on your environment. Depending on the level of permissions, the attacker can even install Ransomware, spread it across your network and cause massive damage to your company through a simple text field on a system.

Tip 1 – Restricting permissions at the database level

The first step to containing this type of attack is to first limit the range of the attack and then try to prevent it from happening.

Yes, that's right. It seems strange, but if you can limit how far such an attack can reach, it is usually a much faster process to implement in the environment and that is why I prefer to start with this step.

Of course, this generalization of mine is based on traditional scenarios and should be evaluated, as your scenario may be different. Generally in large company scenarios, there are several different systems accessing the same instance, with dozens of Stored Procedures with dynamic query, in addition to the several ad-hoc queries that are the result of dynamic queries assembled in the application and sent to the database.

Evaluating and treating all these entry points is usually a very time-consuming thing to do. Precisely for this reason, I recommend starting by restricting application user permissions. It is very unusual for a system user to need permissions that go beyond reading/writing to the database(s) that the system accesses. Instance-level permissions are very rarely actually needed by system users.

And don't tell me that you always release db_owner permission, right? Let's stop this TODAY. A db_owner permission implicitly carries other non-required permissions like IMPERSONATE, which is so dangerous for audits, and also permission to view and list the databases, even though it do not have VIEW ANY DATABASE permission.

In the vast majority of cases, an application may need, at most, general EXECUTE permission on the database and be in the database roles db_datareader, db_datawriter and perhaps db_ddladmin. If you are one of those DBA’s who releases db_owner permission to the application user because it is more practical, I suggest reading the article SQL Server – Understanding the risks of the TRUSTWORTHY property enabled in a database, which shows what a db_owner user can do in an environment where the database has the TRUSTWORTHY parameter enabled. Even if he isn't, he will be allowed to use IMPERSONATE, which is already quite dangerous.

Tip 2 – Instance-level permissions restriction

Unfortunately, what do we see on a daily basis? Application connecting to the bank with a SA user or application user with sysadmin permission. In other words, imagine the scenario in which you have a very simple Web system to meet a specific need in the company's commercial sector. This system is just a supplier registration, which is not even that critical for the company. And this system connects the bank with the user sa. An attacker finds a loophole using this sysadmin user and applies a SQL Injection to the instance that contains all of the company's systems.

Company-wide data captured and then successfully encrypted! And all this from a system that wasn't even the company's Core. In other words, the first step to prevent this type of attack is to limit application permissions and which databases this application can read/write data from. If a system does not access/change any table in another database, why does it have this permission?

Furthermore, the simple fact that the application does not have sysadmin permissions means the attacker will not be able to activate features that are disabled, such as xp_cmdshell, for example, and will also not be able to execute critical commands such as xp_cmdshell, send emails using Database Mail, etc.

For all the reasons mentioned above, it is essential to limit the permissions of application users at the instance level. The idea is that you don't even have this permission. Using SA users or users with sysadmin permission is unthinkable and unjustifiable.

Tip 3 – Restricting permissions at the operating system (OS) level

To close this permissions part, we also need to limit the SQL Server user's permissions on the operating system. We see in many companies the user of the SQL Server service with local server Administrator permissions or even Domain Admin (Maximum permission within Active Directory. Can do EVERYTHING on ANY server).

This type of permission greatly enhances the effect of these SQL Injection attacks. So the most correct thing is to limit yourself to the user of SQL Server services, who is the one who executes the commands at the Operating System level when you execute an xp_cmdshell, for example, to a user with the least permission required.

Tip 4 – Identify dynamic queries in the environment

As I mentioned above, the permissions part is usually faster to implement and that's why I choose to do it first. Now we will shift our focus to preventing attacks, but we need to identify the possible entry points for SQL Injection attacks, that is, where we are using Dynamic Query in our environment.

At the application and system level, this work must be carried out by the Systems/Development team, scanning all the source code in search of places that use dynamic queries and applying the treatment of this in the application, especially because, when the query is assembled in the application, the bank has no way of differentiating what is a dynamic query or not to try to treat this in the database.

At the database level, when there are Stored Procedures that use this dynamic query technique, we can use a T-SQL query to identify all these SP's and evaluate which ones can be used for SQL Injection attacks. Generally I only evaluate Procedures that have string parameters (varchar, nvarchar, char, nchar) and with a size greater than 10. Numeric parameters and short strings are very difficult to use for SQL Injection.

To identify these Stored Procedures, you can use the query below:

DECLARE @Objetos_Query_Dinamica TABLE ( [Ds_Database] nvarchar(256), [Ds_Objeto] nvarchar(256), [Ds_Tipo] nvarchar(128), [definition] VARCHAR(MAX) )


IF (OBJECT_ID('tempdb.dbo.#Palavras_Exec') IS NOT NULL) DROP TABLE #Palavras_Exec
CREATE TABLE #Palavras_Exec (
    Palavra VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AI
)

INSERT INTO #Palavras_Exec
VALUES('%EXEC (%'), ('%EXEC(%'), ('%EXECUTE (%'), ('%EXECUTE(%'), ('%sp_executesql%')


INSERT INTO @Objetos_Query_Dinamica
EXEC sys.sp_MSforeachdb '
IF (''?'' <> ''tempdb'')
BEGIN

    SELECT DISTINCT TOP(100)
        ''?'' AS Ds_Database,
        B.[name],
        B.[type_desc],
        A.[definition]
    FROM
        [?].sys.sql_modules A WITH(NOLOCK)
        JOIN [?].sys.objects B WITH(NOLOCK) ON B.[object_id] = A.[object_id]
        JOIN #Palavras_Exec C WITH(NOLOCK) ON A.[definition] COLLATE SQL_Latin1_General_CP1_CI_AI LIKE C.Palavra
    WHERE
        B.is_ms_shipped = 0
        AND ''?'' <> ''ReportServer''
        AND B.[name] NOT IN (''sp_WhoIsActive'', ''sp_showindex'', ''sp_AllNightLog'', ''sp_AllNightLog_Setup'', ''sp_Blitz'', ''sp_BlitzBackups'', ''sp_BlitzCache'', ''sp_BlitzFirst'', ''sp_BlitzIndex'', ''sp_BlitzLock'', ''sp_BlitzQueryStore'', ''sp_BlitzWho'', ''sp_DatabaseRestore'')
        AND NOT (B.[name] LIKE ''stp_DTA_%'' AND ''?'' = ''msdb'')
        AND NOT (B.[name] = ''sp_readrequest'' AND ''?'' = ''master'')
        AND EXISTS (
            SELECT NULL
            FROM [?].sys.parameters X1 WITH(NOLOCK)
            JOIN [?].sys.types X2 WITH(NOLOCK) ON X1.system_type_id = X2.user_type_id
            WHERE A.[object_id] = X1.[object_id]
            AND X2.[name] IN (''text'', ''ntext'', ''varchar'', ''nvarchar'')
            AND (X1.max_length > 10 OR X1.max_length < 0)
        )
            
END'

SELECT * FROM @Objetos_Query_Dinamica

Result:

Now just identify, validate and analyze the queries that appear in this query and check if they are susceptible to SQL Injection attacks and whenever possible, replace the EXECUTE command with sp_executesql. In the next topic I will show why.

Tip 5 – Handle dynamic queries

And now the part where we must handle our queries has finally arrived.

At the application and system level, this work must be carried out by the Systems/Development team, scanning all the source code in search of places that use dynamic query and applying this treatment in the application, as I had already commented in the previous topic. There are several ways to avoid this in the application, such as parameterized queries, which will block any type of SQL Injection that an attacker may try to attack.

At the database level, you should use the query I shared in the previous topic to identify queries that use dynamic query. The first step is to analyze whether this query really needs to be dynamic. Believe me, I've seen many cases where dynamic queries were used without any need, as I will demonstrate below:

ALTER PROCEDURE dbo.stpConsulta_CPF ( 
    @CPF VARCHAR(14) 
)
AS
BEGIN
    
    DECLARE @Query VARCHAR(MAX) = 'SELECT * FROM dbo._Teste WHERE CPF = ''' + @CPF + ''''
    EXEC(@Query)

END

Note that in the example above, the use of dynamic Query is highly avoidable:

ALTER PROCEDURE dbo.stpConsulta_CPF ( 
    @CPF VARCHAR(14) 
)
AS
BEGIN
    
    SELECT * 
    FROM dbo._Teste 
    WHERE CPF = @CPF

END

Much safer now and we are free from SQL Injection in this example. 🙂

Does the query really need to be dynamic? So the second step is to exchange, whenever possible, the EXECUTE commands for sp_executesql, since the second option allows the parameterization of queries. I'll even use the example I already showed here.

Dynamic Query with EXECUTE:

DECLARE 
    @Schema_ID INT = 4,
    @Nome_Objeto VARCHAR(128) = 'syscerts',
    @Type VARCHAR(10),
    @object_id INT,
    @Query VARCHAR(MAX)


-- Monta a base da nossa query dinâmica 
SET @Query = 'SELECT * FROM sys.objects WHERE 1=1'


-- Aplica os filtros dinâmicamente
IF (@Nome_Objeto IS NOT NULL)
    SET @Query += ' AND [name] = ''' + @Nome_Objeto + ''''

IF (@Type IS NOT NULL)
    SET @Query += ' AND [type] = ''' + @Type + ''''

IF (@Schema_ID IS NOT NULL)
    SET @Query += ' AND [schema_id] = ' + CAST(@Schema_ID AS VARCHAR(10))

IF (@object_id IS NOT NULL)
    SET @Query += ' AND [object_id] = ' + CAST(@object_id AS VARCHAR(10))


-- Mostra na tela a query final depois de montada
SELECT @Query

-- Executa o comando no banco
EXEC(@Query)

Result:

Now let's make some changes to the query to use the parameterized query with sp_executesql:

DECLARE 
    @Schema_ID INT = 4,
    @Nome_Objeto VARCHAR(128) = 'syscerts',
    @Type VARCHAR(10),
    @object_id INT,
    @Query NVARCHAR(MAX)


-- Monta a base da nossa query dinâmica 
SET @Query = 'SELECT * FROM sys.objects WHERE 1=1'


-- Aplica os filtros dinâmicamente
IF (@Nome_Objeto IS NOT NULL)
    SET @Query += ' AND [name] = @Nome_Objeto'

IF (@Type IS NOT NULL)
    SET @Query += ' AND [type] = @Type'

IF (@Schema_ID IS NOT NULL)
    SET @Query += ' AND [schema_id] = @Schema_ID'

IF (@object_id IS NOT NULL)
    SET @Query += ' AND [object_id] = @object_id'


-- Mostra na tela a query final depois de montada
SELECT @Query

-- Executa o comando no banco
EXEC sys.sp_executesql 
    @stmt = @Query,
    @params = N'@Nome_Objeto VARCHAR(128), @Type VARCHAR(10), @Schema_ID INT, @object_id INT',
    @Nome_Objeto = @Nome_Objeto, @Type = @Type, @Schema_ID = @Schema_ID, @object_id = @object_id

Result:

And so, we have the best of all worlds: Flexible and customizable, fast and secure Query.

• Execute Dynamic SQL commands in SQL Server
• Protecting Yourself from SQL Injection in SQL Server – Part 1
• Protecting Yourself from SQL Injection in SQL Server – Part 2
• Using Parameters for SQL Server Queries and Stored Procedures
• Using Triggers to Mitigate Some SQL Injection Effects
• SQL Injection – the golden rule
• SQL Injection: Defense in Depth
• Everything you wanted to know about SQL injection (but were afraid to ask)
• Stored procedures and ORMs won’t save you from SQL injection
• Dynamic SQL & SQL injection