Solved

Dynamic SQL / Stored Procedure and Injection Attacks

Posted on 2008-06-23
4
1,022 Views
Last Modified: 2010-05-18
I have an extensive search page (ASP/SQL Server) which uses similar code to this - with many optional search parameters. I am trying to use only stored procedures to prevent sql injection attacks (the site was recently hacked via another similar page). Doesn't using an EXEC statement make this script vulnerable to a similar attack, since someone could enter an 'INSERT' or similar statement into one of the text fields and it would run inside the script? Or am I misunderstanding? (I hope so, because it would be much easier to do it this way!)

Thanks for your time!

RELATED QUESTION:
--------------------------
Title:
Dynamic "where" statement (Stored procedure)
 Bookmark:
Question: Se attached code snippet. I need help to make the "whereStatement" correct.

If @guid is NOT blank I want the where statement to be like this:
where active='1'  and guid=@guid

If @guid is blank I want the where statement to be like this:
where active='1'  and codeID=@codeID

Thanks for all help :)

ALTER PROCEDURE [dbo].[spKodeDetaljPay]
(
@codeid smallint,
@guid varchar(64)
)
AS
 
BEGIN
 
Select title, text,
(Select name from tblPersonPay where tblCode.PersonID = tblPersonPay.PersonID) as Name,
(Select email from tblPersonPay where tblCode.PersonID = tblPersonPay.PersonID) as email from tblCode
where active='1' -- and " & whereStatement
 
END
 
Try;-
ALTER PROCEDURE [dbo].[spKodeDetaljPay]
(
@codeid smallint,
@guid varchar(64)
)
AS
 
BEGIN
 
Select title, text,
(Select name from tblPersonPay where tblCode.PersonID = tblPersonPay.PersonID) as Name,
(Select email from tblPersonPay where tblCode.PersonID = tblPersonPay.PersonID) as email from tblCode
where
(active='1' and guid=@guid)
OR
(active='1' and codeid=@codeid and @guid='')
 
END

DECLARE @query as varchar(MAX)
 
Set @query = 'Select title, text, ' +
      '(Select name from tblPersonPay where tblCode.PersonID = tblPersonPay.PersonID) as Name, ' +
      '(Select email from tblPersonPay where tblCode.PersonID = tblPersonPay.PersonID) as email from tblCode ' +
      'where active=''1''  '
 
IF @guid = '' OR @guid IS NULL
    Set @query = @query + 'AND guid=''' + @guid + ''' '
ELSE
      Set @query = @query + 'AND codeID=''' + @codeID  + ''' '
 
EXEC(@query)

0
Comment
Question by:sydneypaige
4 Comments
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 75 total points
ID: 21850114
IT really depends on how you do it...but if someone sent into a malicious TSQL string in as a param and there is no input validation done on it and you just pass that string to EXEC(), then you could be in trouble.  
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 175 total points
ID: 21850127
>Doesn't using an EXEC statement make this script vulnerable to a similar attack, since someone could enter an 'INSERT' or similar statement into one of the text fields and it would run inside the script?

yes, the EXEC() would make the sql injection possible.
so, the code of Compaq_Engineer would be definitively the better choice (apart from the fact that "empty" might mean NULL in which case the code is incomplete)

0
 
LVL 8

Accepted Solution

by:
Chumad earned 250 total points
ID: 21850209
One thing you can do is to make sure that all INT's and numerical values are really INT's and not a string. You can also check strings to make sure they do not contain special injection enabling characters like semi=colon and single quotes.

Generally, when doing SQL Injection, a value is appended with the dangerous string - say for example I know that you are using an ID variable directly in your sql:

select * from table where id = @myID

As an attacker, I could set @myID = to this:  "5; delete * from table"

Your SQL statement would execute like this:  select * from table where id = 5; delete * from table

So ultimately what you need to do is make sure that nothing like that can happen.
0
 

Author Closing Comment

by:sydneypaige
ID: 31469933
Thanks for all of the fast feedback! I imagine I will go the route of extensive validation (perhaps a function) - as I have almost 25 optional search fields, which each might have multiple loops. So doing an EXEC statement is pretty much a necessity. Thanks, everyone!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now