Solved

SQL Injection protection mechanism in DataSet

Posted on 2010-09-05
5
1,255 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
GeoClustering  and AOG 25 43
asp web application 3 43
ASP.NET Calendar Control 5 29
disadvantage of code behind appoarch in asp.net 3 29
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

726 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