Solved

Dynamic SQL / Stored Procedure and Injection Attacks

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

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
2012 SQL to JSON Select 5 29
SQL Select Query help 1 34
Linked Server - SP with Param to VIew 7 19
sql server cross db update 2 20
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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 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…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

713 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