Re: SQL Injection Prevention

From: Nigel Rivett (sqlnr_at_hotmail.com)
Date: 09/28/04 

Date: Tue, 28 Sep 2004 10:11:02 -0700

>> if we consider SQL injection vulnurability in isolation from any other
factor
Don't agree with that.

If we include another factor
>> good contractor bad DB owner situation
(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.

"Valery Pryamikov" wrote:

> 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
>
>
> "Nigel Rivett" <sqlnr@hotmail.com> wrote in message
> news:0317B792-78D8-4FCF-957C-338C2C272F81@microsoft.com...
> > 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.
> >
> >
> > "Valery Pryamikov" wrote:
> >
> >> 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
> >>
> >> "Nigel Rivett" <sqlnr@hotmail.com> wrote in message
> >> news:483195D1-E0AC-4765-8EDA-D0B76D923DED@microsoft.com...
> >> > 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.
> >> >
> >> >>> for stored procedure to return the same cursor as select, this stored
> >> >>> procedure has to execute the same select.
> >> >
> >> > Not true.
> >> >
> >> >>> if stored procedure implemented wrong way - ie it constructs sql by
> >> >>> concatenating received parameter with sql string,
> >> >
> >> > 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.
> >> >
> >> >>> 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
> >> >
> >> > 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).
> >> >
> >> >
> >> > "Valery Pryamikov" wrote:
> >> >
> >> >> 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
> >> >>
> >>
> >>
> >>
>
>
>

Relevant Pages