dynamic sql w/sp_executesql - servername parameter issue

I know that we can't pass serername/tablename as a parameter to sp_executesq
l
as follow...
But I have to work around sql injection vulnerability too...
I want to use some type of paratertize way but...can't find a solution yet.
Please help!!
create proc SaferDynamicSQL(@.serverName nvarchar(25))
as
declare @.sql nvarchar(255)
set @.sql = 'select @.p_serverName+'..'+'action from action'
exec sp_executesql @.sql,
N'@.p_serverName nvarchar(25)',
@.p_serverName = @.serverName
goIf you want to avoid injection then why do this dynamically in TSQL? You
could create views that reference the linked server(s) and then reference
the view by name. You could parameterize the connection string in your
client application. You could use UDL files to abstract the server name.
David Portas
SQL Server MVP
--

Dynamic SQL Over 4000 characters

I have created a parameterized statement in a T-SQL procedure that uses
sp_executesql such as the following:
--****************ORIGINAL STATEMENT*************************
DECLARE @.sql nvarchar(4000)
SET @.sql = ' SELECT
...
FROM
...
WHERE
field1 = @.User
AND
field2 = @.FByCar
AND
field3 LIKE @.FByFac'
INSERT #T1
EXEC sp_executesql @.sql,
N'@.User bit,
@.FByCar bit,
@.FByFac varchar(50)',
@.User,
@.FByCar,
@.FByFac
My problem is that often my sql string (my real one, not this modified
replica I have included here) is going to be over 4000 characters. I also am
concerned about SQL Injection, which is the reason for desiring a
parameterized statement such as the one above.
In trying to rewrite it so that the sql string can be over 4000 characters
and wrapped into EXEC(), I have written the following:
--****************REVISED STATEMENT*************************
DECLARE @.sqlFrom nvarchar(4000)
DECLARE @.sqlWhere nvarchar(4000)
SET @.sqlFrom = ' SELECT
...
FROM
... '
SET @.sqlWhere = ' WHERE
field1 = @.User
AND
field2 = @.FByCar
AND
field3 LIKE @.FByFac'
INSERT #T1
EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
N''@.User bit,
@.User = ''' + @.User + ''',
@.FByCar bit,
@.FByCar = ''' + @.FByCar + ''',
@.FByFac varchar(50)',
@.FByFac = ''' + @.FByFac + '''')
My questions:
1. Am I still as protected from SQL Injection writing the Revised statement
as in the Original?
2. In the Revised statement, specifically the INSERT, can the variables be
assigned in a cleaner fashion, such as in the Original statement?
--
Message posted via http://www.sqlmonster.comHi
I think you should be looking at what you are trying to achieve and possibly
split it up, rather than trying to get a statement of over 4000 characters to
execute. There may be scope to use views and or functions to reduce the size
a little. As you concatenating your input variables you may still be subject
to SQL Injection, use of the QUOTENAME function may help.
John
"cbrichards" wrote:
> I have created a parameterized statement in a T-SQL procedure that uses
> sp_executesql such as the following:
> --****************ORIGINAL STATEMENT*************************
> DECLARE @.sql nvarchar(4000)
> SET @.sql => ' SELECT
> ...
> FROM
> ...
> WHERE
> field1 = @.User
> AND
> field2 = @.FByCar
> AND
> field3 LIKE @.FByFac'
> INSERT #T1
> EXEC sp_executesql @.sql,
> N'@.User bit,
> @.FByCar bit,
> @.FByFac varchar(50)',
> @.User,
> @.FByCar,
> @.FByFac
> My problem is that often my sql string (my real one, not this modified
> replica I have included here) is going to be over 4000 characters. I also am
> concerned about SQL Injection, which is the reason for desiring a
> parameterized statement such as the one above.
> In trying to rewrite it so that the sql string can be over 4000 characters
> and wrapped into EXEC(), I have written the following:
> --****************REVISED STATEMENT*************************
> DECLARE @.sqlFrom nvarchar(4000)
> DECLARE @.sqlWhere nvarchar(4000)
> SET @.sqlFrom => ' SELECT
> ...
> FROM
> ... '
> SET @.sqlWhere => ' WHERE
> field1 = @.User
> AND
> field2 = @.FByCar
> AND
> field3 LIKE @.FByFac'
> INSERT #T1
> EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
> N''@.User bit,
> @.User = ''' + @.User + ''',
> @.FByCar bit,
> @.FByCar = ''' + @.FByCar + ''',
> @.FByFac varchar(50)',
> @.FByFac = ''' + @.FByFac + '''')
> My questions:
> 1. Am I still as protected from SQL Injection writing the Revised statement
> as in the Original?
> 2. In the Revised statement, specifically the INSERT, can the variables be
> assigned in a cleaner fashion, such as in the Original statement?
> --
> Message posted via http://www.sqlmonster.com
>|||Thanks John. Given the Revised statement, would I use the QuoteName function
within the Set Statement:
SET @.sqlWhere = ' WHERE
field1 = quotename(@.User, '''')
AND
field2 = quotename(@.FByCar, '''')
AND
field3 LIKE quotename(@.FByFac, '''')'
Or would I use QuoteName within the Insert statement:
INSERT #T1
EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
N''@.User bit,
@.User = ''' + quotename(@.User, '''') + ''',
@.FByCar bit,
@.FByCar = ''' + quotename(@.FByCar, '''') + ''',
@.FByFac varchar(50)',
@.FByFac = ''' + quotename(@.FByFac, '''') + '''')
Or would I use it in both?
John Bell wrote:
>Hi
>I think you should be looking at what you are trying to achieve and possibly
>split it up, rather than trying to get a statement of over 4000 characters to
>execute. There may be scope to use views and or functions to reduce the size
>a little. As you concatenating your input variables you may still be subject
>to SQL Injection, use of the QUOTENAME function may help.
>John
>> I have created a parameterized statement in a T-SQL procedure that uses
>> sp_executesql such as the following:
>[quoted text clipped - 63 lines]
>> 2. In the Revised statement, specifically the INSERT, can the variables be
>> assigned in a cleaner fashion, such as in the Original statement?
--
Message posted via http://www.sqlmonster.com|||Hi
It would be the second option.
John
"cbrichards via SQLMonster.com" wrote:
> Thanks John. Given the Revised statement, would I use the QuoteName function
> within the Set Statement:
> SET @.sqlWhere => ' WHERE
> field1 = quotename(@.User, '''')
> AND
> field2 = quotename(@.FByCar, '''')
> AND
> field3 LIKE quotename(@.FByFac, '''')'
>
> Or would I use QuoteName within the Insert statement:
> INSERT #T1
> EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
> N''@.User bit,
> @.User = ''' + quotename(@.User, '''') + ''',
> @.FByCar bit,
> @.FByCar = ''' + quotename(@.FByCar, '''') + ''',
> @.FByFac varchar(50)',
> @.FByFac = ''' + quotename(@.FByFac, '''') + '''')
>
> Or would I use it in both?
>
> John Bell wrote:
> >Hi
> >
> >I think you should be looking at what you are trying to achieve and possibly
> >split it up, rather than trying to get a statement of over 4000 characters to
> >execute. There may be scope to use views and or functions to reduce the size
> >a little. As you concatenating your input variables you may still be subject
> >to SQL Injection, use of the QUOTENAME function may help.
> >
> >John
> >
> >> I have created a parameterized statement in a T-SQL procedure that uses
> >> sp_executesql such as the following:
> >[quoted text clipped - 63 lines]
> >> 2. In the Revised statement, specifically the INSERT, can the variables be
> >> assigned in a cleaner fashion, such as in the Original statement?
> --
> Message posted via http://www.sqlmonster.com
>

Dynamic SQL in Stored Procedures

A small percentage of my client's stored procedures use dynamic SQL
statements that are executed using sp_executesql. I am planning on
removing access to everything except stored procedure execution.
However, I have heard, that to be able to execute dynamic sql, a user
must have access to more than just stored procedure execution (i.e.,
SELECT, UPDATE, etc.). Can anyone clarify this for me, or lead me to a
site that explains it in better detail? Thank you in advance, Jeremy.On SQL Server 2000, permissions would be needed on the
underlying tables. There are other options with execute as
and certificates in SQL Server 2005.
You can find a lot of details on dynamic sql in the
following article on Erland's site:
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
-Sue
On 5 Jun 2006 06:51:31 -0700, "jbiros" <jbiros@.sppinc.net>
wrote:

>A small percentage of my client's stored procedures use dynamic SQL
>statements that are executed using sp_executesql. I am planning on
>removing access to everything except stored procedure execution.
>However, I have heard, that to be able to execute dynamic sql, a user
>must have access to more than just stored procedure execution (i.e.,
>SELECT, UPDATE, etc.). Can anyone clarify this for me, or lead me to a
>site that explains it in better detail? Thank you in advance, Jeremy.|||Sue,
Thank you for the site reference. That is exactly the type of
information I was looking for.
Jeremy

Dynamic SQL help needed

I have some dynamic SQL code that is executed using the following:

SET @.SQLSTRING = @.SELECT+@.FROM+@.WHEREPARM+@.ORDER

EXEC sp_executesql @.SQLSTRING

and this works fine if my @.WHEREPARM is

SET @.WHEREPARM = ' Where srm.EVENT_HISTORY.EVENT_DATE <> ampfm.rpt_Abstract.AbsCompleteDate'

However, it fails if my @.WHEREPARM is

SET @.WHEREPARM = ' Where srm.EVENT_HISTORY.EVENT_DATE <> ampfm.rpt_Abstract.AbsCompleteDate

and srm.EVENT_HISTORY.EVENT_TYPE_KEY = '460''

I am getting the following error: Incorrect syntax near '460'

I believe I need to change the number of apostrophes around the 460 value but can't quite figure this out. Can someone provide me a correct example?

Thanks!

Try 3 quotes at the end: '460'''

|||

You are correct channge:

Code Snippet

and srm.EVENT_HISTORY.EVENT_TYPE_KEY = '460''

to

Code Snippet

and srm.EVENT_HISTORY.EVENT_TYPE_KEY = ''460'''

|||

Thank you both for the quick response. The ''460''' did the trick.