dotnet security: Re: SQL Injection Prevention -- dotnet security

Re: SQL Injection Prevention

Nigel Rivett
9/28/2004 8:11:03 AM

dotnet security: You're comparing a well built parameterized sql statement against a badly
built stored procedure so it's obvious which will win.

Stored procedures are easier to review and so catch bad proctices and are
usually the domain of people who have some experience in dealing with
databases.

[quoted text, click to view]

Not true.

[quoted text, click to view]

I can't believe anyone would do that - if you would consider it then I
suggest you stay away from databases altogether :).

The stored procedure in your example would probably be

create proc a
@key int
as
select somevalue from sometable where somekey = @key
go

You need to build a case that this is more vulnerable than the apllication
code - bearing in mind that a person writing a stored proc is likely to have
more database experience than the person writing the app code.

[quoted text, click to view]
that string. I believe it must be similar functionality in SQL server

Your belief is very wrong (and I hope you aren't trying to use that belief).

p.s. I've have never written an explicit cursor in t-sql (except to "help"
others and never will).

[quoted text, click to view]

Re: SQL Injection Prevention

Nigel Rivett
9/28/2004 8:23:10 AM

Just noticed you said
[quoted text, click to view]
Missed "possibility" on first reading. Sure you can do that in a stored
procedure but it would be a last resort and you would be very careful about
the way you implemented it and what had access to it.

You would need to compare that against someone building an sql statement to
execute the parameterised query which I guess would have the same
vulnerability.

Re: SQL Injection Prevention

Nigel Rivett
9/28/2004 8:39:05 AM

It's just that your arguement doesn't seem to make sense.
If you write a bad SP it's possible to introduce injection vulnerabilities.
If you write bad app code it's possible to introduce injection
vulnerabilities.

SP's mean that you don't need t ogive the user permissions on the tables
(which incidentally means that dynamic sql is not possible so negates one of
your arguments).

You could say that parameterised client statement security depends on the
way the client code is written.

if instead of the code you gave they decide to exec the statement
conctenated with the parameter they will get back the same cusor but
introduce an injection vulnerability.

Basically it's all about writing sensible code - you can't guard against
poor developers. Trying to force them to use a standard interface will help
though. A dal which enforces parametrised queries would do that. The same as
would only giving access to stored procedures. I suspect they would have the
same effect in this area - just that SPs are easier to truobleshoot, you can
change the database structure without affecting the app and enhance security.

[quoted text, click to view]

Re: SQL Injection Prevention

Nigel Rivett
9/28/2004 10:11:02 AM

[quoted text, click to view]
factor
Don't agree with that.

If we include another factor
[quoted text, click to view]
(well actually good application developer bad database developer)

Then you probably wouldn't use stored procedures but do everything from the
application - in which case parameterised queries are maybe a good idea but I
suspect you would lose a lot of performance having to run any batch jobs from
an app (not too different from a situation I'm having to correct now).

To continue in the same vein. How do you stop the bad database developer
putting a trigger on a table which uses dynamic sql and executes a string
including some data just added to the table which comes from user input (I've
seen it happen).

Similar to the argument about which is better Oracle or sql server? I always
say it depends on your expertise
Good oracle better than bad sql server and vice-versa.
I used to think bad oracle was better than bad sql server - I still think so
but sql server is getting more forgiving.

[quoted text, click to view]

Re: SQL Injection Prevention

Nigel Rivett
9/28/2004 11:29:09 AM

I was pointing that you're not considering it in isolation but with the
reletive competencies of the developer.

1,2 Yeah that was a bit of a convoluted counter-example - but as I say I've
seen it happen.
But if the user doesn't have permissions on the tables (as they shouldn't)
then the dynamic sql won't work so there's no problem and possibly the most
secure solution.

I think it's much more likely that an application developer would build the
sql string from input that the database developer would do it in dynamic sql.
As I say it's easy to prevent in a stored proc - is it easy to prevent in
the application?

I do it by saying that "you will interact with the database using these
objects" but I've never tried to prevent developers circumventing this. I
suppose they don't have access to the connection string which stops them.

[quoted text, click to view]

Re: SQL Injection Prevention

Steve Kass
9/28/2004 11:39:14 AM

Not everyone is bashing you, (see my previous post). ;)

I just want to add that your point is very well taken, and this
discussion is important. The risks of bad programming are serious, and
they need to be well understood!

SK

[quoted text, click to view]

Re: SQL Injection Prevention

Valery Pryamikov
9/28/2004 1:34:57 PM

[quoted text, click to view]
Just an addition to what I said earlier: I can't say it for sure about SQL
server (I was primarily working with Oracle) but in Oracle you have
possibility to execute dynamic cursor from stored procedure. Ie. you
construct whatever sql string inside stored procedure and open cursor on
that string. I believe it must be similar functionality in SQL server. So,
if stored procedure receives parameter and after that constructs dynamic
cursor by concatenating this parameter into sql string - it will feed
unprocessed input into SQL parser and thus introduces sql injection
vulnerability (even so it could be more difficult to exploit it).

