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).
| 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)
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
