>Hi, Mike.
>
>>Why do people use stored procedures? Based on the huge fights I've seen, I wouldn't say most people, either. With .Net you can send parameters. Personally, I want to give the users more control over the queries. Injection attacks can't happen with adhoc parameterized sql, can they?
>>
>>Bear in mind, I'm a .net newbie, but I believe I understand the concept. If I use a stored procedure to assemble an sql command I open myself to injection too.
>

>Definitively, not. SQL injection attacks need:
> 1. the UI being careless about user input
> 2. dynamic SQL been built by concatenating strings
>Stored procedures are not just a way of avoiding SQL attacks, and they don't cover you about anything, but they are generally used for a number of reasons like:
>
>Faster execution due to precompilation. This is increasingly less important as engines get more efficient with dynamic SQL, but there is still there.
>
>Schema isolation: when you access your DB trough SPs, you don't need to know its physical schema. You are using an interface instead, so this is conceptually Information Hiding.
>
>Additional security: the typical DBA doesn't like you doing what you want with her DB (even if you have readonly access to selected tables). SPs ensure you have restricted access to what they wanted you to do.
>
>I agree that dynamic SQL allows you more flexibility, but usually based on the assupmtion that you OWN the DB. And this is something that tend to become false over time. Even if you created and designed the DB for exclusive use of your app, given it becomes successful enough, someone else will start needing access it, and sonn you'll find that it's not your girl anymore. :-)
>

I've read stuff that counters every argument you list here. Of most importance to me is the statement that "Parameterized ad-hoc SQL works just as well"

http://weblogs.asp.net/rhoward/archive/2003/11/17/38095.aspx

and in this article http://www.simple-talk.com/2005/04/11/to-sp-or-not-to-sp-in-sql-server/ the guy says

"I do so knowing that I am not getting any unique security benefits using SPs, knowing that the performance benefits are not as clear cut as I once might have thought (but are still real in some cases), knowing how to leverage SPs to minimize the maintenance load, and understanding that I am more tied to SQL Server than I might be if I were to use ad-hoc SQL."

Try this:

Create a table called tblTest with a char (50) field called test_name. Add some dummy records to it. Just for fun add this text as the test_name of one of the records '';DROP TABLE tblTest

Now run this...

STORE SQLCONNECT('db', 'userid, 'password') TO gnConnHandle
IF gnConnHandle <= 0
   = MESSAGEBOX('Cannot make connection', 16, 'SQL Connect Error')
   RETURN .F.
ENDIF

LOCAL lcName
*Simulate user input.
lcName = "'';DROP TABLE tblTest"

*Dynamically build a where clause consisting of named parameters.
lcWhere = "where test_name = ?m.lcName"

*Dynamically build the SQL.
lcSQL = "select * from tblTest " + m.lcWhere
?m.lcSQL
SQLEXEC(m.gnConnHandle,m.lcSQL,"c_PersonList")
BROWSE
=SQLDISCONNECT(m.gnConnHandle)

The profiler showed that this executed...

exec sp_executesql N'select * from tblTest where test_name = @P1 ', N'@P1 varchar(21)', ''''';DROP TABLE tblTest'

I got back the record with this ''';DROP TABLE tblTest as the test_name field.

So the parameter content was not executed - which would have left an injection opening.

Isn't this the same effect?

Dim thisCommand As SQLCommand = New SQLCommand("SELECT Count(*) " & _
 "FROM Users WHERE UserName = @username AND Password = @password", Connection)
thisCommand.Parameters.Add ("@username", SqlDbType.VarChar).Value = username
thisCommand.Parameters.Add ("@password", SqlDbType.VarChar).Value = password
Dim thisCount As Integer = thisCommand.ExecuteScalar()

No string scrubbing and no injection attacks, yes?

If any of this is wrong, please let me know.

Thanks