Re: SQL Injection Prevention

From: Valery Pryamikov (Valery_at_nospam.harper.no)
Date: 09/28/04 

Date: Tue, 28 Sep 2004 17:47:57 +0200

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

  • Re: how to code to avoid SQL insertion attacks
    ... > vulnerable to SQL injection as someone executing SQL select statements. ... > prepared statements is that you are responsible only for providing the ... you are obviously not aware of the purpose of an "sql injection attack" ... NOT because you used a stored procedure. ...
    (comp.lang.java.programmer)
  • Re: SQL Injection Prevention
    ... against SQL injection than parameterized call to stored procedure. ... > vulnerabilities. ... >> doesn't mean anything about good programming practices what so ever. ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Injection Prevention
    ... >> stored procedure, you have the same level of protection against SQL ... > select statement is better protected against SQL injection than stored ... >> You can check my blog post that I refered in my prevoius post ot that ...
    (microsoft.public.dotnet.security)
  • Re: SQL Injection Prevention
    ... >> stored procedure, you have the same level of protection against SQL ... > select statement is better protected against SQL injection than stored ... >> You can check my blog post that I refered in my prevoius post ot that ...
    (microsoft.public.sqlserver.server)
  • Re: java source code audit
    ... It is a common belief that Hibernate will protect 100% from SQL injection. ... You should check to ensure that the developers have implemented data validation routines that contain a default deny policy and restrict character classes to known good values. ... so I'm discarding SQL injection vulnerabilities. ...
    (Pen-Test)
Quantcast