Re: SQL Injection Prevention

From: Steve Kass (skass_at_drew.edu)
Date: 09/28/04 

Date: Tue, 28 Sep 2004 11:32:39 -0400

Valery,

  I think you are basically correct.

With stored procedures, you can do this:

create proc p (
  @s varchar(1000)
) as
  declare @sql nvarchar(1000
  set @sql = 'select * from T where c = ''' + @s + ''''
  exec (@sql)

and the risk is the call
exec p 'abc'' delete from T --'

But I don't think there's a way to write a parameterized query in such a
way that the passed parameter is truly concatenated into the query
string. If you have

sqlQuery = "select * from T where c = ?"

and the same parameter is passed, the query is safe:

select * from T where c = 'abc'' delete from T --'

I don't know API programming either, but I suspect you cannot do this

sqlQuery = "select * from T where c = '" & ? & "'"

There is a relatively simple way to make any stored procedure as safe as
a parameterized query, and that is to use QUOTENAME (or something with
the same functionality that handles longer strings). Then there is no
longer a risk of injection. Here's an example:

declare @sql nvarchar(100)
declare @p nvarchar(100)
set @p = '3'' drop table T--'
set @sql = '
  select *
  from T
  where c = ''' + @p + ''''
print @sql
set @sql = '
  select *
  from T
  where c = ' + quotename(@p,'''')
print @sql

However, the parameterized query framework effectively takes this
responsibility away from the programmer.

Since I rarely use these APIs, I hope you or someone else will confirm
or deny my suspicions about how parameterized queries work.

Steve Kass
Drew University

Valery Pryamikov wrote:

>>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 to 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
>
>
>"Shabam" <blislecp@hotmail.com> wrote in message
>news:MsednXTEbo9QscTcRVn-og@adelphia.com...
>
>
>>>Actually, I'd rather revoke my prev. statement in favor of following:
>>>if you use parameter placeholder in SQL string (? or @paramname), then
>>>sql
>>>select statement is better protected against SQL injection than stored
>>>procedure (because we have to account for cases when stored procedure
>>>
>>>
>>could
>>
>>
>>>use dynamic sql generation by concatenating values of the parameters,
>>>
>>>
>>while
>>
>>
>>>as standard sql commands like select, update and others are guaranteed
>>>against that).
>>>
>>>
>>Sorry but you lost me. :( Can you explain the difference in an easier to
>>understand manner? I'm a newbie. Thanks.
>>
>>
>>
>>
>
>
>
>

Quantcast