Hi Andy
> From: Andy Davies <a.davies /at/ manchester DO.T gov.uk>
> Subject: Re: Views in SQL Server
> Mike Yearwood said "If you always use parameterized SQL queries, the
> parameter contents can not be used for SIA."
>
> Surely it depends how you build the parameter variables - if you let the
> user enter them what is to stop them entering e.g. a city name as
> "manchester go truncate table dbo.sysusers go"
No offense meant, but this is another almost myth. Parameter content
is not executed.
Your example would have to be more like:
"manchester' go truncate table dbo.sysusers go"
If I pass a parameter to a query the profiler shows:
exec sp_executesql N'select * from Table where field = @P1 ', N' /at/ P1
varchar(21)', ''manchester' go truncate table dbo DO.T sysusers go''
Which will only attempt to find records in the table with that value
in the column 'field'. No string scrubbing to remove "invalid"
characters is required!
The hackers are hoping we're all stupid enough to concatenate in our
applications and stored procedures! Cause if you did such
concatenating in a custom SP, you'd still be open to SIA.
lccmd = "select * from Table where field = 'manchester' go truncate
table dbo.sysusers go"
sqlexec(m.lnconnection,m.lccmd)
The profiler would show:
exec sp_executesql N'select * from Table where field = 'manchester' go
truncate table dbo.sysusers go'
That entire command would be executed as is and your sysusers would vanish.
>> and "If someone were somehow able to access your database at all and run a
>> query, they can just as easily run your SPs."
>
> I thought that was the point - you make the error checking in your sp rock
> solid so you don't care what tool is used to access it.
I'd guess any tool that can call SPs should be able to create
parameterized ad-hoc SQL. I can only see *needing* an SP for a complex
multi-table multi-step almost procedural function, not for simple or
even complex queries.
> btw some comments on this topic seem to imply that SQL Server supports
> parameterised queries: afaik Foxpro supports parameterised queries,
> including queries to a SQL Server back-end, but SQL Server itself only
> supports parameters to sp's - or have I missed something?
That would imply VFP/Access/Crystal Reports/VB were concatenating the
user values into the query command string, which is not true. That
would be a reason to use SPs only! However, let me put it another way.
The product is called SQL Server, not SP Server. ;) It's better than
you're giving it credit for.
>From the SQL Server Books Online:
sp_executesql
Executes a Transact-SQL statement or batch that can be reused many
times, or that has been built dynamically. The Transact-SQL statement
or batch can contain embedded parameters.
Syntax
sp_executesql [@stmt =] stmt
[
{, [@params =] N' /at/ parameter_name data_type [, DO.T ..n]' }
{, [@param1 =] 'value1' [,...n] }
]
Which brings me to one last point. If an application is separating a
user from the back-end, why should we be going to all this trouble to
"secure" the back-end with SPs?
Mike
©2006 Mike yearwood |
