>Hi, Mike.
>
>>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."
>
>Please, read again my message. What they are saying is almost the same I said. Indeed, they are both more pro-SPs than I am, it seems. I'm not really a big fan of SPs, but learned to work with them because so many people want them in place, so I don't have the options on my side.

I'm starting to believe it's because people blindly believe they need to validate/scrub user input. That's just plain wrong - if you use parameters.

>
>>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()

>
>Well, this is all dynamic SQL, not SPs. Dynamic SQL tends to be a better target for SQL injection. Notice, though, that neither of this attacks can succeed if you take the simple measure of ALWAYS VALIDATE USER INPUT, which is something I always make a point about. Of course, I won't use string concatenation neither.

The code I posted demonstrated no need to validate user input. I concatenated the string using the variable names, not their content. That's the key point.

>
>Even so, SPs are not avoiding the chance if you get the parameters and do the same inside the SP.
>

Right!