SQL Injection in Stored Procedure

Let us examine SQL Injection in Stored Procedure. This would be 1 of the vulnerable cases.

The Server Side Code would be something like:
oCmd.CommandText = "sp_login";
oCmd.CommandType = CommandType.StoredProcedure;
oCmd.Parameters.Add( "@loginId", strUserName);
oCmd.Paramerters.Add( “@password”, strPassword);
oCon.Open();
string result = (string)oCmd.ExecuteScalar();
oCon.Close();
====================================================================
The Stored Procedure would be:
CREATE PROC sp_login (@loginid nvarchar(25),@password)
AS
DECLARE @SQLString NVARCHAR(500)
DECLARE @loginid VARCHAR(64)
DECLARE @password VARCHAR(64)

/* Build the SQL string once.*/

SET @SQLString = 'SELECT * from cust_users WHERE login_id = '+ ''''+@loginid+'''' + 'AND password = '+ ''''+@password+''''

EXECUTE sp_executesql @SQLString

====================================================================

If the user input is as follows:
loginId = ' OR 1=1 --
password = junk

The above stored procedure will have an injection attack. The procedure executing will return all the rows because of the injected SQL.

Cheers.