October 2, 2011 at 10:27 am
Hi,
Below is my query code but I keep getting Incorrect syntax near '@table_name'. error
Checked Books Online but do not see anything wrong with my synax as it relates to sp_executesql.
Can some help?
DECLARE @table_name nVARCHAR(128)
DECLARE @params nVARCHAR(300)
DECLARE @sql nvarchar(1000)
SET @table_name = 'AB_Test'
set @params = N'@table_name varchar(128)'
SET @sql = 'Select * from data.@table_name '
SELECT @sql AS SQLStr
SELECT @params AS Params
SELECT @table_name AS Tbl
Exec sp_executesql @sql, @params, @table_name
October 2, 2011 at 1:10 pm
You aren't passing any parameters to the query based on what you have posted. To get what you want based on your post, try this:
DECLARE @table_name nVARCHAR(128)
DECLARE @sql nvarchar(1000)
SET @table_name = 'AB_Test'
SET @sql = 'Select * from data.' + @table_name
SELECT @sql AS SQLStr
SELECT @table_name AS Tbl
Exec sp_executesql @sql
October 2, 2011 at 2:01 pm
Lynn Pettis (10/2/2011)
You aren't passing any parameters to the query based on what you have posted. To get what you want based on your post, try this:
DECLARE @table_name nVARCHAR(128)
DECLARE @sql nvarchar(1000)
SET @table_name = 'AB_Test'
SET @sql = 'Select * from data.' + @table_name
SELECT @sql AS SQLStr
SELECT @table_name AS Tbl
Exec sp_executesql @sql
How about SQL Injection?
October 2, 2011 at 2:06 pm
Lexa (10/2/2011)
Lynn Pettis (10/2/2011)
You aren't passing any parameters to the query based on what you have posted. To get what you want based on your post, try this:
DECLARE @table_name nVARCHAR(128)
DECLARE @sql nvarchar(1000)
SET @table_name = 'AB_Test'
SET @sql = 'Select * from data.' + @table_name
SELECT @sql AS SQLStr
SELECT @table_name AS Tbl
Exec sp_executesql @sql
Yes? Something wrong here?
October 2, 2011 at 2:12 pm
Lexa (10/2/2011)
Lynn Pettis (10/2/2011)
You aren't passing any parameters to the query based on what you have posted. To get what you want based on your post, try this:
DECLARE @table_name nVARCHAR(128)
DECLARE @sql nvarchar(1000)
SET @table_name = 'AB_Test'
SET @sql = 'Select * from data.' + @table_name
SELECT @sql AS SQLStr
SELECT @table_name AS Tbl
Exec sp_executesql @sql
How about SQL Injection?
You are correct, the code I posted won't protect you against SQL Injection. There are ways to protect against that, but the issue here is you can't pass the name of the table as a parameter to sp_executesql. You have to build the sql in a manner like that I have shown you. I believe a safer way would be:
DECLARE @table_name nVARCHAR(128)
DECLARE @params nVARCHAR(300)
DECLARE @sql nvarchar(1000)
SET @table_name = 'AB_Test'
--SET @params = N'@table_name varchar(128)'
SET @sql = 'Select * from data.' + quotename(@table_name)
SELECT @sql AS SQLStr
SELECT @table_name AS Tbl
October 2, 2011 at 7:06 pm
Lynn Pettis (10/2/2011)
Lexa (10/2/2011)
Lynn Pettis (10/2/2011)
You aren't passing any parameters to the query based on what you have posted. To get what you want based on your post, try this:
DECLARE @table_name nVARCHAR(128)
DECLARE @sql nvarchar(1000)
SET @table_name = 'AB_Test'
SET @sql = 'Select * from data.' + @table_name
SELECT @sql AS SQLStr
SELECT @table_name AS Tbl
Exec sp_executesql @sql
How about SQL Injection?
You are correct, the code I posted won't protect you against SQL Injection. There are ways to protect against that, but the issue here is you can't pass the name of the table as a parameter to sp_executesql. You have to build the sql in a manner like that I have shown you. I believe a safer way would be:
DECLARE @table_name nVARCHAR(128)
DECLARE @params nVARCHAR(300)
DECLARE @sql nvarchar(1000)
SET @table_name = 'AB_Test'
--SET @params = N'@table_name varchar(128)'
SET @sql = 'Select * from data.' + quotename(@table_name)
SELECT @sql AS SQLStr
SELECT @table_name AS Tbl
Thanks Lynn, I did not realize that a table name cannot be passed as a parameter to sp_executesql. So to write the code in a secure manner, is it still recommended to use REPLACE( ) function to replace “;” and to check the parameters for malicious code while using quotename?
October 2, 2011 at 8:06 pm
This is the typical reference I've seen passed around on how to use dynamic sql well. http://www.sommarskog.se/dynamic_sql.html
October 2, 2011 at 8:16 pm
Well the real issue is that you should know in advance to what table you need to get data from. This is the funcdamental problem you have to fix at this point.
The one time I really saw a need for a construct like this was when a 3rd party ERP had this naming convention => dbo.[company name$Item]
Since the website was connecting to a different company based on the login (had only 3 when I left but the project asked for 28 different companies), it was just impossible to go the stored proc way. I had to go with parameterized statements.
That being said. The cie name was NEVER driven by user input which protected the queries from injection. At the login, the user manually chose its company but then the software defined the global parameters for that cie name so that no injection could ever interfere.
The rest of the parameters where sent like any normal parameterized query. I can't say for sure it was bullet proof, but it seemed 99.999% of the way there since the baisc hacks didn't work.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply