Solved

SQL Injection protection mechanism in DataSet

Posted on 2010-09-05
5
1,196 Views
Last Modified: 2012-05-10
Hi!

I read here (http://weblogs.asp.net/scottgu/archive/2006/09/30/Tip_2F00_Trick_3A00_-Guard-Against-SQL-Injection-Attacks.aspx) that the TableAdapter/DataSet designer built-into VS 2005 uses this mechanism automatically, as do the ASP.NET 2.0 data source controls.

How this mechanism works? I can't understand how parametrizing resolves the problem.

For example, if the querystring is waiting a string and the user inserts:

website.com/page.aspx?id=iaskdo;DROP DATABASE pubs --

It's still being a string, right? If the user inserts something like this, an exception is raised or nothing happens (just keep the DB safe)?

Thanks in advance!
0
Comment
Question by:calypsoworld
  • 2
  • 2
5 Comments
 
LVL 30

Accepted Solution

by:
Alexandre Simões earned 500 total points
ID: 33608158
The difference is between having a working query that accepts parameters and a skeleton of a query to witch you will concatenate the passed strings and then execute the query.

Imagine that you want to get a record based on its Name, simple stuff.
I'm using the name because it's text, you can pass everything you want to search for.
You would want to execute something like:

-- Option1: SAFE WHAY
SELECT Name, Address FROM Entities WHERE Name LIKE @EntityName;

-- Option 2: UNSAFE WHAY
DECLARE @query VARCHAR(1000);
SET @query = 'SELECT Name, Address FROM Entites WHERE Name LIKE ''' + Whatever the user passed as the input + '''
EXEC SP_Executesql @query;

-- Injection Scenario
We'll try to pass the following text to the query:
Alex'; DROP DATABASE Pubs;--

Notice the ' after Alex and the -- comments at the end.
The ' is to force the string to end and the -- is to comment everything else that may come after that could corrupt the query.


Evaluate the results:
At option one you can never inject malicious code because the query is already done and expecting a variable value there, it will raise an error if you try to pass something that won't compile.
At the very top it will return nothing just because there's no user with DROP DATABASE in its name :)
The result query to be executed would be: SELECT Name, Address FROM Entities WHERE Name LIKE 'Alex; DROP DATABASE Pubs;';


At option 2 on the other hand it's handled as an execution of a string that is expected to be a query or a sequence of queries.
Based on this you could pass in:  that wound result in the following query:

SELECT Name, Address FROM Entites WHERE Name = 'Alex'; DROP DATABASE Pubs;--'

This will execute nicely as it will execute the SELECT statement searching tor an Alex and the will DROP the database.
Of course you can also prevent this by not granting DROP DATABASE to the user that executes the queries but you can also replace the DROP  DATABASE with a DELETE FROM Pubs;


A gold rule is to never trust the user input... NEVER EVER! :)
0
 

Author Comment

by:calypsoworld
ID: 33608189
Thank you, Alex Code. Very helpful explanation.

So I don't have to do anything to prevent SQL Injection, because I'm using strongly typed DataSet and the protection is already set. Right?

Thanks again!
0
 
LVL 30

Assisted Solution

by:Alexandre Simões
Alexandre Simões earned 500 total points
ID: 33608223
I don't use DataSets for interaction with the database, it's just too important to me the lay it out to a so "obscure" functionality, I like to be in control of everything that happens when I interact with the database.
For that I use Stored Procedures or some LINQ based ORM's like Entity Framework, Linq To SQL or SubSonic... something like that.

Anyway, you can always have a look at the SQL that the Dataset generates for you for the objects CRUD.
As I can recall its all based on parameters so yeah, you'll have no problems.

Yet again, consider a better approach when it comes to interacting with the database.
0
 

Author Comment

by:calypsoworld
ID: 33608341
Yes, it is all based on parameters. I'm able to create the queries in Designer mode and all the parameters are set as @param1 @param2 etc...

Thank you for the tips using SPROCS, LINQ etc but I'm finishing my project now and I'm just trying to understand what's happening behind the scene in the ''transparent'' mechanisms.

But I'll surely be careful when deciding how to interact with DB in my next project.

Thank you again!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video discusses moving either the default database or any database to a new volume.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

706 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

20 Experts available now in Live!

Get 1:1 Help Now