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
Solved

SQL Injection protection mechanism in DataSet

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

860 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