In order to order to see how we can use a parameterized stored procedure during the login process, we need to set up our basic logon page. In this article I will only show the screen shot of the logon page, if you need the code for the page please follow this link to my web site and copy the code from there; http://www.Database-Security.Info/sqlinjectioncode_pt1.asp.
Standard Logon Page
http://www.database-security.info/Images/sqlinjection_pt1_image001.jpg
Most web develops will take the input from the two input boxes and concatenate those two inputs into a SQL string to be utilized by their code.
strSQL = "SELECT strusername, strpassword FROM tUser WHERE strusername = '" + username + "' AND strpassword = '" + password + "'"
set objRS = objDB.execute(strSQL)
if (objRS.EOF) then
'this is where you would do normal processing for invalid users
end if
'this is where you would do normal processing for valid users
If you use this typical method of logging in your users, you have left yourself open to SQL Injection. Yes, you could validate each of the user inputs to scrub the data for SQL
Injection, but validation has its own set of problems and sometimes requires a complex set of logic code to determine if the input is "bad" or "good".
You can greatly reduce the threat of SQL Injection in the case of the logon screen and in the case of missed threats during your input scrubbing by not creating the SQL statement in ASP and passing the user inputs as parameters to a stored procedure.
'Execute stored procedure
Set objConn = Server.CreateObject("ADODB.Command")
objConn.ActiveConnection = cn
objConn.CommandText = "spTestLogon"
objConn.CommandType = 4
'Parameter for stored procedure
SET objParam = objConn.CreateParameter("@strLogin",200, 1, 50)
objConn.Parameters.Append objParam
objConn.Parameters("@strLogin") = username
'Parameter for stored procedure
SET objParam = objConn.CreateParameter("@strPassWord",200, 1, 50)
objConn.Parameters.Append objParam
objConn.Parameters("@strPassWord") = password
Set objrso = objConn.Execute
If NOT objrso.EOF then
Response.Write "Access Granted"
Else
Response.Write "Access Denied"
End If