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.

>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.

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

Best regards,