December 10, 2010 at 6:25 am
Thanks Gila,
I understood, and sql-injection your blog was good.
Thanks & Regards,
MC
December 13, 2010 at 2:48 am
Hi Gila,
I would like to know something more related to this. I have some applications which are still using
SQL 2000 !!!! , and my understandings are:
1)VARCHAR(max) / NVARCHAR(max) is not introduced in SQL 2000
2) sp_executesql expects an input of the form NVARCHAR
so in dynamic sql, as the string length was very huge, we were using EXEC( @sql1+@sql2+..)
such that @sql1,@sql2 ect was declared as VARCHAR(8000) -- to get the max lenght.
Here my doubt is in this case as we are not able to use sp_executesql (due to the vary large length of the string), then how can we avoid the chance of SQL Injection in SQL 2000?
Is it like declare @sql1 ,@sql2 etc as NVARCHAR(4000) , then finally @sql = @sql1+@sql2
sp_executesql @sql ... ?
Thanks & Regards,
MC
December 13, 2010 at 12:20 pm
Yo! Original Poster! Listen Up!
GilaMonster has REPEATEDLY told you to avoid the approach you are so avidly accepting.
Go back and re-read his comments and links about alternative ways that avoid SQL Injection attacks.
If you don't know what a SQL Injection attack is or why you should care, Go back and re-read his comments and links about alternative ways that avoid SQL Injection attacks.
In other words, go back and re-read his comments and links about alternative ways that avoid SQL Injection attacks.
This is serious stuff.
Pay attention and learn how to do this the right way.
December 13, 2010 at 1:08 pm
David...
1) GilaMonster = Gail Shaw. Definate she.
2)
Here my doubt is in this case as we are not able to use sp_executesql (due to the vary large length of the string), then how can we avoid the chance of SQL Injection in SQL 2000?
He is trying. Be calm.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 13, 2010 at 1:12 pm
only4mithunc (12/13/2010)
Is it like declare @sql1 ,@sql2 etc as NVARCHAR(4000) , then finally @sql = @sql1+@sql2
sp_executesql @sql ... ?
You can keep doing it in a similar way, Only4. The primary item here that guards against the sql injection is the parameter usage. Make sure you use parameters in your @sql string builds, and then use the additional arguments to sp_executesql to populate them to guard from injection.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 13, 2010 at 1:18 pm
david_wendelken (12/13/2010)
Yo! Original Poster! Listen Up!
The last post by the Original Poster (OP) stated that he's going to read over that blog post in detail. Hence he is trying. MC (poster above) is not the OP, but someone else who got involved in the discussion.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 13, 2010 at 1:23 pm
only4mithunc (12/13/2010)
Here my doubt is in this case as we are not able to use sp_executesql (due to the vary large length of the string), then how can we avoid the chance of SQL Injection in SQL 2000?
I have not used SQL 2000 in over 4 years.
If I recall (and I may well be mis-remembering, so test!), you're forced essentially to use EXEC as sp_executesql wants a parameter max nvarchar(4000), hence no parameters, hence you need to be very, very, very, very careful about where those parameters come from. Check for odd commands or characters. Use QUOTE_NAME, preferably whitelist the input if you can
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 13, 2010 at 1:24 pm
Craig - thanks so much for your post today. Really appreciated and a little overwhelmed here by your going above-and-beyond with all your help - especially to an anonymous stranger. Only other thing I can say is I hope you and your family have a wonderful holiday season and a happy New Year.
December 13, 2010 at 1:29 pm
Gail - and a big thanks to you too of course. Wishing you a happy and safe holiday season with your family and happy New Year!
December 13, 2010 at 7:58 pm
Friends,
My doubt was related to this post thats why I have asked it in this thread itself rather than creating a new, I have seen when some one is asking a question, if the same/related one was discussed already, then people will advise to refer that, thats why I asked my doubt here rather than starting a new thread.
Gila,
Thanks for your response, as you mentioned I have to take care of that.
Craig,
Thanks for your reply and making others clear about the matter.
dso808 (The Original Poster),
I hope your doubts and issues are solved.
David,
I would like to see you keep smiling
Thanks & Regards,
MC
December 13, 2010 at 8:30 pm
GilaMonster (12/13/2010)
david_wendelken (12/13/2010)
Yo! Original Poster! Listen Up!The last post by the Original Poster (OP) stated that he's going to read over that blog post in detail. Hence he is trying. MC (poster above) is not the OP, but someone else who got involved in the discussion.
Heh, good catch, Gail. I quoted the wrong poster... but they're both trying, so I guess it's still valid. 🙂
No worries, DSO. I'm just glad I didn't confuse you worse while Gail was trying to make sure you did it the right way. :w00t:
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 13, 2010 at 8:37 pm
GilaMonster (12/13/2010)
only4mithunc (12/13/2010)
Here my doubt is in this case as we are not able to use sp_executesql (due to the vary large length of the string), then how can we avoid the chance of SQL Injection in SQL 2000?I have not used SQL 2000 in over 4 years.
If I recall (and I may well be mis-remembering, so test!), you're forced essentially to use EXEC as sp_executesql wants a parameter max nvarchar(4000), hence no parameters, hence you need to be very, very, very, very careful about where those parameters come from. Check for odd commands or characters. Use QUOTE_NAME, preferably whitelist the input if you can
Gail is correct. She reminded me of something I hadn't needed to do for a bit. My apologies for almost leading you up the creek. I found a quick and easy definition of it here:
http://www.sommarskog.se/dynamic_sql.html#sp_executesqlong
To wit:
There is a limitation with sp_executesql on SQL 2000 and SQL 7, since you cannot use longer SQL strings than 4000 characters. (On SQL 2005 and later, you should use nvarchar(MAX) to avoid this problem.) If you want to use sp_executesql when your query string exceeds this limit to make use of parameterised query plans, there is actually a workaround. To wit, you can wrap sp_executesql in EXEC():
DECLARE @sql1 nvarchar(4000),
@sql2 nvarchar(4000),
@state char(2)
SELECT @state = 'CA'
SELECT @sql1 = N'SELECT COUNT(*)'
SELECT @sql2 = N'FROM dbo.authors WHERE state = @state'
EXEC('EXEC sp_executesql N''' + @sql1 + @sql2 + ''',
N''@state char(2)'',
@state = ''' + @state + '''')
This works, because the @stmt parameter to sp_executesql is ntext, so by itself, it does not have any limitation in size.
You can even use output parameters by using INSERT-EXEC, as in this example:
CREATE TABLE #result (cnt int NOT NULL)
DECLARE @sql1 nvarchar(4000),
@sql2 nvarchar(4000),
@state char(2),
@mycnt int
SELECT @state = 'CA'
SELECT @sql1 = N'SELECT @cnt = COUNT(*)'
SELECT @sql2 = N'FROM dbo.authors WHERE state = @state'
INSERT #result (cnt)
EXEC('DECLARE @cnt int
EXEC sp_executesql N''' + @sql1 + @sql2 + ''',
N''@state char(2),
@cnt int OUTPUT'',
@state = ''' + @state + ''',
@cnt = @cnt OUTPUT
SELECT @cnt')
SELECT @mycnt = cnt FROM #result
You have my understanding if you think this is too messy to be worth it.
I had forgotten that annoying tidbit. The workaround I evolved into was using an variable declared to nTEXT and use WRITETEXT or UPDATETEXT to build the string and pass it, but it's been a while since I did that. I wasn't aware of the above method. :hehe: I wouldn't go that route unless you've argued with LOB's on a regular basis, though.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 14, 2010 at 5:08 am
Hi Craig,
Thanks a lot for the detailed reply,either of the above method should solve the issue.
Thanks & Regards,
MC
December 14, 2010 at 2:25 pm
Hi Gail - got one more question for you. Here's what I now have:
ALTER PROCEDURE [dbo].[PTicketSearch]
(
@StartDate datetime = NULL,
@EndDate datetime = NULL,
@Dept nvarchar(50) = NULL,
@Submitter nvarchar(100) = NULL,
@Assigned nvarchar(100) = NULL,
@status nvarchar(10) = NULL
)
AS
DECLARE @SQL NVARCHAR(4000), @Where NVARCHAR(4000)
SET @SQL = 'SELECT * FROM ProblemTicket '
IF @StartDate is not NULL
SET @Where = @Where + 'AND ProbTicketDate >= @_StartDate '
IF @EndDate is not NULL
SET @Where = @Where + 'AND ProbTicketDate <= @_EndDate '
IF @Dept is not NULL
SET @Where = @Where + 'AND ProbTicketSubDept Like @_Dept '
IF @Submitter is not NULL
SET @Where = @Where + 'AND ProbTicketSubName Like @_Submitter '
IF @Assigned is not NULL
SET @Where = @Where + 'AND ProbTicketAssigned Like @_Assigned '
IF @status is not NULL
SET @Where = @Where + 'AND ProbTicketStatus = @_Status '
IF LEN(@Where) > 0
SET @SQL = @SQL + 'WHERE ' + RIGHT(@Where, LEN(@Where)-3)
-- print @SQL
EXEC sp_executesql @SQL,N'@_StartDate = datetime, @_EndDate = datetime, @_Dept = nvarchar(50), @_Submitter = nvarchar(100), @_Assigned = nvarchar(100), @_Status = nvarchar(10)',
@_StartDate = @StartDate, @_EndDate = @EndDate, @_Dept = @Dept, @_Submitter = @Submitter, @_Assigned = @Assigned, @_Status = @status
As a test, I then do this:
exec PTicketSearch NULL,NULL,NULL,NULL,NULL,NULL
But I now get this error message:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.
I have been banging my head the last half hour to try to find the problem but am not getting anywhere. As you can see, I also inserted a stub, 'print @SQL', which returns ''SELECT * FROM ProblemTicket' before the error message. That's correct, so I don't understand where the problem with '=' is coming from. Please advise. Thanks again in advance!
December 14, 2010 at 2:47 pm
The problem is not in adhoc sql string. It's in the EXEC. You don't use = to set data types. Red is wrong.
EXEC sp_executesql @SQL,N'@_StartDate = datetime, @_EndDate = datetime, @_Dept = nvarchar(50), @_Submitter = nvarchar(100), @_Assigned = nvarchar(100), @_Status = nvarchar(10)',
@_StartDate = @StartDate, @_EndDate = @EndDate, @_Dept = @Dept, @_Submitter = @Submitter, @_Assigned = @Assigned, @_Status = @status
Take a look at the example on my blog.
btw, are the columns Status, etc nvarchar?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 31 through 45 (of 57 total)
You must be logged in to reply to this topic. Login to reply