Как мне передать имя таблицы в хранимый процесс?
Я только что натолкнулся на странную вещь... на нашем сайте есть код, который принимает гигантский оператор SQL, модифицирует его в коде, выполняя поиск и замену на основе некоторых пользовательских значений, а затем передает его на SQL Server как запрос.
Я думал, что это будет чище, как параметризованный запрос к хранимому процессу, с пользовательскими значениями в качестве параметров, но когда я посмотрел более внимательно, я понимаю, почему они могут это делать... таблица, из которой они выбирают, переменно зависит от этих пользовательских значений.
Например, в одном случае, если значения были ("FOO", "BAR"), запрос в конечном итоге был бы похож на "SELECT * FROM FOO_BAR"
Есть ли простой и понятный способ сделать это? Все, что я пытаюсь сделать, кажется не элегантным.
РЕДАКТИРОВАТЬ: Я мог бы, конечно, динамически генерировать sql в хранимой процедуре, и выполнить его (bleh), но в этот момент мне интересно, получил ли я что-нибудь.
РЕДАКТИРОВАТЬ 2: Рефакторинг имен таблиц каким-либо разумным способом, скажем, наличие их всех в одной таблице с разными именами в качестве нового столбца было бы хорошим способом решить все это, на что несколько человек прямо или на что ссылались. К сожалению, это не вариант в этом случае.
12 ответов
Прежде всего, вы НИКОГДА не должны делать композиции команд SQL в клиентском приложении, как это, вот что такое SQL-инъекция. (Это нормально для инструмента администратора, который не имеет собственных привилегий, но не для приложения общего пользования).
Во-вторых, да, параметризованный вызов хранимой процедуры является более чистым и безопасным.
Однако, поскольку для этого вам потребуется использовать динамический SQL, вы все равно не хотите включать переданную строку в текст выполненного запроса. Вместо этого вы хотите использовать переданную строку для поиска имен реальных таблиц, которые пользователь должен разрешить запрашивать.
Вот простой наивный пример:
CREATE PROC spCountAnyTableRows( @PassedTableName as NVarchar(255) ) AS
-- Counts the number of rows from any non-system Table, *SAFELY*
BEGIN
DECLARE @ActualTableName AS NVarchar(255)
SELECT @ActualTableName = QUOTENAME( TABLE_NAME )
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @PassedTableName
DECLARE @sql AS NVARCHAR(MAX)
SELECT @sql = 'SELECT COUNT(*) FROM ' + @ActualTableName + ';'
EXEC(@SQL)
END
Некоторые справедливо спросили, почему это безопаснее. Надеюсь, маленькие Бобби Таблицы могут прояснить это:
Ответы на дополнительные вопросы:
Одно только QUOTENAME не гарантирует безопасность. MS рекомендует нам использовать его, но они не дали гарантии, что он не может быть обманут хакерами. К вашему сведению, настоящая безопасность - это все о гарантиях. Таблица поиска с QUOTENAME, это другая история, это не ломается.
QUOTENAME не является строго обязательным для этого примера, обычно достаточно поиска трансляции только для INFORMATION_SCHEMA. QUOTENAME здесь, потому что это хорошая форма для безопасности, чтобы включить полное и правильное решение. QUOTENAME здесь фактически защищает от определенной, но схожей потенциальной проблемы, известной как скрытая инъекция.
(Не) к счастью, нет никакого способа сделать это - вы не можете использовать имя таблицы, переданное в качестве параметра для хранимого кода, кроме как для динамического создания SQL. Когда дело доходит до решения, где генерировать SQL-код, я предпочитаю код приложения, а не хранимый код. Код приложения обычно быстрее и проще в обслуживании.
Если вам не нравится решение, с которым вы работаете, я бы предложил более глубокий редизайн (то есть изменить схему / логику приложения, чтобы вам больше не приходилось передавать имя таблицы в качестве параметра где-либо).
Я бы поспорил против динамического генерирования SQL в хранимой процедуре; это приведет к неприятностям и может стать причиной уязвимости при инъекциях.
Вместо этого я бы проанализировал все таблицы, на которые может повлиять запрос, и создал бы какое-то перечисление, которое определило бы, какую таблицу использовать для запроса.
Похоже, вам будет лучше с решением ORM.
Я съеживаюсь, когда вижу динамический sql в хранимой процедуре.
Одна вещь, которую вы можете рассмотреть, - это создать оператор case, содержащий одну и ту же команду SQL, которую вы хотите, один раз для каждой действительной таблицы, затем передать в качестве строки имя таблицы в эту процедуру и попросить case выбрать, какую команду запустить.
Кстати, как сотрудник службы безопасности, приведенное выше предложение о том, чтобы выбрать из системных таблиц, чтобы убедиться, что у вас есть действительная таблица, мне кажется бесполезной операцией. Если кто-то может внедрить, передал QUOTENAME(), тогда внедрение будет работать как на системной таблице, так и на базовой таблице. Единственное, что помогает в этом, - убедиться, что это правильное имя таблицы, и я думаю, что приведенное выше предложение - лучший подход к этому, поскольку вы вообще не используете QUOTENAME().
@RBarry Young Вам не нужно добавлять скобки к @ActualTableName в строке запроса, поскольку они уже включены в результат запроса в INFORMATION_SCHEMA.TABLES. В противном случае при выполнении будут возникать ошибки.
CREATE PROC spCountAnyTableRows (@PassedTableName as NVarchar (255)) AS - подсчитывает количество строк в любой не системной таблице, БЕЗОПАСНО НАЧИНАЕТСЯ @ActualTableName AS NVarchar(255)
SELECT @ActualTableName = QUOTENAME( TABLE_NAME )
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @PassedTableName
DECLARE @sql AS NVARCHAR(MAX)
--SELECT @sql = 'SELECT COUNT(*) FROM [' + @ActualTableName + '];'
-- changed to this
SELECT @sql = 'SELECT COUNT(*) FROM ' + @ActualTableName + ';'
EXEC(@SQL)
КОНЕЦ
В зависимости от того, является ли набор столбцов в этих таблицах одинаковым или разным, я подхожу к нему двумя способами в более долгосрочной перспективе:
1) если они одинаковые, почему бы не создать новый столбец, который будет использоваться в качестве селектора, значение которого определяется на основе предоставленных пользователем параметров? (это оптимизация производительности?)
2) если они разные, есть вероятность, что обработка с ними также отличается. Таким образом, кажется, что разделение кода select / handle на отдельные блоки и последующий вызов их по отдельности было бы для меня наиболее модульным подходом. Вы будете повторять часть "select * from", но в этом случае набор таблиц, как мы надеемся, конечен.
Позволить вызывающему коду предоставить две произвольные части имени таблицы, чтобы сделать выбор из, кажется очень опасным.
Я не знаю причину, по которой у вас есть данные распределены по нескольким таблицам, но похоже, что вы нарушаете одну из основ. Данные должны быть в таблицах, а не в именах таблиц.
Если таблицы имеют более или менее одинаковый формат, подумайте, лучше ли будет поместить данные в одну таблицу. Это решит вашу проблему с динамическим запросом и сделает структуру базы данных более гибкой.
Я бы любой ценой избегал динамического SQL.
Не самое элегантное решение, но отлично справляется со своей задачей.
PROCEDURE TABLE_AS_PARAMTER (
p_table_name IN VARCHAR2
) AS
BEGIN
CASE p_table_name
WHEN 'TABLE1' THEN
UPDATE TABLE1
SET
COLUMN1 =1
WHERE
ID =1;
WHEN 'TABLE2' THEN
UPDATE TABLE1
SET
COLUMN1 =1
WHERE
ID =2;
END CASE;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK
END TABLE_AS_PARAMTER;
Вы можете использовать подготовленный оператор в хранимой процедуре для выполнения запроса.
Простой пример демонстрации:
DELIMITER //
CREATE PROCEDURE example (IN p_table_name VARCHAR(20), IN p_col VARCHAR(20))
BEGIN
SET @sql = CONCAT('SELECT ', p_col, '_input, ', p_col, '_output', ' FROM ', p_table_name);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END//
DELIMITER ;
Здесь, если у вас есть такие столбцы, как «first_input», «first_output», «второй_вход» и «второй_выход» из таблицы input_output.
Если вы хотите получить данные «first_input», «first_output» из таблицы input_output:
call example('input_output', 'first');
На самом деле, я хотел знать, как передать имя таблицы для создания таблицы в хранимой процедуре. Прочитав некоторые ответы и попытавшись внести некоторые изменения, я наконец смог создать таблицу с именем, переданным в качестве параметра. Вот хранимая процедура для других, чтобы проверить любую ошибку в ней.
ИСПОЛЬЗОВАТЬ [Имя базы данных] GO /****** Объект: StoredProcedure [dbo].[Sp_CreateDynamicTable] Дата скрипта: 20.06.2015 16:56:25 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[Sp_CreateDynamicTable] @tName varchar(255) КАК НАЧАТЬ НАЧАТЬ ВКЛЮЧЕНО; ОБЪЯВИТЬ @SQL nvarchar(макс.)
SET @SQL = N'CREATE TABLE [DBO].['+ @tName + '] (DocID nvarchar(10) null);'
EXECUTE sp_executesql @SQL
КОНЕЦ
Вместо того, чтобы запрашивать таблицы, основанные на пользовательских значениях ввода, вы можете выбрать процедуру вместо этого. то есть
1. Создайте процедуру FOO_BAR_prc и внутри нее вы поместите запрос 'select * from foo_bar', чтобы запрос был предварительно скомпилирован базой данных.
2. Затем, основываясь на пользовательском вводе, выполните правильную процедуру из кода вашего приложения.
Поскольку у вас есть около 50 таблиц, это может оказаться нереальным решением, так как с вашей стороны потребуется много работы.