Re: SQL Injection Prevention

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

Date: Tue, 28 Sep 2004 14:53:45 +0200

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...
>> 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.
>
> 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/
>
>
> "Valery Pryamikov" <Valery@nospam.harper.no> wrote in message
> news:O4sdT7UpEHA.3396@tk2msftngp13.phx.gbl...
>> "Valery Pryamikov" <Valery@nospam.harper.no> wrote in message news:...
>> >> Sorry but you lost me. :( Can you explain the difference in an easier
>> >> to
>> >> understand manner? I'm a newbie. Thanks.
>> > Lets us compare call of stored procedure and sql select with using
>> > parameters (? or @paramname):
>> > sqlCallSp = "execute sp_somename(?)"
>> > sqlSelect = "select somevalue from sometable where somekey = ?"
>> > suppose that both sp and select return the same cursor.
>> > for stored procedure to return the same cursor as select, this stored
>> > procedure has to execute the same select. But if stored procedure
>> > implemented wrong way - ie it constructs sql by concatenating received
>> > parameter with sql string, then it introduced sql injection
>> > vulnerability
>> > inside stored procedure regardless of how parameter was passed to
>> > stored
>> > procedure. Conversely, executing parameterized sql select guarantees
>> > against sql injection attacks.
>> >
>> > -Valery.
>> > http://www.harper.no/valery
>> >
>> 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
>>
>>
>
>

Relevant Pages

  • Re: XML vs SQL Server
    ... The built in factory assumes a common syntax among the ... So as long as the sql can be shared, ... procedures for Sql Server and stored procedure for Oracle? ... Oracle supports stored procedure overloads, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: XML vs SQL Server
    ... Then, yes, the built in factory will be sufficient. ... So as long as the sql can be shared, ... procedures for Sql Server and stored procedure for Oracle? ... Oracle supports stored procedure overloads, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Report to display data from sql serv.
    ... IIF (case statements in SQL Server) scenarios etc. ... then you need to create a Stored Procedure in SQL Server and use that as ... at run time supplying the parameters in code by using the Exec command. ...
    (microsoft.public.access.reports)
  • 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: ADO stored proc
    ... to know how to access a SQL Server stored procedure using vbscript ... I've looked at vbscript books and sql server books on ... I don't like this technique since: ...
    (microsoft.public.scripting.vbscript)