sql server - Providing string variable into Dynamic SQL SELECT statement WITHOUT it being read as a column name? - Stack Overflo

admin管理员组

文章数量:1312889

I have a situation where I am trying to create a query using Dynamic SQL in SSMS where I am trying to generate a complete file path by concatenating both the name of a parent folder(s) (which change on each project) and a filename from a table (the name of this table changes on each project, as well). I can successfully pass the name of the table to my Dynamic SQL in a variable, but I'm struggling with the file path inside of the SELECT CONCAT() statement, because SSMS keeps trying to read this value as a column name for that table, rather than as a string.

Here's some sample data:

SELECT * INTO AAA_Demo_Table FROM (VALUES ('Image01.png'), ('Image02.png'), ('Report00001.jpg'), ('Image01.jpg'), ('File1.gif')) AS [Filename] ([Filename])

This first query works, though it obviously doesn't take advantage of the variables that I've established:

DECLARE @DemoTable VARCHAR(200)
DECLARE @Folder01 VARCHAR(200)
DECLARE @Folder02 VARCHAR(200)
DECLARE @Query1 NVARCHAR(MAX)

SET @DemoTable = 'AAA_Demo_Table' -- Replace with the name of your project
SET @Folder01 = 'Documents'
SET @Folder02 = 'EMD_Images'

-- THIS EXAMPLE WORKS
SET @Query1 = '
SELECT CONCAT(''Documents'', ''/'', ''Images'', ''/'', FileName) AS FilePaths
FROM AAA_Demo_Table
'

EXEC sp_executesql @Query1

Next, if I try sliding my table name in dynamically, that also works:

DECLARE @DemoTable VARCHAR(200)
DECLARE @Folder01 VARCHAR(200)
DECLARE @Folder02 VARCHAR(200)
DECLARE @Query1 NVARCHAR(MAX)

SET @DemoTable = 'AAA_Demo_Table' -- Replace with the name of your project
SET @Folder01 = 'Documents'
SET @Folder02 = 'Images'

-- THIS EXAMPLE WORKS
SET @Query1 = '
SELECT CONCAT(''Documents'', ''/'', ''Images'', ''/'', ADT.FileName) AS FilePaths
FROM ' + @DemoTable + ' ADT
'

EXEC sp_executesql @Query1

But when I try to dynamically provide the file names into the Dynamic SQL, it fails:

DECLARE @DemoTable VARCHAR(200)
DECLARE @Folder01 VARCHAR(200)
DECLARE @Folder02 VARCHAR(200)
DECLARE @Query1 NVARCHAR(MAX)

SET @DemoTable = 'AAA_Demo_Table' -- Replace with the name of your project
SET @Folder01 = 'Documents'
SET @Folder02 = 'Images'

-- THIS EXAMPLE FAILS
SET @Query1 = '
SELECT CONCAT(' + @Folder01 + ', ''/'', ' + @Folder02 + ', ''/'', ADT.FileName) AS FilePaths
FROM ' + @DemoTable + ' ADT
'

EXEC sp_executesql @Query1

