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>
Posts
3 comments:
Very nice. Thank you for taking the time.
It works. Thank you.
Thanks for this. It is definitely better than the substitution functions currently making rounds.
Post a Comment