Techniques for avoiding SQL injection
A common type of attack against web applications exposed on the Internet involves sending specially crafted malformed or otherwise unexpected data to the application interfaces. Because many web applications use a database backend, a typical attack will involve sending pieces of SQL statements as parameters to the application pages, trying to exploit the possibility that the parameters are used directly in SQL statements used to communicate with the database backend.A successful attack can, depending of the credentials of the database login used, give the attacker read or write access to the entire database. The best way of avoiding SQL injection attacks in web applications exposed on the Internet, is to validate all input before it reaches the database backend and to avoid using form and querystring data directly in the SQL statements.
This page contains three methods of achieving SQL injection immunity in web applications written in classic ASP.
Method 1: Data retrieval only, simple numeric input conversion
Retrieving a record using numeric data from the querystring:
Option Explicit
Dim strQSID
strQSID = Left(Request.QueryString("pageid"), 9)
If IsNumeric(strQSID) Then
GetName(CLng(strQSID))
Else
Response.Write("pageid must be numeric")
End If
Sub GetName(id)
Dim strDbConnection
Dim objConn
Dim objRS
Dim strSQL
strDbConnection = Application("dbConnectionString")
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open(strDbConnection)
strSQL = "SELECT fullName FROM persons WHERE uID = " & id
Set objRS = objConn.Execute(strSQL)
If objRS.EOF Then
Response.Write("ID not found")
Else
Response.Write(objRS.Fields("fullName").value)
End If
objRS.Close()
Set objRS = Nothing
objConn.Close()
Set objConn = Nothing
End Sub
Dim strQSID
strQSID = Left(Request.QueryString("pageid"), 9)
If IsNumeric(strQSID) Then
GetName(CLng(strQSID))
Else
Response.Write("pageid must be numeric")
End If
Sub GetName(id)
Dim strDbConnection
Dim objConn
Dim objRS
Dim strSQL
strDbConnection = Application("dbConnectionString")
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open(strDbConnection)
strSQL = "SELECT fullName FROM persons WHERE uID = " & id
Set objRS = objConn.Execute(strSQL)
If objRS.EOF Then
Response.Write("ID not found")
Else
Response.Write(objRS.Fields("fullName").value)
End If
objRS.Close()
Set objRS = Nothing
objConn.Close()
Set objConn = Nothing
End Sub
Method 2: Data retrieval only, text input, comparing data in the ASP code
Checking a login/password from a submitted form:
Option Explicit
Dim strDbConnection
Dim objConn
Dim objRS
Dim strSQL
Dim strUserLogin
Dim strUserPassword
strDbConnection = Application("dbConnectionString")
strUserLogin = Left(Request.Form("userlogin"), 255)
strUserPassword = Left(Request.Form("userpassword"), 255)
Set objConn = Server.CreateObject("ADODB.Connection")
strSQL = "SELECT userLogin, userPassword FROM Users"
objConn.Open(strDbConnection)
Set objRS = objConn.Execute(strSQL)
Do While Not objRS.EOF
If (objRS.Fields("userLogin").value = strUserLogin) And _
(objRS.Fields("userPassword").value = strUserPassword) Then
Session("LoggedIn") = objRS.Fields("userLogin").value
End If
objRS.MoveNext()
Loop
objRS.Close()
Set objRS = Nothing
objConn.Close()
Set objConn = Nothing
If Session("LoggedIn") <> "" Then
Response.Redirect("loggedinpage.asp")
Else
Response.Redirect("login.asp")
End If
Dim strDbConnection
Dim objConn
Dim objRS
Dim strSQL
Dim strUserLogin
Dim strUserPassword
strDbConnection = Application("dbConnectionString")
strUserLogin = Left(Request.Form("userlogin"), 255)
strUserPassword = Left(Request.Form("userpassword"), 255)
Set objConn = Server.CreateObject("ADODB.Connection")
strSQL = "SELECT userLogin, userPassword FROM Users"
objConn.Open(strDbConnection)
Set objRS = objConn.Execute(strSQL)
Do While Not objRS.EOF
If (objRS.Fields("userLogin").value = strUserLogin) And _
(objRS.Fields("userPassword").value = strUserPassword) Then
Session("LoggedIn") = objRS.Fields("userLogin").value
End If
objRS.MoveNext()
Loop
objRS.Close()
Set objRS = Nothing
objConn.Close()
Set objConn = Nothing
If Session("LoggedIn") <> "" Then
Response.Redirect("loggedinpage.asp")
Else
Response.Redirect("login.asp")
End If
Note: The above example assumes passwords are stored in plaintext. For real-world use, they should be stored using a salted hash.
Method 3: Data retrieval and updating, numeric and text string input using ADO command parameters (Stored procedure-ish)
Retrieving rows using data from the querystring:
Option Explicit
Dim strDbConnection
Dim objConn
Dim objCommand
Dim strSQL
Dim objParam
Dim objRS
Dim strQSBirthDate
Const adInteger = 3
Const adVarWChar = 202
Const adBoolean = 11
Const adGUID = 72
Const adDBTimeStamp = 135
Const adParamInput = &H0001
Const adParamOutput = &H0002
Const adCmdText = &H0001
Const adCmdStoredProc = &H0004
'Look in adovbs.inc for more data types
strDbConnection = Application("dbConnectionString")
strQSBirthDate = Left(Request.QueryString("birthdate"), 19)
Set objCommand = Server.CreateObject("ADODB.Command")
strSQL = "SELECT * FROM persons WHERE birthDate > ?"
objCommand.CommandText = strSQL
objCommand.CommandType = adCmdText
Set objParam = objCommand.CreateParameter _
(, adDBTimeStamp, adParamInput, 8, strQSBirthDate)
objCommand.Parameters.Append(objParam)
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open(strDbConnection)
objCommand.ActiveConnection = objConn
Set objRS = Server.CreateObject("ADODB.Recordset")
Set objRS = objCommand.Execute
If objRS.EOF Then
Response.Write("No items found")
Else
Do While Not objRS.EOF
Response.Write(objRS.Fields("birthDate").value & ", ")
Response.Write(objRS.Fields("fullName").value & "<br />")
objRS.MoveNext()
Loop
End If
objRS.Close()
Set objRS = Nothing
objConn.Close()
Set objConn = Nothing
Dim strDbConnection
Dim objConn
Dim objCommand
Dim strSQL
Dim objParam
Dim objRS
Dim strQSBirthDate
Const adInteger = 3
Const adVarWChar = 202
Const adBoolean = 11
Const adGUID = 72
Const adDBTimeStamp = 135
Const adParamInput = &H0001
Const adParamOutput = &H0002
Const adCmdText = &H0001
Const adCmdStoredProc = &H0004
'Look in adovbs.inc for more data types
strDbConnection = Application("dbConnectionString")
strQSBirthDate = Left(Request.QueryString("birthdate"), 19)
Set objCommand = Server.CreateObject("ADODB.Command")
strSQL = "SELECT * FROM persons WHERE birthDate > ?"
objCommand.CommandText = strSQL
objCommand.CommandType = adCmdText
Set objParam = objCommand.CreateParameter _
(, adDBTimeStamp, adParamInput, 8, strQSBirthDate)
objCommand.Parameters.Append(objParam)
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open(strDbConnection)
objCommand.ActiveConnection = objConn
Set objRS = Server.CreateObject("ADODB.Recordset")
Set objRS = objCommand.Execute
If objRS.EOF Then
Response.Write("No items found")
Else
Do While Not objRS.EOF
Response.Write(objRS.Fields("birthDate").value & ", ")
Response.Write(objRS.Fields("fullName").value & "<br />")
objRS.MoveNext()
Loop
End If
objRS.Close()
Set objRS = Nothing
objConn.Close()
Set objConn = Nothing
Inserting a row using data from the querystring:
Option Explicit
Dim strDbConnection
Dim objConn
Dim objCommand
Dim strSQL
Dim objParam
Dim strQSFullName
Dim strQSBirthDate
Const adInteger = 3
Const adVarWChar = 202
Const adBoolean = 11
Const adGUID = 72
Const adDBTimeStamp = 135
Const adParamInput = &H0001
Const adParamOutput = &H0002
Const adCmdText = &H0001
Const adCmdStoredProc = &H0004
'Look in adovbs.inc for more data types
strDbConnection = Application("dbConnectionString")
strQSFullName = Left(Request.QueryString("fullname"), 255)
strQSBirthDate = Left(Request.QueryString("birthdate"), 19)
Set objCommand = Server.CreateObject("ADODB.Command")
strSQL = "INSERT INTO persons (fullName, birthDate) VALUES (?, ?)"
objCommand.CommandText = strSQL
objCommand.CommandType = adCmdText
Set objParam = objCommand.CreateParameter _
(, adVarWChar, adParamInput, 255, strQSFullName)
objCommand.Parameters.Append(objParam)
Set objParam = objCommand.CreateParameter _
(, adDBTimeStamp, adParamInput, 8, strQSBirthDate)
objCommand.Parameters.Append(objParam)
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open(strDbConnection)
objCommand.ActiveConnection = objConn
objCommand.Execute
objConn.Close()
Set objConn = Nothing
Dim strDbConnection
Dim objConn
Dim objCommand
Dim strSQL
Dim objParam
Dim strQSFullName
Dim strQSBirthDate
Const adInteger = 3
Const adVarWChar = 202
Const adBoolean = 11
Const adGUID = 72
Const adDBTimeStamp = 135
Const adParamInput = &H0001
Const adParamOutput = &H0002
Const adCmdText = &H0001
Const adCmdStoredProc = &H0004
'Look in adovbs.inc for more data types
strDbConnection = Application("dbConnectionString")
strQSFullName = Left(Request.QueryString("fullname"), 255)
strQSBirthDate = Left(Request.QueryString("birthdate"), 19)
Set objCommand = Server.CreateObject("ADODB.Command")
strSQL = "INSERT INTO persons (fullName, birthDate) VALUES (?, ?)"
objCommand.CommandText = strSQL
objCommand.CommandType = adCmdText
Set objParam = objCommand.CreateParameter _
(, adVarWChar, adParamInput, 255, strQSFullName)
objCommand.Parameters.Append(objParam)
Set objParam = objCommand.CreateParameter _
(, adDBTimeStamp, adParamInput, 8, strQSBirthDate)
objCommand.Parameters.Append(objParam)
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open(strDbConnection)
objCommand.ActiveConnection = objConn
objCommand.Execute
objConn.Close()
Set objConn = Nothing
Checking a login/password from a submitted form:
Option Explicit
Dim strDbConnection
Dim objConn
Dim objCommand
Dim strSQL
Dim objParam
Dim objRS
Dim strUserLogin
Dim strUserPassword
Const adInteger = 3
Const adVarWChar = 202
Const adBoolean = 11
Const adGUID = 72
Const adDBTimeStamp = 135
Const adParamInput = &H0001
Const adParamOutput = &H0002
Const adCmdText = &H0001
Const adCmdStoredProc = &H0004
'Look in adovbs.inc for more data types
strDbConnection = Application("dbConnectionString")
strUserLogin = Left(Request.Form("userlogin"), 255)
strUserPassword = Left(Request.Form("userpassword"), 255)
Set objCommand = Server.CreateObject("ADODB.Command")
strSQL = "SELECT * FROM Users WHERE userLogin = ? AND userPassword = ?"
objCommand.CommandText = strSQL
objCommand.CommandType = adCmdText
Set objParam = objCommand.CreateParameter _
(, adVarWChar, adParamInput, 255, strUserLogin)
objCommand.Parameters.Append(objParam)
Set objParam = objCommand.CreateParameter _
(, adVarWChar, adParamInput, 255, strUserPassword)
objCommand.Parameters.Append(objParam)
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open(strDbConnection)
objCommand.ActiveConnection = objConn
Set objRS = Server.CreateObject("ADODB.Recordset")
Set objRS = objCommand.Execute
If objRS.EOF Then
Session("LoggedIn") = ""
Else
Session("LoggedIn") = objRS.Fields("userLogin").value
End If
objRS.Close()
Set objRS = Nothing
objConn.Close()
Set objConn = Nothing
If Session("LoggedIn") <> "" Then
Response.Redirect("loggedinpage.asp")
Else
Response.Redirect("login.asp")
End If
Dim strDbConnection
Dim objConn
Dim objCommand
Dim strSQL
Dim objParam
Dim objRS
Dim strUserLogin
Dim strUserPassword
Const adInteger = 3
Const adVarWChar = 202
Const adBoolean = 11
Const adGUID = 72
Const adDBTimeStamp = 135
Const adParamInput = &H0001
Const adParamOutput = &H0002
Const adCmdText = &H0001
Const adCmdStoredProc = &H0004
'Look in adovbs.inc for more data types
strDbConnection = Application("dbConnectionString")
strUserLogin = Left(Request.Form("userlogin"), 255)
strUserPassword = Left(Request.Form("userpassword"), 255)
Set objCommand = Server.CreateObject("ADODB.Command")
strSQL = "SELECT * FROM Users WHERE userLogin = ? AND userPassword = ?"
objCommand.CommandText = strSQL
objCommand.CommandType = adCmdText
Set objParam = objCommand.CreateParameter _
(, adVarWChar, adParamInput, 255, strUserLogin)
objCommand.Parameters.Append(objParam)
Set objParam = objCommand.CreateParameter _
(, adVarWChar, adParamInput, 255, strUserPassword)
objCommand.Parameters.Append(objParam)
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open(strDbConnection)
objCommand.ActiveConnection = objConn
Set objRS = Server.CreateObject("ADODB.Recordset")
Set objRS = objCommand.Execute
If objRS.EOF Then
Session("LoggedIn") = ""
Else
Session("LoggedIn") = objRS.Fields("userLogin").value
End If
objRS.Close()
Set objRS = Nothing
objConn.Close()
Set objConn = Nothing
If Session("LoggedIn") <> "" Then
Response.Redirect("loggedinpage.asp")
Else
Response.Redirect("login.asp")
End If
Note: The above example assumes passwords are stored in plaintext. For real-world use, they should be stored using a salted hash.
Page last updated 2008-06-06 20:39. Some rights reserved (CC by 3.0)