I've tried searching quite a bit for an answer here, but I haven't had any luck. All of the examples that I can find are either not using variables inside the SELECT statement (they're providing a variable to FROM or WHERE, usually), or they are setting the entire SELECT statement into a variable, rather than storing a string to be placed into a subsequent SELECT statement.

In my case, this is a much smaller portion of a larger query where I want to do a lot of handling of these filenames, and the top level folders would be referenced multiple times. Saving the entire SELECT statement into a single variable would be functionally indistinguishable from just having the user scroll through the query and make all of the replacements, which is why I was hoping to use this approach of only having the user provide the table name and top-level folders once and then applying that to the later queries.

I have a situation where I am trying to create a query using Dynamic SQL in SSMS where I am trying to generate a complete file path by concatenating both the name of a parent folder(s) (which change on each project) and a filename from a table (the name of this table changes on each project, as well). I can successfully pass the name of the table to my Dynamic SQL in a variable, but I'm struggling with the file path inside of the SELECT CONCAT() statement, because SSMS keeps trying to read this value as a column name for that table, rather than as a string.

Here's some sample data:

SELECT * INTO AAA_Demo_Table FROM (VALUES ('Image01.png'), ('Image02.png'), ('Report00001.jpg'), ('Image01.jpg'), ('File1.gif')) AS [Filename] ([Filename])

This first query works, though it obviously doesn't take advantage of the variables that I've established:

DECLARE @DemoTable VARCHAR(200)
DECLARE @Folder01 VARCHAR(200)
DECLARE @Folder02 VARCHAR(200)
DECLARE @Query1 NVARCHAR(MAX)

SET @DemoTable = 'AAA_Demo_Table' -- Replace with the name of your project
SET @Folder01 = 'Documents'
SET @Folder02 = 'EMD_Images'

-- THIS EXAMPLE WORKS
SET @Query1 = '
SELECT CONCAT(''Documents'', ''/'', ''Images'', ''/'', FileName) AS FilePaths
FROM AAA_Demo_Table
'

EXEC sp_executesql @Query1

Next, if I try sliding my table name in dynamically, that also works:

DECLARE @DemoTable VARCHAR(200)
DECLARE @Folder01 VARCHAR(200)
DECLARE @Folder02 VARCHAR(200)
DECLARE @Query1 NVARCHAR(MAX)

SET @DemoTable = 'AAA_Demo_Table' -- Replace with the name of your project
SET @Folder01 = 'Documents'
SET @Folder02 = 'Images'

-- THIS EXAMPLE WORKS
SET @Query1 = '
SELECT CONCAT(''Documents'', ''/'', ''Images'', ''/'', ADT.FileName) AS FilePaths
FROM ' + @DemoTable + ' ADT
'

EXEC sp_executesql @Query1

But when I try to dynamically provide the file names into the Dynamic SQL, it fails:

DECLARE @DemoTable VARCHAR(200)
DECLARE @Folder01 VARCHAR(200)
DECLARE @Folder02 VARCHAR(200)
DECLARE @Query1 NVARCHAR(MAX)

SET @DemoTable = 'AAA_Demo_Table' -- Replace with the name of your project
SET @Folder01 = 'Documents'
SET @Folder02 = 'Images'

-- THIS EXAMPLE FAILS
SET @Query1 = '
SELECT CONCAT(' + @Folder01 + ', ''/'', ' + @Folder02 + ', ''/'', ADT.FileName) AS FilePaths
FROM ' + @DemoTable + ' ADT
'

EXEC sp_executesql @Query1

I've tried searching quite a bit for an answer here, but I haven't had any luck. All of the examples that I can find are either not using variables inside the SELECT statement (they're providing a variable to FROM or WHERE, usually), or they are setting the entire SELECT statement into a variable, rather than storing a string to be placed into a subsequent SELECT statement.

In my case, this is a much smaller portion of a larger query where I want to do a lot of handling of these filenames, and the top level folders would be referenced multiple times. Saving the entire SELECT statement into a single variable would be functionally indistinguishable from just having the user scroll through the query and make all of the replacements, which is why I was hoping to use this approach of only having the user provide the table name and top-level folders once and then applying that to the later queries.

• sql-server
• dynamic-sql

Share Improve this question Follow edited Jan 31 at 20:20 Dale K 27.5k1515 gold badges5858 silver badges8383 bronze badges asked Jan 31 at 19:49 WJTownsendWJTownsend 12566 bronze badges 3

• 1 Why inject the string and not parametrised it? Your SQL is dangerously open to SQL Injection attacks; you need to paramterise as much as you can, and sanitise anything else (object names). – Thom A Commented Jan 31 at 19:52
• Doing it this way is something that I found previously on StackOverflow and started using as a way to build "template" scripts, where I only have to provide a few strings that change. The Folder values aren't gathered via form or anything like that, they're just accessed by myself or other technical staff who already have access to the database. This is just a means to easily run a script that doesn't change aside from a few small parameters. If anyone accessing the query wanted to damage the database, they could just do so. – WJTownsend Commented Jan 31 at 20:00
• 1 Technical staff can still make mistakes. Better to limit that potential. – Dale K Commented Jan 31 at 20:19

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

Just pass your parameters as parameters, don't inject them. Also sanitise your object names. What you had was wide open to SQL Injection attacks.

DECLARE @DemoTable sysname; --corrected datatype
DECLARE @Folder01 VARCHAR(200);
DECLARE @Folder02 VARCHAR(200);
DECLARE @Query1 NVARCHAR(MAX);

SET @DemoTable = N'AAA_Demo_Table' -- Replace with the name of your project
SET @Folder01 = 'Documents';
SET @Folder02 = 'Images';

-- THIS EXAMPLE FAILS
SET @Query1 = '
SELECT CONCAT(@Folder01, ''/'', @Folder02, ''/'', ADT.FileName) AS FilePaths
FROM dbo.' + QUOTENAME(DemoTable) + ' ADT;';

EXEC sys.sp_executesql @Query1, N'@Folder01 varchar(200), @Folder02(200) nvarchar(200)', @Folder01, @Folder02;

本文标签：