Why Dynamic Query Is Bad

Introduction

In this article, we will be talking about Dynamic Query and why it's bad.

What is static query?

As we know, a query that computes a result based on a given set of inputs is called static query. The list of input parameters is fixed and static query evaluates the result based on that.

Now, consider a scenario where you want to obtain the result with a given set of parameters which either can be less or equal to the desired input. We can assume we need an approach called function overloading. Alas!! That’s not possible here.

We can use a Dynamic Query in that case. It makes the programmer's life easy in various ways which we will be discussing further.

What is Dynamic Query?

Dynamic SQL query is a set of SQL statements stored in a variable. We can create so many variable queries and easily join them to get the results.

We can create DDL and DML statement together in one procedure also.

For example -

CREATE OR ALTER PROCEDURE GeneralCreate
@TableName varchar(30), @id varchar(5) AS
DECLARE @query varchar(1000), @query2 varchar(1000)
SET @query = 'CREATE TABLE ' + @TableName + '(' + @id + ' int )'
PRINT @query
SET @query2 = 'INSERT INTO ' + @TableName + ' VALUES('
'123'
')'
PRINT @query2

Output

Advantages of Dynamic Query

• It reduces the work of programmers a lot. They don’t need to write separate procedure/function anymore. Thus, a lot of flexibility is provided. Based on the list of input, the query is built.
• Performance is improved as better execution plan is generated which handles control of flow and has multiple SELECT statements, especially if sp_executesql is used which obtains parameterized queries. It is more likely that the execution plan is re-used.

Consider the following code for the general SELECT query.

Create OR Alter Procedure GenericTableSelect
@TableName VarChar(100)
AS
Declare @SQL VarChar(1000)
SELECT @SQL = 'SELECT * FROM '
SELECT @SQL = @SQL + @TableName
print @SQL
exec GenericTableSelect Employee

Output

Threats to Dynamic Query

SQL injection is an approach where an intruder enters data which causes the application to execute SQL statements not generally intended to execute. SQL injection is possible with the Dynamic SQL that doesn’t  handle parameters well. Assuming that SQL statements were sent from the client, dynamic SQL gets generated in T-SQL stored procedures, or SQL batches are executed from CLR stored procedures.

As we know, with the query, a string is executed on the fly. Here, we take this example how dynamic SQL could create a problem. Consider a procedure to search orders which takes parameters. Suppose, the user intentionally puts '1 OR drop table order --'  instead of the product name to look for what will be the resultant code.

Consider the following procedure example.

Alter PROCEDURE search_orders @id int = NULL, @name varchar(40) = NULL AS
DECLARE @sql varchar(1000)
SELECT @sql = ' SELECT Name ' + ' FROM dbo.product WHERE 1 = 1 '
IF @id IS NOT NULL
SELECT @sql = @sql + ' AND ID = '
'' + cast(@id as varchar) + ''
''
IF @name IS NOT NULL
SELECT @sql = @sql + ' AND Name LIKE '
'' + @name + ''
''
print(@sql)

Output

search_orders 1, '1 OR drop table order --'

As we can see, there is a command to drop table which is not expected from the general user. We need to safeguard our data from such attacks.

Here is the list of precautions to avoid such attacks to some extent.

• Never give unnecessary privileges to the user. The user should have at most SELECT permission that ensures that the user gets only reading permission.
• Try not to show SQL errors to users. This might give hint to an attacker to find a loophole in the database.
• Make it mandatory to supply parameters separately to users. That is, in a T-SQL procedure, use sp_executesql, not EXEC().

Disadvantage of Dynamic Query

• It is vulnerable to SQL injection which could hamper the security a lot.
• It is very complex in nature as the query plan is built on the fly. It is difficult to understand how the query is going to form.
• If sp_executesql is not used for calling the procedure, then the execution plan cannot be reused.

Conclusion

From the above discussion, we can conclude that although Dynamic Query gives liberty to build statements on the fly, this flexibility comes at a cost of security and performance hit.

Therefore, unless there is an urgent need, we should not opt for Dynamic Query.