SQL Injection in Stored Procedure : 2nd Case Study

Stored procedure with dynamic SQL and embedded parameters

The Stored Procedure

Create proc authenticate (@uid nvarchar(25),@pwd nvarchar(25))
as
DECLARE @uid VARCHAR(64)
DECLARE @pwd VARCHAR(64)
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)

/* Build the SQL string once.*/

SET @SQLString =
N'SELECT * FROM users WHERE userid = @uid AND password = @pwd'

SET @ParmDefinition = N'@login VARCHAR(64), @password VARCHAR(64)'


Server side code:

cmd.CommandText = "authenticate";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add( "@uid", strUserName);
cmd.Paramerters.Add( “@pwd, strPassword);
con.Open();

string result = (string)cmd.ExecuteScalar();
oCon.Close();

In this case,bSQL Injection would NOT be possible. Hence what I would like to summarize is if at all we have to use dynamic SQL in stored procedure, always use embedded parameters in dynamic SQL