SQL Server 2012动态SQL

SQL Server 2012动态SQL - 存储过程 - 获取语法错误(SQL Server 2012 dynamic SQL - stored procedure - getting syntax error)

我正在编写脚本以在数据库中生成存储过程，其当前模式符号将是未知的（想想共享主机）。

我决定在存储过程中使用动态SQL，以便Web应用程序可以根据用户定义的设置将数据库模式传递给SQL Server，以便正确触发。

当我开始编写存储过程时，我注意到动态SQL打开了一个我通常不会拥有的整个SQL注入问题所以我重新编写了解决此问题的过程。 但是，即使SQL允许我运行脚本来生成存储过程，每次我尝试运行测试存储过程时，都会出现语法错误

关键字'WHERE'附近的语法不正确

我相信这与模式的参数有关，但我不知道为什么这不起作用？ 我输入了模式的值dbo 。

/* Name : usp_GetTestTicker Description : returns test ticker */ if not exists (select * from dbo.sysobjects where id = object_id(N'usp_GetTestTicker') and OBJECTPROPERTY(id, N'IsProcedure') = 1) BEGIN DECLARE @sql as nvarchar(150) SET @sql = 'CREATE procedure usp_GetTestTicker AS' EXEC(@sql) END GO ALTER PROCEDURE usp_GetTestTicker @schema VARCHAR(25), @TickerItemId INT AS SET NOCOUNT ON BEGIN DECLARE @sql_cmd NVARCHAR(MAX) DECLARE @sql_params NVARCHAR(MAX) SET @sql_cmd = N'SELECT * FROM @schema.TickerItem WHERE TickerItemId = @TickerItemId' SET @sql_params = N'@schema VARCHAR(25), @TickerItemId INT' EXEC sp_executesql @sql_cmd, @sql_params, @schema, @TickerItemId END GO

I am writing scripts to generate stored procedures within a database whose current schema notation will be unknown (think shared hosting).

I have decided to use dynamic SQL within the stored procedures so that the web application can pass the database schema based on a user defined setting to the SQL Server in order for it to fire properly.

When I started writing the stored procedures, I noticed that dynamic SQL opens up a whole SQL injection problem I would not normally have so I re-wrote the procedure to combat this. However even though SQL allows me to run the script to generate the stored procedure, each time I try to run the test stored procedure, I get a syntax error

Incorrect syntax near the keyword 'WHERE'

I believe this is to do with the parameter for the schema but I am at a loss as to why this is not working? I am entering the value dbo for the schema.

/* Name : usp_GetTestTicker Description : returns test ticker */ if not exists (select * from dbo.sysobjects where id = object_id(N'usp_GetTestTicker') and OBJECTPROPERTY(id, N'IsProcedure') = 1) BEGIN DECLARE @sql as nvarchar(150) SET @sql = 'CREATE procedure usp_GetTestTicker AS' EXEC(@sql) END GO ALTER PROCEDURE usp_GetTestTicker @schema VARCHAR(25), @TickerItemId INT AS SET NOCOUNT ON BEGIN DECLARE @sql_cmd NVARCHAR(MAX) DECLARE @sql_params NVARCHAR(MAX) SET @sql_cmd = N'SELECT * FROM @schema.TickerItem WHERE TickerItemId = @TickerItemId' SET @sql_params = N'@schema VARCHAR(25), @TickerItemId INT' EXEC sp_executesql @sql_cmd, @sql_params, @schema, @TickerItemId END GO

最满意答案

要防止SQL注入，您需要根据sys.schemas表验证模式，例如

ALTER PROCEDURE usp_GetTestTicker @schema NVARCHAR(25), @TickerItemId INT AS BEGIN SET NOCOUNT ON IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = @schema) BEGIN -- throw an error here. Your web code will have to handle the error and report an invalid schema END ELSE BEGIN DECLARE @sql_cmd NVARCHAR(MAX), @sql_params NVARCHAR(MAX) SET @sql_cmd = N'SELECT * FROM ' + @schema + '.TickerItem WHERE TickerItemId = @TickerItemId' SET @sql_params = N'@TickerItemId INT' EXEC sp_executesql @sql_cmd, @sql_params, @TickerItemId END END

To prevent SQL injection, you will need to validate the schema against the sys.schemas table, e.g.

ALTER PROCEDURE usp_GetTestTicker @schema NVARCHAR(25), @TickerItemId INT AS BEGIN SET NOCOUNT ON IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = @schema) BEGIN -- throw an error here. Your web code will have to handle the error and report an invalid schema END ELSE BEGIN DECLARE @sql_cmd NVARCHAR(MAX), @sql_params NVARCHAR(MAX) SET @sql_cmd = N'SELECT * FROM ' + @schema + '.TickerItem WHERE TickerItemId = @TickerItemId' SET @sql_params = N'@TickerItemId INT' EXEC sp_executesql @sql_cmd, @sql_params, @TickerItemId END END