Как установить имя таблицы в динамическом SQL-запросе?

Я хочу установить имя таблицы в динамическом SQL-запросе. Я успешно попробовал параметр следующим образом:

/* Using sp_executesql */
/* Build and Execute a Transact-SQL String with a single parameter 
value Using sp_executesql Command */

/* Variable Declaration */
DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(100)
/* set the parameter value */
SET @EmpID = 1001
/* Build Transact-SQL String by including the parameter */
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = @EmpID' 
/* Specify Parameter Format */
SET @ParameterDefinition =  '@EmpID SMALLINT'
/* Execute Transact-SQL String */
EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @EmpID

Теперь я хочу получить TABLE NAME динамически, используя параметр, но мне это не удалось. Пожалуйста, помогите мне.


sql parameters sql-server sql-server-2008 dynamicquery
person Neo    schedule 19.12.2013    source источник
comment
Вы не можете параметризовать имя таблицы. Вы просто делаете это вручную на шаге SET @SQLQuery.   -  person MatBailie    schedule 19.12.2013
comment
@MatBailie на самом деле вы можете передать имя таблицы, используя его как строку и используя exec имени строки (оно ведет себя точно так же, как процедура)   -  person HellBaby    schedule 19.12.2013
comment
@hellbaby - И все же вы не можете указать имя таблицы в качестве параметра для sp_executesql, это можно сделать только с помощью замены в строке. Это ОЧЕНЬ разные. Параметризация обеспечивает проверку типов, защиту от атак путем внедрения SQL-кода, повторное использование плана выполнения и т. д. Замена строк другими строками ничего из этого не делает, поэтому параметризация не является не.   -  person MatBailie    schedule 19.12.2013
comment
@MatBailie на самом деле вам не нужна команда sp_executesql, в mssql 2008+ достаточно просто вызвать таким образом: exec @ myqueryconcatenatedvariablewithtablename и вы получили ответ; по поводу инъекций - может ты и прав(кто со знанием дела сломает). Так что зависит от того, где ему нужно использовать это в конце концов ...   -  person HellBaby    schedule 19.12.2013
comment
@HellBaby - Это точно такой же механизм - вы просто подставляете имя таблицы в свою строку, а затем выполняете полученную строку. В вашем примере имя таблицы не является параметром.   -  person MatBailie    schedule 19.12.2013

Ответы (5)


arrow_upward
35
arrow_downward

Имена таблиц не могут быть указаны в качестве параметров, поэтому вам придется создавать строку SQL вручную следующим образом:

SET @SQLQuery = 'SELECT * FROM ' + @TableName + ' WHERE EmployeeID = @EmpID'

Однако убедитесь, что ваше приложение не позволяет пользователю напрямую вводить значение @TableName, так как это сделает ваш запрос уязвимым для SQL-инъекций. Одно из возможных решений этой проблемы см. в этом ответе.

person Dan    schedule 19.12.2013
comment
спасибо, но в любом случае я действительно не хочу этого. не может передать имя таблицы в sp_executesql :( - person Neo; 19.12.2013
comment
@Neo Вы должны иметь возможность использовать sp_executesql, поскольку у вас уже должно быть вставлено правильное имя таблицы при настройке строки SQL. Это основная причина, по которой строки SQL создаются вручную. - person Govind Rai; 09.02.2016
comment
Еще одно соображение состоит в том, чтобы также применить QuoteName к переменной. Это будет правильно оборачивать имя таблицы в обертки объектов, по умолчанию квадратные скобки [...] - person GoldBishop; 04.05.2017

arrow_upward
42
arrow_downward

Чтобы защититься от SQL-инъекций, я обычно стараюсь использовать функции везде, где это возможно. В этом случае вы можете сделать:

...
SET @TableName = '<[db].><[schema].>tblEmployees'
SET @TableID   = OBJECT_ID(TableName) --won't resolve if malformed/injected.
...
SET @SQLQuery = 'SELECT * FROM ' + OBJECT_NAME(@TableID) + ' WHERE EmployeeID = @EmpID'
person galaxis    schedule 16.03.2015

arrow_upward
7
arrow_downward

Попробуй это:

/* Variable Declaration */
DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(100)
DECLARE @TableName AS NVARCHAR(100)
/* set the parameter value */
SET @EmpID = 1001
SET @TableName = 'tblEmployees'
/* Build Transact-SQL String by including the parameter */
SET @SQLQuery = 'SELECT * FROM ' + @TableName + ' WHERE EmployeeID = @EmpID' 
/* Specify Parameter Format */
SET @ParameterDefinition =  '@EmpID SMALLINT'
/* Execute Transact-SQL String */
EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @EmpID
person Saharsh Shah    schedule 19.12.2013
comment
определенно полный ответ, который я искал. - person Ji_in_coding; 21.04.2016
comment
Великолепно! Отлично решил мою проблему. - person Dan Bechard; 01.09.2016
comment
Открыт для SQL-инъекций. Все, что вам нужно сделать, это передать неправильный параметр @TableName, такой как 'sys.databaeses; Изменить роль сервера sysadmin Добавить участника [BadGuy]; --' и там идет ваша система. - person Cody Konior; 17.02.2017

arrow_upward
2
arrow_downward

Это лучший способ получить схему динамически и добавить ее в различные таблицы в базе данных, чтобы динамически получать другую информацию.

select @sql = 'insert #tables SELECT ''[''+SCHEMA_NAME(schema_id)+''.''+name+'']'' AS SchemaTable FROM sys.tables'

exec (@sql)

конечно #tables - это динамическая таблица в хранимой процедуре

person CA Martin    schedule 19.08.2019

arrow_upward
1
arrow_downward

Основываясь на предыдущем ответе @user1172173, посвященном уязвимостям SQL Injection, см. ниже:

CREATE PROCEDURE [dbo].[spQ_SomeColumnByCustomerId](
@CustomerId int,
@SchemaName varchar(20),
@TableName nvarchar(200)) AS
SET Nocount ON
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(100)
DECLARE @Table_ObjectId int;
DECLARE @Schema_ObjectId int;
DECLARE @Schema_Table_SecuredFromSqlInjection NVARCHAR(125)

SET @Table_ObjectId = OBJECT_ID(@TableName)
SET @Schema_ObjectId = SCHEMA_ID(@SchemaName)
SET @Schema_Table_SecuredFromSqlInjection = SCHEMA_NAME(@Schema_ObjectId) + '.' + OBJECT_NAME(@Table_ObjectId)

SET @SQLQuery = N'SELECT TOP 1 ' + @Schema_Table_SecuredFromSqlInjection + '.SomeColumn 
FROM dbo.Customer 
INNER JOIN ' + @Schema_Table_SecuredFromSqlInjection + ' 
ON dbo.Customer.Customerid = ' + @Schema_Table_SecuredFromSqlInjection + '.CustomerId 
WHERE dbo.Customer.CustomerID = @CustomerIdParam 
ORDER BY ' + @Schema_Table_SecuredFromSqlInjection + '.SomeColumn DESC' 
SET @ParameterDefinition =  N'@CustomerIdParam INT'

EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @CustomerIdParam = @CustomerId; RETURN
person outofcoolnames    schedule 16.01.2020