?
Solved

SQL Injection protection mechanism in DataSet

Posted on 2010-09-05
5
Medium Priority
?
1,295 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
[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
  • 2
  • 2
5 Comments
 
LVL 30

Accepted Solution

by:
Alexandre Simões earned 2000 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 2000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

770 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