dslreports logo
 
    All Forums Hot Topics Gallery
spc
Search similar:


uniqs
1185

happyrobot
join:2001-05-09
Brooklyn, NY

happyrobot

Member

Solve an office bet: SQL Injection + .NET

Hello DSLR webmaster forum. Long time no see.

I'm currently involved in a debate with a developer.
Very long story and since it's work-related, I'd rather not get in to that many details.

We have many web projects that are still based in ASP Classic. They work fine but will need to be updated to .NET sooner or later.
In Classic ASP, we have a very robust series of functions that check/parse querystrings or user entered data to protect from SQL Injections.

The debate is this: the developer claims that moving to .NET will magically solve any and all SQL Injections problems.

I claim that they still exist and will still be a problem if you don't know what you are doing.

Anyone want to weigh in?

Robert
Premium Member
join:2001-08-25
Miami, FL

Robert

Premium Member

.NET has updates that can minimize SQL injections by addressing the common ways that are usually used. But it is up to the programmer to ensure that they are cleaning their queries and coding to further minimize SQL injections.

So no, moving to .Net won't somehow magically stop all SQL injections.

Steve
I know your IP address

join:2001-03-10
Tustin, CA

Steve to happyrobot

to happyrobot
said by happyrobot:

the developer claims that moving to .NET will magically solve any and all SQL Injections problems.

Absolutely not - it would scare the crap out of me if one of my developers ever said such a thing. If you have the ability to create a string of SQL and then execute it, SQL injection is possible.

Maybe he's thinking about buffer overflows, which are very heavily mitigated by .NET.

Steve — who has a bit of expertise on this issue

DC DSL
There's a reason I'm Command.
Premium Member
join:2000-07-30
Washington, DC
Actiontec GT784WN

DC DSL to happyrobot

Premium Member

to happyrobot
.NET by itself WILL NOT magically prevent SQL injection. Protection comes from a security-conscious redesign of the app from the database to the front end. That starts with using parameterized Stored Procedures instead of direct SQL statements generated at runtime by the application. Use a business logic layer as an intermediary between the UI and the datastore to centralize validation and rules enforcement. Use databinding between the UI and objects your application provides, never the actual datastore. And, carefully evaluate your user privilege requirements, and enforce them.

happyrobot
join:2001-05-09
Brooklyn, NY

happyrobot

Member

Thank you for your feedback.
This has been an exasperating discussion I've had with this guy.
I kind of wanted to just make sure that I wasn't going crazy.

cdru
Go Colts
MVM
join:2003-05-14
Fort Wayne, IN

cdru to DC DSL

MVM

to DC DSL
said by DC DSL:

That starts with using parameterized Stored Procedures instead of direct SQL statements generated at runtime by the application.

It doesn't even have to be a stored procedure. Going that route adds a additional layer of control and security to prevent a rogue developer from doing something. But just a parameterized query vs a dynamically built one at runtime is enough to prevent the SQL injection.

DC DSL
There's a reason I'm Command.
Premium Member
join:2000-07-30
Washington, DC

DC DSL

Premium Member

A parameterized query is fine if it's not executed all that frequently. However, for high-volume queries, a stored procedure is the preferred practice. It eliminates the additional parsing and validation steps.

cowboyro
Premium Member
join:2000-10-11
CT

cowboyro

Premium Member

said by DC DSL:

a stored procedure is the preferred practice. It eliminates the additional parsing and validation steps.

^^^--- THAT.
Always use stored procedures for manipulating data, or whenever something depends on a parameter.
There is one thing to say select * from products (will work fine but it's basement programming style) and another one to say select * from products where product = ' + productCode + "'"
In an ideal world all data is accessed through stored procedures and views.
Furthermore, it is a good security practice to access all data with an account that has the least permissions needed.

cdru
Go Colts
MVM
join:2003-05-14
Fort Wayne, IN

cdru to DC DSL

MVM

to DC DSL
said by DC DSL:

A parameterized query is fine if it's not executed all that frequently. However, for high-volume queries, a stored procedure is the preferred practice. It eliminates the additional parsing and validation steps.

Depending on what flavor and version the SQL server is, the "parsing and validation steps" are negligible between stored procedure and a parametrized inline query. SQL Server 2005 and newer caches it so any performance hit only happens once. Adhoc inline queries like those that are generated using string concatenation would not be cached in a reusable way unless the query was exactly the same.

Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 from Microsoft
Non-scientific real world "test" of inline parameterized queries vs stored procs

I agree that with you and cowboyro that SP can be better from a maintainability and security standpoints, as well as at least a draw with performance. And I feel sorry for anyone that would write a very complex query outside of a stored procedure. But with simple queries the performance hit isn't there like it use to be in SQL Server 2000 and earlier.

Another consideration is that with technologies such as LINQ to SQL, the queries writes itself in many cases. You can still use stored procedures, but depending on how much of the LINQ to SQL kool-aid you drink you may not even use them for simple projects.

Steve
I know your IP address

join:2001-03-10
Tustin, CA

Steve to happyrobot

to happyrobot
Addressing a few points that have been made in the thread:

First, use of stored procedures is not an automatic guarantee of safety from SQL injection errors, as it's possible for a stored procedure to create an SQL query dynamically.

Though it's easy to parameterize a query that includes "... WHERE ID = ?", you can't do the same thing for "SELECT a,b,c FROM ? WHERE ..." -- table names can't be replaced that way, so you have to build it dynamically.

I'm not much of a web programmer and I have had to do this enough times that it seems to be a common enough practice.

The problem is that many people sanitize wrong by excluding known-bad characters. This is dangerous unless you really seriously truly know everything about Unicode and international character sets - just asking for problems.

Better is to sanitize by including only known-good characters, and for a table name it might be A-Z0-9_ - if the table name contains characters outside that range, it's rejected.

The side effect of this is that if you have incomplete knowledge of what should be included, your code will fail loudly but safely. Many databases allow for qualification with owner names, so a dot would be added to the list above, and attempts to use a table name with a dot will fail.

But this is far, far better than the penalty for getting the known-bad list wrong.

Second, I think that suggesting stored procedures for everything is a bit excessive - there are enough apps with a few modest queries that can be well served by making those apps self-contained and not having to create the SP in the database. It's a good practice, but not one to be slavish about.

Finally, there are frameworks that mostly take the SQL out of your hands, and I'm sure that .NET has such frameworks. You'll map out your data, and stuff like LINQ or other things generate the queries for you. These are probably safe.

But even a good framework will occasionally require a funky query created directly, and that's where you have to really be careful, as well as why .NET does not provide any universal and automatic protection against this kind of thing.

Steve