Tuesday, September 30, 2008

Classic ASP SQL Injection Prevention

Undoubtedly one of the most common vulnerabilities that I run across during penetration tests or web application security assessments is SQL injection. The fix is very easy for most programming languages, however one seems to be horribly neglected on the world wide web. If you search google for SQL injection prevention along with a specific language, you will run across many forum posts suggesting fixes, many of which are incorrect or simply deterrents that don't fix the root of the problem. More specifically, there is a lack of examples online for PROPERLY preventing SQL injection on Classic ASP pages.

With that being said, simple filtration of certain characters, keywords, and other attempts to deter SQL injection are many times quite laughable to a security professional such as myself who knows many ways to circumvent such countermeasures. Aside from some of the feeble attempts at prevention I've seen, the end goal is to properly secure your resources regardless of past code written. With the lack of Classic ASP examples to properly prevent SQL injection, I am providing an example simple login page below on how to correctly and incorrectly perform database queries using Classic ASP and VBScript. There are other methods than the one shown below that work, but this seems to be the simplest. Enjoy!


<%@ Language = "VBScript" %>
<%
Option Explicit
Dim cnnLogin, rstLogin, strUsername, strPassword, strSQL
Const adCmdText = 1 'Evaluate as a textual definition
Const adCmdStoredProc = 4 'Evaluate as a stored procedure
%>
<html>
<head><title>Login Page</title>
</head>
<body bgcolor="gray">
<%
If Request.Form("action") <> "validate_login" Then
%>
<form action="login.asp" method="post">
<input type="hidden" name="action" value="validate_login" />
<table border="0">
<tr>
<td align="right">Login:</td>
<td><input type="text" name="login" /></td>
</tr>
<tr>
<td align="right">Password:</td>
<td><input type="password" name="password" /></td>
</tr>
<tr>
<td align="right"></td>
<td><input type="submit" VALUE="Login" /></td>
</tr>
</table>
</form>
<%
Else
Set cnnLogin = Server.CreateObject("ADODB.Connection")
cnnLogin.open "PROVIDER=SQLOLEDB;DATA SOURCE=localhost;UID=dbuser;PWD=dbpassword;DATABASE=test"

'============================================================================================
'BAD WAY WITH CONCATENTATION DON'T DO IT!!!
'------------------------------------------
strSQL = "SELECT * FROM users WHERE username='" & Request.Form("login")& "' AND password='"_
& Request.Form("password") & "';"
Set rstLogin = cnnLogin.Execute(strSQL)
'============================================================================================

'CORRECT WAY - Parameterized Query with dynamic sql
<!--
strSQL = "SELECT * FROM users WHERE username=? AND password=?"
Dim cmd1
Set cmd1 = Server.CreateObject("ADODB.Command")
cmd1.ActiveConnection = cnnLogin
cmd1.CommandText = strSQL
cmd1.CommandType = adCmdText
cmd1.Parameters(0) = Request.Form("login")
cmd1.Parameters(1) = Request.Form("password")
Set rstLogin = cmd1.Execute()
-->

'CORRECT WAY - Parameterized Query with stored procedure
<!--
Dim cmd2
Set cmd2 = Server.CreateObject("ADODB.Command")
cmd2.ActiveConnection = cnnLogin
cmd2.CommandText = "login_sp"
cmd2.CommandType = adCmdStoredProc
cmd2.Parameters(1).Value = Request.Form("login")
cmd2.Parameters(2).Value = Request.Form("password")
Set rstLogin = cmd2.Execute
-->
If Not rstLogin.EOF Then
%>
<p>
<strong>Successfully Logged In!</strong>
</p>
<%
Else
%>
<p>
<font size="4" face="arial,helvetica"><strong>Login Failed!</strong></font>
</p>
<p>
<a href="login.asp">Try Again</a>
</p>
<%
'Response.End
End If

' Clean Up
rstLogin.Close
Set rstLogin = Nothing
cnnLogin.Close
Set cnnLogin = Nothing
End If
%>
</body>
</html>

3 comments:

Anonymous said...

Very nice. Thank you for taking the time.

Anonymous said...

It works. Thank you.

Anonymous said...

Thanks for this. It is definitely better than the substitution functions currently making rounds.