Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Dynamic SQL / Stored Procedure and Injection Attacks

Posted on 2008-06-23
4
Medium Priority
?
1,028 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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 and will be exposed to the many uses the SELECT statement has.

688 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