Why Dynamic Query Is Bad

Priti Kumari

8y37.7k7100Article

SQL ServerDynamic Query

7 Comments

Join the conversation! Your thoughts help the community grow.

Onkar Sharma6y ago
Thank you for sharing such a useful content...
Ivan Ospina8y ago
There is NO such thing as Advantages of Dynamic Query, "It reduces the work of programmers a lot" is not true and it is the common misconception about it, programmers use to write this kind of queries because they are too lazy to understand how conditions in WHERE clause work and prefer to write a snowball extracomplex unnecessary big query. "Performance is improved..." the 99% of cases this is not true because these lazy guys always use EXEC and they don't care about execution plans even, if they do then they just create an index awareless the optimizer is not using it
Hadshana Kamalanathan8y ago
Good Article,Thank you for sharing information
Manish Kumar8y ago
Some times dynamic query is good in terms of performance perspective.
- Priti Kumari8y ago
  yes, but as I said every action has its equal and opposite reaction. Although you get better performance "sometime" but at the cost of threats such as SQL injection. You need to ensure that all the parameters passed to procedure are correct and as per the format.
Hiten Pandya8y ago
Very nice explaination and good knowledge....Keep posting such articles.
Suraj Kumar8y ago
Very good knowledge sharing which is related to every programmer, developer and DBA. Thanks a lot. Keep sharing such thing in future.

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 --'

Output

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.