Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Dynamic SQL / Stored Procedure and Injection Attacks

Posted on 2008-06-23
4
Medium Priority
?
1,029 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 300 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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 700 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 1000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

972 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