Dynamically Constructed Queries

This section examines some ways in which you can construct queries dynamically, including:

• Executing a string statement

• Querying by form

• Using parameterized queries

Executing a String

Transact-SQL contains a variation of the Execute statement that you can use to run a batch recorded in the form of a character string:

      EXEC[UTE] ({@string_variable [N]'tsql_string'} [+...n]) 

You can supply a Transact-SQL batch in the form of a character string, a variable, or an expression:

      Exec ('select * from Contact' 

The Execute statement allows you to assemble a batch or a query dynamically. This might look like magic to you:

      declare @chvTable sysname      set @chvTable = 'Contact'      Exec ('select * from ' + @chvTable} 

The Execute statement is necessary because the following batch, which you might expect to work, will actually result in a syntax error:

      declare @chvTable sysname      set @chvTable = 'Contact'      select * from @chvTable  -- this will cause an error 

The error occurs because SQL Server expects a table name, and will not accept a string or a variable, in a From clause.

It is important to realize that you are dealing with two separate batches in the example with the Execute statement. You can use the variable to assemble the second batch, but you cannot reference variables from the batch that initiated the Execute statement in the string batch. For example, the following code will result in a syntax error:

      declare @chvTable sysname      set @chvTable = 'Contact'      Exec ('select * from @chvTable') 

The server will return

      Server: Msg 137, Level 15, State 2, Line 1      Must declare the variable '@chvTable'. 

Note 

Even if you were to declare the variable in the second hatch, the Select statement would fail because you cannot use a string expression or variable in the From clause.

You cannot use a database context from the other batch, either:

      Use Asset      exec ('Use Northwind select * from Employees')      select * from Employees   -- Error 

Query by Form

One of the simplest ways to create a search form in a client application is to list all the fields in a table as text boxes on a form. The user will fill some of them in, and they can be interpreted as search criteria.

The trouble with this kind of solution is that, most of the time, the user will leave blank most of the text boxes. This does not mean that the user wants to find only those records in which the values of the blank fields are set to empty strings, but rather that those fields should not be included in the criteria. Stored procedures have a static structure, but something dynamic would be more appropriate to launch this kind of query.

The following stored procedure assembles a character-string query. The contents of the Where clause are based on the criteria that were specified (that is, fields that were not set to null). When all components are merged, the query returns a list of matching contacts:

      Create Procedure dbo.ap_QBF_Contact_List      -- Dynamically assemble a query based on specified parameters.      -- Test: exec dbo.ap_QBF_Contact_List OchvFirstName = 'Dejan'1'      (         @chvFirstName     varchar (30)     = NULL,         @chvLastName      varchar (30)     = NULL,         @chvPhone         typPhone         = NULL,         @chvFax           typPhone         = NULL,         @chvEmail         typ Email        = NULL,         @insOrgUnitId     smallint         = NULL,         @chvUserName      varchar (50)     = NULL,         @debug            int              = 0      )      As      set nocount on      Declare @chvQuery nvarchar(max),              @chvWhere nvarchar(max)      Select @chvQuery = 'SET QUOTED_IDENTIFIER OFF SELECT * FROM dbo.Contact',             @chvWhere = ''      If @chvFirstName is not null        Set @chvWhere = @chvWhere + ' FirstName =" '                      + @chvFirstName + '" AND'      If @chvLastName is not null         Set @chvWhere = @chvWhere + ' LastName =" ' + @chvLastName + '"  AND'      If @chvPhone is not null      set @chvWhere = @chvWhere + ' Phone =" '                    + @chvPhone + '" AND'      If @chvFax is not null         set @chvWhere = @chvWhere + ' Fax =" ' + @chvFax + '" AND'      If @chvEmail is not null         set @chvWhere = @chvWhere + ' Email =" '                       + @chvEmail + '" AND'      If @insOrgUnitId is not null          set @chvWhere = @chvWhere + ' OrgUnitId = '                        + @insOrgUnitId + ' AND'      If @chvUserName is not null         set @chvWhere = @chvWhere + ' UserName =" '                       + @chvUserName + '"'      if @debug <> 0         select @chvWhere chvWhere      - - remove ' AND' from the end of string      begin try          If Substring(@chvWhere, Len(@chvWhere) - 3, 4) = ' AND'          -set @chvWhere = Substring(@chvWhere, 1, Len(@chvWhere) - 3)      end try      begin Catch         Raiserror ('Unable to remove last AND operator.', 16, 1)         return      end catch      if @debug <> 0         select @chvWhere chvWhere      begin try      If Len(@chvWhere) > 0         set @chvQuery = @chvQuery + ' WHERE ' + @chvWhere      if @ debug <> 0         select @chvQuery Query      -- get contacts         exec (@chvQuery)      end try      begin Catch         declare @s varchar(max)         set @s = 'Unable to execute new query: ' + @chvQuery         Raiserror (@s, 16, 2)         return      end catch      return 

The procedure is composed of sections that test the presence of the criteria in each parameter and add them to the Where clause string. At the end, the string with the Select statement is assembled and executed. Figure 15-1 shows the result of the stored procedure (along with some debugging information).

Figure 15-1: The results of Query By Form

Tip 

You are right if you think that this solution can probably be implemented more easily using client application code (for example, in Visual Basic).

Data Script Generator

Database developers often need an efficient way to generate a set of Insert statements to populate a table. In some cases, data is already in the tables, but it may need to be re-created in the form of a script to be used to deploy it on another database server, such as a test server.

One solution is to assemble an Insert statement dynamically for every row in the table using a simple Select statement:

      select 'Insert dbo.AcquisitionType values('      + Convert(varchar, AcquisitionTypeId)      + ', ''' + AcquisitionType      + ''')' from dbo.AcquisitionType 

When you set Query Analyzer to Result In Text and execute such a statement, you get a set of Insert statements for each row:

      --------------------------------------------------------------      Insert dbo.AcquisitionType values (1,  'Purchase')      Insert dbo.AcquisitionType values (2,  'Lease')      Insert dbo.AcquisitionType values (3,  'Rent')      Insert dbo.AcquisitionType values (4,  'Progress Payment')      Insert dbo.AcquisitionType values (5,  'Purchase Order')      (5 row(s) affected) 

The Insert statements can now be encapsulated inside a pair of Set Insert_Identity statements and then saved as a script file or copied (through Clipboard) to the Query pane. This process can save you a substantial amount of typing time, but you still have to be very involved in creating the original Select statement that generates the desired results.

An alternative solution is to use the util.ap_DataGenerator stored procedure:

      alter proc util.ap_DataGenerator      -- generate a set of Insert statements      -- that can reproduce content of the table.      -- It does not handle very very long columns.         @table sysname = 'Inventory',         @debug int = 0      -- debug: exec util.ap_DataGenerator (Stable = 'Location', (Sdebug = 1 as      declare (SchvVal varchar(max)      declare (SchvSQL varchar(max)      declare (SchvColList varchar(max)      declare (SintColCount smallint      declare (Si small int      set (SchvColList = ' '      set (SchvVal = ' '      select (SintColCount = Max([ORDINAL_POSITION]},           @i = 1      FROM     [INFORMATION_SCHEMA].[COLUMNS]      where    [TABLE_NAME] = (Stable      while @i >= @intColCount      begin         SELECT @chvVal = @schvVal         + '+'',''+case when ' + [COLUMN_NAME]         + ' is null then ''null'' else '         + case when DATA_TYPE in ('varchar', 'nvarchar', 'datetime',                               'smalldatetime', 'char', 'nchar')                        then'''''''''''+convert(varchar(max),'               else '+ convert(varchar(max),'            end            + convert(varchar(max),[COLUMN_NAME])            + case when DATA_TYPE in ('varchar', 'nvarchar', 'datetime',                                  'smalldatetime','char', 'nchar')                            then '}+''''''''                  else ') '            end      + ' end '      FROM [INFORMATION SCHEMA].[COLUMNS]         where [TABLE_NAME] = @table         and [ORDINAL POSITION] = @i      --- if @debug <> 0 select @chvVal [@chvVa1]          -- get column list         SELECT @chvColList = @chvColList               + ',' + convert(varchar(max),[COLUMN_NAME])         FROM [INFORMATION_SCHEMA].[COLUMNS]         where [TABLE_NAME] = @table         and [ORDINAL_POSITION] = @i         set @i = @i + 1      end      if @debug <> 0 select @chvColList [@chvColList]      -- remove first comma      set @chvColList = substring(@chvColList, 2, len(@chvColList)      set @chvVal = substring(@chvVal, 6, len(@chvVal))      -- assemble a command to query the table to assemble everything      set @chvSQL = 'select ''Insert dbo.' + @table            + ' (' + @chvColList +') values (' ' + '           + @chvVal + ' + '')''from ' +@table      -- get result      if @debug <> 0 select @chvSQL chvSQL      exec(@chvSQL)      return 

The procedure is based on the INFORMATION_SCHEMA.COLUMNS system view. It simply loops through columns of a specified table and collects data in two variables. The @chvColList variable collects a comma-delimited list of columns:

      @chvColList      ------------------------------------------------------------------      ,LocationId,Location,Address,City,Provinceld,Country      (1 row(s) affected) 

The @chvVal variable collects a set of Case statements that will be used to generate Values clauses of the Insert statements (I formatted it so that you can understand it more easily):

      @chvVal      --------------------------------------------------      + ' , ' +      case when LocationId is null then 'null'           else + convert(varchar(max),LocationId)      end+', ' +      case when Location is null then 'null'           else ''''+convert(varchar(max),Location)+''''      end + ' , ' + case when Address is null then 'null'           else ''''+convert(varchar(max),Address)+''''      end + ' , ' + case when City is null then 'null'           else ''''+convert(varchar(max),City)+''''      end + ' , ' + case when ProvinceId is null then 'null'           else ''''+convert(varchar(max),Provinceld)+''''      end + ' , ' + case when Country is null then 'null'           else ''''+convert(varchar(max),Country)+''''      end      (1 row(s) affected) 

Data for this string is gathered in a similar manner, but the code is more complex in order to handle nullability of columns and to insert different delimiters for different data types.

In the final step before execution, these strings are put together in a Select statement that will retrieve data from the table (again, I formatted the string so that you can more easily understand its structure):

      chvSQL      ------------------------------------------------------------      select 'Insert dbo.Location(LocationId,Location,Address,City,      ProvinceId,Country) values (      '+case when LocationId is null then 'null'             else + convert(varchar(max),LocationId)      end +',      '+case when Location is null then 'null'             else ''''+convert(varchar(max),Location)+''''      end +', '+case when Address is null then 'null'             else ''''+convert(varchar(max),Address)+''''      end +',      '+case when City is null then 'null'             else ''''+convert(varchar(max),City)+''''      end +',      '+case when ProvinceId is null then 'null'             else ''''+convert(varchar(max),ProvinceId)+''''      end +', '+case when Country is null then 'null'             else ''''+convert(varchar(max),Country)+''''       end + ')' from Location      (1 row(s) affected) 

The result is a set of Insert statements:

      Insert dbo.Location(LocationId,Location,Address,City,ProvinceId,Country)      values (2,'Trigon Tower','1 Trigon Av.','Toronto','ON ','Canada')      Insert dbo.Location(LocationId,Location,Address,City,ProvinceId,Country)      values (3,'Sirmium Place','3 Sirmium St.','Toronto','ON ','Canada')      Insert dbo.Location(LocationId,Location,Address,City,ProvinceId,Country)      values (4,'Singidunum Plaza','27 Trigon Av.','Toronto','ON ','Canada')      Insert dbo.Location(LocationId,Location,Address,City,ProvinceId,Country)      values (5,'Mediana Tower','27 Istlington St.','London','ON ','Canada') 

Using the sp_executesql Stored Procedure

An important advantage stored procedures have over ad hoc queries is their capability to reuse an execution plan. SQL Server, and developers working in it, can use two methods to improve the reuse of queries and batches that are not designed as stored procedures. Autoparameterization is covered in Appendix B. This section focuses on using a system stored procedure to enforce parameterization of a query.

If you know that a query will be re-executed with different parameters and that reuse of its execution plan will improve performance, you can use the sp_executesql system stored procedure to execute it. This stored procedure has the following syntax:

      sp_executesql [@stmt =] stmt      [          {, [@params =] N'@parameter_name data_type [,...n]' }          {, [@paraml =] 'valuel' [,...n] }      ] 

The first parameter, @stmt, is a string containing a batch of Trans act-SQL statements. If the batch requires parameters, you must also supply their definitions as the second parameter of the sp_executesql procedure. The parameter definition is followed by a list of the parameters and their values. The following script executes one batch twice, each execution using different parameters:

      EXECUTE sp_executesq1          @Stmt = N'SELECT * FROM Asset.dbo.Contact WHERE ContactId = Old',          @Farms = N'@Id int',          @Id = 11      EXECUTE sp_executesq1          @Stmt = N'SELECT * FROM Asset.dbo.Contact WHERE ContactId = @Id',          @Farms = N'@Id int',          @Id = 313 

There is one unpleasant requirement to this exercise. If all database objects are not fully qualified (that is, hard-coded with the database name and schema name), the SQL Server engine will not reuse the execution plan.

In some cases, you may be able to ensure that all database objects are fully qualified. However, this requirement becomes a problem if you are building a database that will be deployed under a different name or even if you use more than one instance of the database in your development environment (for example, one instance for development and one for testing).

The solution is to obtain the name of a current database using the Db_Name() function. You can then incorporate it in a query:

      Declare @chvQuery nvarchar(200)      Set @chvQuery = N'Select * From ' + DB_NAME()                    + N'.dbo.Contact Where ContactId = @Id'      EXECUTE sp_executesql @stmt = @chvQuery,                            @Farms = N'@Id int',                            @Id = 1      EXECUTE sp_executesql @stmt = @chvQuery,                            @Farms = N'@Id int',                            @Id = 313 

Solutions based on this system stored procedure with parameters are better than solutions based on the execution of a character string using the Execute statement. The execution plan for the latter is seldom reused. It might happen that it will be reused only when parameter values supplied match those in the execution plan. Even in a situation in which you are changing the structure of a query, the number of possible combinations of query parameters is finite (and some of them are more probable than others). Therefore, reuse will be much more frequent if you force parameterization using sp_executesql.

When you use Execute, the complete batch has to be assembled in the form of a string each time. This requirement also takes time. If you are using sp_executesql, the batch will be assembled only the first time. All subsequent executions can use the same string and supply an additional set of parameters.

Parameters that are passed to sp_executesql do not have to be converted to characters. That time is wasted when you are using Execute, in which case parameter values of numeric type must be converted. By using all parameter values in their native data type with sp_executesql, you may also be able to detect errors more easily.

Security Implications

As a reminder, the following are two security concerns that are important in the case of dynamically assembled queries:

• Permissions on underlying tables

• SQL injection

Permissions on Underlying Tables

The fact that a caller has permission to execute the stored procedure that assembles the dynamic query does not mean that the caller has permission to access the underlying tables. You have to assign these permissions to the caller separately. Unfortunately, this requirement exposes your database—someone might try to exploit the fact that you are allowing more than the execution of predefined stored procedures.

SQL Injection

Dynamically assembled queries present an additional security risk. A malicious user could use a text box to type something like this:

      Acme' DELETE INVENTORY -- 

A stored procedure (or application) can assemble this into a query, such as

      Select *      from vlnventory      Where Make = 'Acme' DELETE INVENTORY --' 

The quote completes the parameter value and, therefore, the Select statement, and then the rest of the query is commented out with two dashes. Data from an entire table could be lost this way.

Naturally, a meticulous developer, such as you, would have permissions set to prevent this kind of abuse. Unfortunately, damage can be done even using a simple Select statement:

      Acme' SELECT * FROM CUSTOMERS -- 

In this way, your competitor might get a list of your customers:

      Select *      from vlnventory      Where Make = 'Acme' SELECT * FROM CUSTOMERS --' 

A hack like this is possible not just on string parameters; it might be even easier to perform on numeric parameters. A user can enter the following:

      122121 SELECT * FROM CUSTOMERS 

The result might be a query such as this:

      Select *      from vInventory      Where InventoryId = 122121 SELECT * FROM CUSTOMERS 

Fortunately, it's not too difficult to prevent this. No, you do not have to parse strings for SQL keywords; it's much simpler. The application must validate the content of text boxes. If a number or date is expected, the application must make sure that values really are of numeric or date data types. If text (such as a T-SQL keyword) is added, the application should prompt the user to supply a value of the appropriate data type.

Unfortunately, if a text box is used to specify a string, there is little that you can validate. The key is to prevent the user from adding a single quote (') to the query. There are several ways to do this. The quote is not a legal character in some types of fields (such as keys, e-mails, postal codes, and so forth) and the application should not accept it in such fields. In other types of fields (such as company names, personal names, descriptions, and so on), use of quotes may be valid. In that case, in the procedure that assembles the string, you should replace a single quote—char (39)—with two single quotes—char (39) +char(39) —and SQL Server will find a match for the string:

      set @chvMake = Replace(@chvMake, char(39), char(39) + char(39)) 

The dynamic query will become a query that works as expected:

      Select *      from vInventory      Where Make = 'Dejan''s Computers Inc.' 

In the case in which someone tries to inject a SQL statement, SQL Server will just treat it as a part of the parameter string:

      Select *      from vInventory      Where Make = 'Dejan'' SELECT * FROM CUSTOMERS --' 

Another possibility is to replace a single quote —char (39) —with a character that looks similar onscreen but that is stored under a different code, such as ()—char (96). Naturally, you have to make this substitution for all text boxes (on both data entry and search pages). In some organizations, this substitution might be inappropriate, since existing databases may already contain quotes.

Note 

You are at risk not only when you are passing strings directly from a GUI into the stored procedure that is assembling a query, hut also when an application is reading the field with injected SQL into a variable that will he used in a dynamic query. A user might attempt to weaken security with some administrative procedure—there is no need for direct interaction with the code by the attacker. Therefore, you should convert all string input parameters and local variables that are participating in the dynamic assembly of the query.

You also might want to prevent users from injecting special characters (such as wild cards) into strings that will be used in Like searches. The following function will make a string parameter safe for use in dynamic queries:

      CREATE FUNCTION util.fnSafeDynamicString      -- make string parameters safe for use in dynamic strings         (@chvInput nvarchar(max),          @bitLikeSafe bit = 0) -- set to 1 if string will be used in LIKE          RETURNS nvarchar(max)      AS      BEGIN          declare @chvOutput nvarchar(max)          set (SchvOutput = Replace(@chvInput, char(39), char(39) + char(39))          if @bitLikeSafe = 1      begin      -- convert square bracket      set (SchvOutput = Replace(@chvOutput, '[', '[[]') -- convert wild cards      set (SchvOutput = Replace(@chvOutput, '%', '[%]') set (SchvOutput = Replace(@chvOutput, '_', '[_]')      end      RETURN (@chvOutput} END 

You can test the function with the following:

      SELECT 'select * from vInventory where Make = '''      + util.fnSafeDynamicString ('Dejan' + char(39) + 's Computers Inc.', 0)      + ' ' ' ' 

This test simulates a case in which a user enters the following text on the screen:

      Dejan' s Computers Inc. 

The result becomes

      select * from vInventory where Make = 'Dejan''s Computers Inc.'      (1 row(s) affected) 

In the case of a Like query, you must prevent the user from using wild cards. The following query simulates a case in which a user enters % in the text box. It also assumes that the application or stored procedure is adding another % at the end of the query.

      SELECT 'select * from vInventory where Make like '''      + util.fnSafeDynamicString ('%a', 1)      + '%' ' ' 

When you set the second parameter of the function to 1, the function replaces the first % character with [%], in which case it will not serve as a wild card:

      -----------------------------------------------      select * from vInventory where Make = '[%]a%*      (1 row(s) affected)