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


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

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


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


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

Note: The above example assumes passwords are stored in plaintext. For real-world use, they should be stored using a salted hash.

Tags: asp, databases
Page last updated 2008-06-06 20:39. Some rights reserved (CC by 3.0)