Solved

Dynamic SQL / Stored Procedure and Injection Attacks

Posted on 2008-06-23
4
1,023 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

813 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

12 Experts available now in Live!

Get 1:1 Help Now