主页 / dba / 问题 / 111339
Accepted
Phrancis
Asked: 2015-08-18 14:29:23 +0800 CST

用户共享查询：动态 SQL 与 SQLCMD

  • 772

我必须重构并记录一些foo.sql查询，这些查询将由数据库技术支持团队共享（用于客户配置和类似的事情）。有些类型的票会定期出现，每个客户都有自己的服务器和数据库，但除此之外，架构都是相同的。

存储过程目前不是一个选项。我正在争论是否使用动态或 SQLCMD，我没有使用太多，因为我在 SQL Server 上有点新。

SQLCMD 脚本我觉得对我来说绝对“看起来”更干净，更容易阅读并根据需要对查询进行小的更改，但也强制用户启用 SQLCMD 模式。动态更困难，因为语法高亮由于使用字符串操作编写查询而丢失。

这些正在使用 Management Studio 2012 SQL 版本 2008R2 进行编辑和运行。这两种方法的优缺点是什么，或者 SQL Server 的一些“最佳实践”在一种方法或另一种方法上是什么？其中一个比另一个“更安全”吗？

动态示例：

declare @ServerName varchar(50) = 'REDACTED';
declare @DatabaseName varchar(50) = 'REDACTED';
declare @OrderIdsSeparatedByCommas varchar(max) = '597336, 595764, 594594';

