SQL Injection in stored procedure
-
What are the guidelines to avoid sql injection in stored procedure. I know if using dynamic sql satements in procedure, the parameters can be used for sql injection. But if I use fixed order by then it is safe? want to listen others views.
AJ
-
New to this this forum so dont know if this is appropriate to ask for email addresses but I have a great document about SQL injection attacks in SQL Server and I am willing to share it with anyone.
There are 10 types of people in this world - those that understand binary and those that don't
-
a
There are 10 types of people in this world - those that understand binary and those that don't
-
I guess because of all the spam, giving or asking for eMail isn't the best idea. Although I never received spam because of being here. However, I think using the private messaging feature here is a better way for interested members to ask for hat document. Or if you have one, put it on the web and post the link here.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks Dave
My concern is whether option 1 is vulnerable to sql attack
Option1:
Create PROCEDURE dbo.usp_GetContactValues
(
@JobIDs nvarchar(4000)
)
AS
SET NOCOUNT ON
DECLARE @SQL nvarchar(4000)
SET @SQL = 'SELECT JOB_id, job_desc
FROM JOBS WITH (NOLOCK)
WHERE JOB_id IN (' + @JobIDs + ')'
Execute (@SQL)
GO
Option 2:
Create PROCEDURE dbo.usp_GetContactValues
(
@JobIDs nvarchar(4000)
)
AS
SET NOCOUNT ON
DECLARE @SQL nvarchar(4000)
SET @SQL = 'SELECT JOB_id, job_desc
FROM JOBS WITH (NOLOCK)
WHERE JOB_id IN (' + @JobIDs + ')' +
'ORDER BY JOB_id'
Execute (@SQL)
GO
Now in option 1 can be attacked but option 2 is secured due to order by clause and will give error. My question is: Can SQL injection work on option 2?
-
Dynamic SQL, there is no need.
A user-defined function, trivial to write, returns results desired.
Seek and you shall find the answer:
http://www.sqlteam.com/item.asp?ItemID=11499
SQL injection, a serious possibility. Avoid it you must -- dynamic SQL, use it not. It leads to the dark side. Unlearn that it even exists, for all but the most complicated matters.
-
as long as you're not building SQL statements anywhere in the process (eg. Neither the SPROC, ADO etc) then you will be fine again SQL Injection! As long as Parameters are used, the literal value of the parameter of the parameter is used ... it is NOT built into the statement!
eg.
Create Proc MyProc
@nvcParam nvarchar(50) = " 'aname' or 1=1 "
AS
SELECT * FROM MyUserTable
WHERE nvcUserName = @nvcParam
... this would search for the full literal value in the nvcUserName field ie It would look for a username of " 'aname' or 1=1" ... and more than likely, yield no records!?
Obviously, if you tried to build the statement within the sproc, you get the same issues as if you were building the sql statement OUTSIDE the sproc ... in this case, it being anyone can log into you system!!
Simple advice ... just parametise everything!!
Cheers
Mark
-
Yeah you are right but in case of dynamic query, if there is something after the parameter like order by clause in the next line, sql injection will not affect. Am I right ?
-
I am just trying to see the possiblity of sql injection in the code. otherwise I may have to change all procs to use replace ' with ''.
I don't want to unnecessaly change if ther is no danger of sql injection.
-
Ignore Yoda's solution to avoid dynamic SQL, do you?
confident you are in your skills. A confidence that can lead to the dark side!
consider your code:
SET @SQL = 'SELECT JOB_id, job_desc
FROM JOBS WITH (NOLOCK)
WHERE JOB_id IN (' + @JobIDs + ')' +
'ORDER BY JOB_id'
Execute (@SQL)
Not be stopped by this, will an evil jedi. A clear indicator of this technique is acceptance of a list of items with commas. Avoid this you must! refer to link provided!! Enhance your T-SQL skills, you will.
Yoda might submit a @JobIDs value of:
1) OR 1=1 --
to show you the lesson to be learned. Try it you will. ORDER BY will save you not!
To avoid dynamic SQL -- it is the way of the Jedi. To stray from that path -- leads quickly to the dark side.
-
OK see the actual situation like this
create PROCEDURE dbo.usp_GetContactValues
(
@JobIDs nvarchar(4000)
)
AS
SET NOCOUNT ON
DECLARE @SQL nvarchar(4000)
SET @SQL = 'SELECT JOB_id, job_desc
FROM JOBS WITH (NOLOCK)
WHERE JOB_id IN (' + @JobIDs + ')' + char(10) +
'ORDER BY JOB_id'
print @SQL
Execute (@SQL)
GO
Runs fine: exec usp_GetContactValues @JobIDs = '2,5,10'
Trying to sql injection:
exec usp_GetContactValues @JobIDs = ' 0,1); delete from xxx; --'
if there is oder by can affect by sql injection. let me know if can be attacked by sql injection?
-
Ignored Yoda again, you have.
I can not teach him.
Patience and attention to detail, he has not. Tried what Yoda suggests? No! The path to the darkside is clear.
-
If you know then write answer.
Yoda can be killed with my skills.
-
hmm.
Patient Yoda must be, to present solution a THIRD time, hmm?
http://www.sqlteam.com/item.asp?ItemID=11499
A true jedi uses the proper tools for the job; dynamic sql leads quickly to the dark side. Hopefully listen this time, you will.
Enhance your skills ... unlearn what you have learned. Perhaps it is not too late to save you, from the dark side!
-
Listen to Yoda you must, far better and safer his solution is.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply