数据库查询中的特殊字符失败

Question

我已经创建了一个名为Suri的_DB的数据库，下面的查询在执行时失败了。如果数据库名称不带单引号，则查询将成功执行。查询是

 USE [master];

IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE [Id] = OBJECT_ID('tempdb..#tmp_filegroups'))

BEGIN    
DROP TABLE #tmp_filegroups    
END

CREATE TABLE #tmp_filegroups
(
[DbName] nvarchar(128),
[DbId] int,
[GroupName] nvarchar(128),
[GroupId] int,
[IsFileStream] bit
)

IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE [Id] = OBJECT_ID('tempdb..#tmp_dbandlogfiles'))
BEGIN
DROP TABLE #tmp_dbandlogfiles
END

CREATE TABLE #tmp_dbandlogfiles
(
[DbName] nvarchar(128),
[DbId] int,
[GroupId] int,
[Name] nvarchar(128),
[FileName] nvarchar(260),
[Size] float,
[IsReadOnlyMedia] bit,
[IsReadOnly] bit,
[IsOffline] bit,
[IsSparse] bit,
[IsPrimaryFile] bit,
[SpaceUsed] float,
[FileType] int
)    
    
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE [Id] = OBJECT_ID('tempdb..#tmp_fulltextcatalogfiles'))    
BEGIN
DROP TABLE #tmp_fulltextcatalogfiles
END

CREATE TABLE #tmp_fulltextcatalogfiles
(
[DbName] nvarchar(128),
[DbId] int,
[Name] nvarchar(128),
[Path] nvarchar(260)
)

DECLARE @DB NVARCHAR(128)
DECLARE @DBID int
DECLARE @CMD NVARCHAR(MAX)

DECLARE Databases CURSOR FAST_FORWARD FOR
SELECT name,dbid FROM master..sysdatabases
WHERE name in (N'Suri''s _DB')

OPEN Databases

FETCH NEXT FROM Databases INTO @DB, @DBID

WHILE (@@FETCH_STATUS = 0)
BEGIN

BEGIN TRY

SELECT @CMD = 'use ' + '[' + @DB + ']' + '; select N''' + @DB + ''' as [DbName],' + CAST(@DBID as varchar(30)) + ' as [DbId],s.name as [GroupName],s.data_space_id as [GroupId], CAST(case s.type when ''FD'' then 1 else 0 end AS bit) AS [IsFileStream] FROM sys.filegroups as s ORDER by [GroupId] ASC'

INSERT INTO #tmp_filegroups execute(@CMD)

SELECT @CMD = 'use ' + '[' + @DB + ']' + '; select N''' + @DB + ''' as [DbName],' + CAST(@DBID as varchar(30)) + ' as [DbId],s.data_space_id as [GroupId],s.name AS [Name],s.physical_name AS [FileName],s.size * CONVERT(float,8) AS [Size],s.is_media_read_only AS [IsReadOnlyMedia],s.is_read_only AS [IsReadOnly],CAST(case s.state when 6 then 1 else 0 end AS bit) AS [IsOffline],s.is_sparse AS [IsSparse],CAST(CASE s.file_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [IsPrimaryFile],CAST(fileproperty(s.name,''SpaceUsed'') AS float) * CONVERT(float,8) AS [SpaceUsed], Type AS [FileType] FROM sys.database_files as s'

INSERT INTO #tmp_dbandlogfiles execute(@CMD)



SELECT @CMD = 'use ' + '[' + @DB + ']' + '; select N''' + @DB + ''' as [DbName],' + CAST(@DBID as varchar(30)) + ' as [DbId],cat.name as [Name],cat.path as [Path] FROM sys.fulltext_catalogs AS cat ORDER by [Name] ASC'

INSERT INTO #tmp_fulltextcatalogfiles execute(@CMD)

END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ERRMESS
END CATCH

FETCH NEXT FROM Databases INTO @DB, @DBID

END

CLOSE Databases

DEALLOCATE Databases

select [DbName],[DbId],[GroupName],[GroupId],[IsFileStream] from #tmp_filegroups

DROP TABLE #tmp_filegroups

为什么会失败？

我试着转义单引号

Answer 1

生成sql查询字符串时，dbname的单引号不会终止。将任何单引号替换为2个单引号以终止字符。

SELECT @CMD = 'use ' + '[' + @DB + ']' + '; select N''[' + replace(@DB, '''', '''''') + ']'' as [DbName],' + CAST(@DBID as varchar(30)) + ' as [DbId],s.name as [GroupName],s.data_space_id as [GroupId], CAST(case s.type when ''FD'' then 1 else 0 end AS bit) AS [IsFileStream] FROM sys.filegroups as s ORDER by [GroupId] ASC'

INSERT INTO #tmp_filegroups execute(@CMD)

SELECT @CMD = 'use ' + '[' + @DB + ']' + '; select N''[' + replace(@DB, '''', '''''') + ']'' as [DbName],' + CAST(@DBID as varchar(30)) + ' as [DbId],s.data_space_id as [GroupId],s.name AS [Name],s.physical_name AS [FileName],s.size * CONVERT(float,8) AS [Size],s.is_media_read_only AS [IsReadOnlyMedia],s.is_read_only AS [IsReadOnly],CAST(case s.state when 6 then 1 else 0 end AS bit) AS [IsOffline],s.is_sparse AS [IsSparse],CAST(CASE s.file_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [IsPrimaryFile],CAST(fileproperty(s.name,''SpaceUsed'') AS float) * CONVERT(float,8) AS [SpaceUsed], Type AS [FileType] FROM sys.database_files as s'

INSERT INTO #tmp_dbandlogfiles execute(@CMD)

SELECT @CMD = 'use ' + '[' + @DB + ']' + '; select N''[' + replace(@DB, '''', '''''') + ']'' as [DbName],' + CAST(@DBID as varchar(30)) + ' as [DbId],cat.name as [Name],cat.path as [Path] FROM sys.fulltext_catalogs AS cat ORDER by [Name] ASC'

INSERT INTO #tmp_fulltextcatalogfiles execute(@CMD)