-Valery.
http://www.harper.no/valery

Re: SQL Injection Prevention

Tibor Karaszi
9/28/2004 1:42:05 PM

[quoted text, click to view]

Using cursors in SQL Server is very rare (*) in the first place. Using them with dynamic SQL is even
more uncommon (*). A trained SQL Server developer will solve vast majority of problems using set
based code, without using dynamic SQL.

(*) Unless you have someone who is new to the SQL language, and have a lack of training...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

[quoted text, click to view]

Re: SQL Injection Prevention

Valery Pryamikov
9/28/2004 2:53:45 PM

Tibor,
we aren't talking about good programming practices when we discuss SQL
injection, aren't we :-).
as long as there is possibility to screw something, we have to account for
it. Therefore my statement stays that parameterized SQL actually provides
better protection against SQL injection than parameterized call to stored
procedure.

-Valery.
http://www.harper.no/valery

"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:uMq1sAVpEHA.1588@TK2MSFTNGP09.phx.gbl...
[quoted text, click to view]

Re: SQL Injection Prevention

Valery Pryamikov
9/28/2004 5:20:36 PM

Common guys, what is it with you? I'm not bashing stored procedures, not at
all. I'm just saying that when it concerns to SQL injection, then
parameterized DML statement is more protected than parameterized call to
stored procedure. That's it. I don't think you can prove opposite. But that
doesn't mean anything about good programming practices what so ever. Do you
read subject - SQL injection which only happens due to bad programming
practices.

I'm not in any way going to fight beaten to death holy war about "are stored
procedures better than SQL DMLs or not".

-Valery.
http://www.harper.no/valery

[quoted text, click to view]

Re: SQL Injection Prevention

Valery Pryamikov
9/28/2004 5:28:24 PM

Again, we aren't talking good programming practices here, we are just
talking about SQL injection vulnurability. and as long as possibility exists
we have to account for it.

-Valery.
http://www.harper.no/valery

[quoted text, click to view]

Re: SQL Injection Prevention

Valery Pryamikov
9/28/2004 5:40:10 PM

[quoted text, click to view]
let me say it another way. Think of following highly hypothetic situation:
You are contractor. You are brought to build a web site. You are a great
programmer that follows all good programming practices and you never
introduce SQL injection vulnerabilities into your code. But due to some
reason you were not given access to code of stored procedures in database.
In this case when you write parameterized DML statements (like select) you
are better protected against introducing SQL injection vulnerability than
when you are calling some blackbox stored procedure even so you are using
parameters...
This is highly hypothetic situation that isn't likely to happen, but it
could happen, therefore my statement stays.

-Valery.
http://www.harper.no/valery

[quoted text, click to view]

Re: SQL Injection Prevention

Valery Pryamikov
9/28/2004 5:47:57 PM

Nigel,
in my other answer to you I mentioned hipothetical "good contractor bad DB
owner situation", that hopefully should better explain my points. Main point
is that if we consider SQL injection vulnurability in isolation from any
other factor, then parameterized SQL DML actually provides better protection
against SQL injection than parameterized call to stored procedure. That's
it.

-Valery.
http://www.harper.no/valery

[quoted text, click to view]

Re: SQL Injection Prevention

Valery Pryamikov
9/28/2004 5:51:49 PM

Thanks, Steve :-).

btw. I'm not saying that other guys are bashing me :-), not at all! It was
more like apologizing from me to the stored procedures camp and attempt to
assure that I'm not going to say that stored procedures are bad - not at
all. They are great, they are tremendous and they has they role (as well as
DML is great and has its role too).

-Valery.
http://www.harper.no/valery

[quoted text, click to view]

Re: SQL Injection Prevention

Valery Pryamikov
9/28/2004 7:51:32 PM

Nigel,
It's great to disagree on that :-). If you can't consider SQL injection
vulnerability in isolation from other factors I can't argue to that. It's
just that when you are trying to assess possible security vulnurabilities
it's often required to look at possible problems in isolation from others
for not being drowned in overcomplexities.

But frankly, which one do you think is more likely to happen:
1. inexperienced database programmer putting dynamic sql statement in stored
procedure in attempt to quickly solve one or another problem.
2. database programmer putting dynamic sql statement in trigger procedure.

Note that in second case it would probably be a bit more experienced
database programmer since its more natural to begin with stored procedures
than with triggers, but even this could be ignored (I'm not even sure that 2
is even allowed, and if yes, than not on all databases, but again I might be
wrong here).

I'm saying that 1 is much more likely to happen... and if you agree that 1
is more likely to happen than 2, than we have nothing to argue about, and
that's just prove my points. If not - than probably we simply should agree
to disagree.

Valery Pryamikov. MCP, MCAD, MCSD, Windows Security MVP (former Windows SDK
MVP).
http://www.harper.no/valery

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the dotnet security group.