Doubt Reg. Exec in Dynamic SQL

Question

Post reply

Doubt Reg. Exec in Dynamic SQL

yogesh.balasubramanian

SSC-Addicted

Points: 474
More actions
December 26, 2008 at 3:29 am

#92910

Directly using Exec (@SomeQuery) leads to SQL injection. I need to avoid SQL Injections one way of doing it is as follows.
[font="Arial"]DECLARE @sql nvarchar(4000)
SELECT @sql = ‘ SELECT [name], [address] ‘ + ‘ FROM [USERS] Where ‘
SELECT @sql = @sql + ‘ [username] LIKE @username’
EXEC sp_executesql @sql, N‘@username varchar(200)’, @username
[font="Arial"][/font][/font]
I want to know whether there are any other methods? Since by this method we are restricted to nVarchar max which is 4000. Some of my dynamic queries goes beyond the length? Whether its possible to use bind parameters in Exec. Any one have any idea? Guide me if i am wrong somewhere?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 1

www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

weitzera Hall of Fame Points: 3442 More actions · Answer 2

I doubt I'm saying anything not contained in that document, but my first impulse is to tell you not to use dynamic sql.

Depending on what you're trying to do this could be easy or hard, but for the example you gave it would be much better to build your own stored procedure to select based on username.

Why do you need/want to use dynamic sql?

Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

yogesh.balasubramanian (12/26/2008)
I want to know whether there are any other methods? Since by this method we are restricted to nVarchar max which is 4000. Some of my dynamic queries goes beyond the length? Whether its possible to use bind parameters in Exec. Any one have any idea? Guide me if i am wrong somewhere?

nvarchar(MAX) can take slightly more characters than just 4.000. If you are at least on SQL Servr 2005 you can use that data type. Just read the article Madhi mentioned.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

yogesh.balasubramanian SSC-Addicted Points: 474 More actions · Answer 4

weitzera (12/30/2008)
I doubt I'm saying anything not contained in that document, but my first impulse is to tell you not to use dynamic sql.
Depending on what you're trying to do this could be easy or hard, but for the example you gave it would be much better to build your own stored procedure to select based on username.
Why do you need/want to use dynamic sql?

It was just a sample code. We have several search options with more than 20 columns for building several reports. In such cases we would be using the dynamic query.

weitzera Hall of Fame Points: 3442 More actions · Answer 5

Again, I don't know what requirements led to your decision to use dynamic sql, but a stored procedure will give you much better performance and security even if you have to use a large case statement.

One technique that I use to do similar tasks is to create a stored procedure with a parameter for each of the possible columns. I then give each parameter a default value outside of the range of possible values (usually NULL) In the where clause of the select inside the sp, I have the following for each column:

...

((@columnAValue IS NULL) OR columnA = @columnAValue)

I also find that this leads to more readable and maintainable code.

There are not that many cases where dynamic sql is truly required.

Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'

dhaval.samaranayeke SSC Enthusiast Points: 122 More actions · Answer 6

Hi Yogesh,

Try using more than 1 variable and combine the variables and then execute the command. For instance

DECLARE @sql1 nvarchar(4000)

DECLARE @sql2 nvarchar(4000)

SELECT @sql1 = ‘ SELECT [name], [address] ‘ + ‘ FROM [USERS] Where ‘

SELECT @sql2 = ‘ [username] LIKE @username’

EXEC sp_executesql @sql1 + @sql2, N‘@username varchar(200)’, @username

I hope this will be of some help to you.

Samar

weitzera Hall of Fame Points: 3442 More actions · Answer 7

two concatenating two nvarchar(max) variables will result in one nvarchar(max) string.

If you use varchar instead of nvarchar, you will get an extra 4000 characters, but the overall issue still remains.

Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'

dhaval.samaranayeke SSC Enthusiast Points: 122 More actions · Answer 8

Hi Wietz,

Sorry for not replying early enough. I tried executing the code which i had posted. It was not working simply because sp_executesql takes only one nvarchar parameter. I realised this later and sorry for that. 😀 It gives me an incorrect syntax error near "+". I tried executing by removing sp_executesql and then it worked. So i think something like this EXEC (@sql1+@sql2 , ...) should work. Any comment on this would be appreciated. 🙂

Regards,

Samar