Re: SQL Injection Prevention

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

Date: Tue, 28 Sep 2004 08:11:03 -0700

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: SQL Injection Prevention
    ... You're comparing a well built parameterized sql statement against a badly ... built stored procedure so it's obvious which will win. ...
    (microsoft.public.dotnet.security)
  • Re: Views vs Stored Procedures, whats the difference?
    ... I hope you are not suggesting you embed SQL queries into the application? ... A stored procedure logic will be exactly as fast as the algorithm you ... I understant that SQL Server supports hints. ... implementations (nestedloop, merge, hash, ..) on decent sized tables, then ...
    (comp.databases.ms-sqlserver)
  • Re: Stored Procedures - Patterns and Practices
    ... >published the reasoning behind its opinions. ... I disagree that the debate in SQL Server related discussion forums ... If the natural key is long or spans too many ... I want to call a stored procedure that adds a customer ...
    (microsoft.public.sqlserver.programming)
  • Re: Issue with retrieving large data over web using Stored Procedu
    ... how do I go about analyzing a stored procedure with selecting ... Is there any tool in the SQL Profiler that analyze each Trace? ... "Active Server Pages error 'ASP 0113' ... This email account is my spam trap ...
    (microsoft.public.inetserver.asp.db)
  • Re: Problem using SP as record source with Access2002 & SQL Server 7
    ... I've installed a brand new copy of SQL Server 2000 with the original ... I've created the folowing stored procedure for the resync ... Resync Command: ALL_CUSTOMERS_RESYNC? ... I need to upgrade to a newer version of Microsoft SQL ...
    (microsoft.public.access.adp.sqlserver)