declare @sql_OrderCheckQuery varchar(max) = ('
use {@DatabaseName};
select 
    -- stuff
from 
    {@ServerName}.{@DatabaseName}.[dbo].[client_orders]
        as "Order"
    inner join {@ServerName}.{@DatabaseName}.[dbo].[vendor_client_orders]
        as "VendOrder" on "Order".o_id = "VendOrder".vco_oid
where "VendOrder".vco_oid in ({@OrderIdsSeparatedByCommas});
');
set @sql_OrderCheckQuery = replace( @sql_OrderCheckQuery, '{@ServerName}',   quotename(@ServerName)   );
set @sql_OrderCheckQuery = replace( @sql_OrderCheckQuery, '{@DatabaseName}', quotename(@DatabaseName) );
set @sql_OrderCheckQuery = replace( @sql_OrderCheckQuery, '{@OrderIdsSeparatedByCommas}', @OrderIdsSeparatedByCommas );
print   (@sql_OrderCheckQuery); -- For debugging purposes.
execute (@sql_OrderCheckQuery);

SQLCMD 示例：

:setvar ServerName "[REDACTED]";
:setvar DatabaseName "[REDACTED]";
:setvar OrderIdsSeparatedByCommas "597336, 595764, 594594"

use $(DatabaseName)
select 
    --stuff
from 
    $(ServerName).$(DatabaseName).[dbo].[client_orders]
        as "Order"
    inner join $(ServerName).$(DatabaseName).[dbo].[vendor_client_orders]
        as "VendOrder" on "Order".o_id = "VendOrder".vco_oid
where "VendOrder".vco_oid in ($(OrderIdsSeparatedByCommas));
sql-server dynamic-sql

1 个回答

  1. Best Answer

    只是为了解决这些问题：

    • 从技术上讲，这两个选项都是“动态”/即席查询，在提交之前不会被解析/验证。并且两者都容易受到 SQL 注入的影响，因为它们没有参数化（尽管使用 SQLCMD 脚本，如果您从 CMD 脚本中传入一个变量，那么您确实有机会替换'为''，这可能会或可能不会工作，具体取决于变量被使用）。

    • 每种方法都有优点和缺点：

      • SSMS 中的 SQL 脚本可以很容易地编辑（如果需要，那就太好了），并且使用结果比使用 SQLCMD 的输出更容易。不利的一面是，用户在 IDE 中，因此很容易弄乱 SQL，而 IDE 可以很容易地在不知道 SQL 的情况下进行各种更改。
      • 通过 SQLCMD.EXE 运行脚本不允许用户轻松进行更改（无需在编辑器中编辑脚本然后先保存）。如果用户不应该更改脚本，那就太好了。此方法还允许记录它的每次执行。不利的一面是，如果需要定期编辑脚本，那将非常麻烦。或者，如果用户需要扫描 100k 行的结果集和/或将这些结果复制到 Excel 或其他东西，那么这种方法也很困难。

    如果您的支持人员没有进行临时查询而只是填写这些变量，那么他们不需要在 SSMS 中编辑这些脚本并进行不需要的更改。

    我会创建 CMD 脚本来提示用户输入所需的变量值，然后使用这些值调用SQLCMD.EXE 。CMD 脚本甚至可以将执行记录到文件中，并附上时间戳和提交的变量值。

    每个 SQL 脚本创建一个 CMD 脚本并放置在网络共享文件夹中。用户双击 CMD 脚本，它就可以工作了。

    这是一个示例：

    • 提示用户输入服务器名称（还没有错误检查）
    • 提示用户输入数据库名称
      • 如果留空，它将列出指定服务器上的数据库并再次提示
      • 如果数据库名无效，会再次提示用户
    • 提示用户输入 OrderIDsSeparatedByCommas
      • 如果为空，则再次提示用户
    • 运行 SQL 脚本，将 的值%OrderIDsSeparatedByCommas%作为 SQLCMD 变量传递$(OrderIDsSeparatedByCommas)
    • 将执行日期、时间、ServerName、DatabaseName 和 OrderIDsSeparatedByCommas 记录到以运行脚本的 Windows 登录名命名的日志文件中（这样，如果日志目录是网络并且有多个人使用它，则不会有任何写入日志文件上的争用，例如如果每个条目都将 USERNAME 记录到文件中，则可能会出现这种情况）
      • 如果日志文件目录不存在，则创建

    测试 SQL 脚本（名称：FixProblemX.sql）：

    SELECT  *
FROM    sys.objects
WHERE   [schema_id] IN ($(OrderIdsSeparatedByCommas));

    CMD 脚本（命名：FixProblemX.cmd）：

    @ECHO OFF
SETLOCAL ENABLEDELAYEDEXPANSION

SET ScriptLogPath=\\server\share\RunSqlCmdScripts\LogFiles

CLS

SET /P ScriptServerName=Please enter in a Server Name (leave blank to exit): 

IF "%ScriptServerName%" == "" GOTO :ThisIsTheEnd

REM echo %ScriptServerName%

:RequestDatabaseName
ECHO.
SET /P ScriptDatabaseName=Please enter in a Database Name (leave blank to list DBs on %ScriptServerName%): 

IF "%ScriptDatabaseName%" == "" GOTO :GetDatabaseNames

SQLCMD -b -E -W -h-1 -r0 -S %ScriptServerName% -Q "SET NOCOUNT ON; IF (NOT EXISTS(SELECT [name] FROM sys.databases WHERE [name] = N'%ScriptDatabaseName%')) RAISERROR('Invalid DB name!', 16, 1);" 2> nul

IF !ERRORLEVEL! GTR 0 (
    ECHO.
    ECHO That Database Name is invalid. Please try again.

    SET ScriptDatabaseName=
    GOTO :RequestDatabaseName
)

:RequestOrderIDs
ECHO.
SET /P OrderIdsSeparatedByCommas=Please enter in the OrderIDs (separate multiple IDs with commas): 

IF "%OrderIdsSeparatedByCommas%" == "" (

    ECHO.
    ECHO Don't play me like that. You gots ta enter in at least ONE lousy OrderID, right??
    GOTO :RequestOrderIDs
)


REM Finally run SQLCMD!!
SQLCMD -E -W -S %ScriptServerName% -d %ScriptDatabaseName% -i FixProblemX.sql -v OrderIdsSeparatedByCommas=%OrderIdsSeparatedByCommas%

REM Log this execution
SET ScriptLogFile=%ScriptLogPath%\%~n0_%USERNAME%.log
REM echo %ScriptLogFile%

IF NOT EXIST %ScriptLogPath% MKDIR %ScriptLogPath%

ECHO %DATE% %TIME% ServerName=%ScriptServerName%    DatabaseName=[%ScriptDatabaseName%] OrderIdsSeparatedByCommas=%OrderIdsSeparatedByCommas%   >> %ScriptLogFile%

GOTO :ThisIsTheEnd

:GetDatabaseNames
ECHO.
SQLCMD -E -W -h-1 -S %ScriptServerName% -Q "SET NOCOUNT ON; SELECT [name] FROM sys.databases ORDER BY [name];"
ECHO.
GOTO :RequestDatabaseName

:ThisIsTheEnd
PAUSE

    请务必ScriptLogPath在脚本顶部编辑变量。

    此外，SQL 脚本（由SQLCMD.EXE-i的命令行开关指定）可能会受益于具有完全限定的路径，但并不完全确定。

    • 13

相关问题