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
>