Link to home
Start Free TrialLog in
Avatar of cdemott33
cdemott33Flag for United States of America

asked on

Vbscript Help Needed - Protecting Queries from special characters / SQL Injection

Hi EE - I could use your help.  I'm following the instructions listed in this EE article:

https://www.experts-exchange.com/articles/3626/ASP-Classic-Using-Parameterized-Queries.html

There are two functions in the article they suggest I create in order to protect from "idiots with nothing better to do" and I'd really like them to work.

Here's the code:

<%
Function ProtectSQL(SQLString)
SQLString = Replace(SQLString, "'", "&amp;#39;") ' replace single Quotes with Double Quotes
SQLString = Replace(SQLString, ">", "&amp;gt;") ' replace > with &amp;gt;
SQLString = Replace(SQLString, "<", "&amp;lt;") ' replace < with &amp;lt;
SQLString = Replace(SQLString, "(","&amp;#40;") ' replace ( with &amp;#40;
SQLString = Replace(SQLString, ")","&amp;#41;") ' replace ) with &amp;#41;
SQLString = Replace(SQLString, "&amp;", "&amp;amp;")
SQLString = Replace(SQLString, "%", "&amp;#37;")
' replace vblf with <br /> (This is mainly used for Memo fields).
SQLString = Replace(SQLString, vblf,"<br />") 
SQLString = Trim(SQLString)
ProtectSQL = SQLString
End Function
%>

<%
Function ReverseSQL(SQLString)
SQLRevString = Replace(SQLRevString, "&amp;#39;", "'") 
SQLRevString = Replace(SQLRevString, "&amp;gt;", ">") 
SQLRevString = Replace(SQLRevString, "&amp;lt;", "<") 
SQLRevString = Replace(SQLRevString, "&amp;#40;","(") 
SQLRevString = Replace(SQLRevString, "&amp;#41;",")") 
SQLRevString = Replace(SQLRevString, "&amp;amp;", "&amp;")
SQLRevString = Replace(SQLRevString, "%", "&amp;#37;")
SQLRevString = Replace(SQLRevString,"<br />", vblf)
SQLRevString = Trim(SQLRevString)
ReverseSQL = SQLRevString
End Function
%>

Open in new window


Here's what we tried to do.

We made an UPDATE password form that used the ProtectSQL function listed above to store the updated password.  

The password we created was:  (dog)'haha'

This was stored in the MS SQL database as:  &amp;#40;dog&amp;#41;&amp;#39;haha&amp;#39;

Now, when I attempt to login using the password (dog)'haha' the login fails.

Here's my code for the login page:

	Function ProtectSQL(SQLString)
		SQLString = Replace(SQLString, "'", "&amp;#39;") ' replace single Quotes with Double Quotes
		SQLString = Replace(SQLString, ">", "&amp;gt;") ' replace > with &amp;gt;
		SQLString = Replace(SQLString, "<", "&amp;lt;") ' replace < with &amp;lt;
		SQLString = Replace(SQLString, "(", "&amp;#40;") ' replace ( with &amp;#40;
		SQLString = Replace(SQLString, ")", "&amp;#41;") ' replace ) with &amp;#41;
		SQLString = Replace(SQLString, "&amp;", "&amp;amp;")
		SQLString = Replace(SQLString, "%", "&amp;#37;")
		' replace vblf with <br /> (This is mainly used for Memo fields).
		SQLString = Replace(SQLString, vblf,"<br />") 
		SQLString = Trim(SQLString)
		ProtectSQL = SQLString
	End Function
	
	Function ReverseSQL(SQLRevString)
		SQLRevString = Replace(SQLRevString, "&#39;", "'") 
		SQLRevString = Replace(SQLRevString, "&gt;", ">") 
		SQLRevString = Replace(SQLRevString, "&lt;", "<") 
		SQLRevString = Replace(SQLRevString, "&#40;","(") 
		SQLRevString = Replace(SQLRevString, "&#41;",")") 
		SQLRevString = Replace(SQLRevString, "&amp;", "&")
		SQLRevString = Replace(SQLRevString, "%", "&#37;")
		SQLRevString = Replace(SQLRevString, "<br />", vblf)
		SQLRevString = Trim(SQLRevString)
		ReverseSQL = SQLRevString
	End Function
		
	strLogin = Trim(Request.Form("txtLogin"))
	strPassword = ProtectSQL(Trim(Request.Form("txtPassword")))
	
	Response.write("strPassword = " & strPassword)

	Set Conn = CreateObject("ADODB.Connection")
	Conn.ConnectionString = connectionStr
	Conn.Open

	Set sqlUser = Server.CreateObject("ADODB.Command")
	sqlUser.ActiveConnection = Conn
	sqlUser.Prepared = true
	sqlUser.commandtext	= 	"SELECT ID, name, eMailAddress, rights, webadmin, updatedPassword " & _
							"FROM Login " & _
							"WHERE userName =? " & _
							"AND password = ? "
	sqlUser.Parameters.Append sqlUser.CreateParameter("@Username", 200, 1, 150, strLogin) 
	sqlUser.Parameters.Append sqlUser.CreateParameter("@Password", 200, 1, 150, strPassword) 
	set rsUser = sqlUser.execute
	
	If Not rsUser.EOF Then	
		strUserFound = "True"
		strID = rsUser.Fields("ID").value
		strName = rsUser.Fields("name").value
		strEmail = rsUser.Fields("eMailAddress").value
		strRights = rsUser.Fields("rights").value	
		strWebAdmin = rsUser.Fields("webadmin").value	
		strUpdatedPassword = rsUser.Fields("updatedPassword").value		
	Else
		strUserFound = "False"
	End If 
	
	rsUser.close
	set rsUser = Nothing
	Conn.close
	set Conn = Nothing

Open in new window


Could someone please help me and point me to what I'm doing incorrectly.  Thanks!
Avatar of Big Monty
Big Monty
Flag of United States of America image

the following line in ProtectSQL converts any single quotes to double quotes:

SQLString = Replace(SQLString, "'", "&amp;#39;")

that obviously changes the value of your password, that's why it fails. I would change that line to ENCODE the single quote, so it would be:

SQLString = Replace(SQLString, "'", "\'")

\' will escape your single quote in your sql instead of changing it to a double quote
Avatar of cdemott33

ASKER

Hi Big Monty -

My updated function is below, however I'm still not able to login using that password:

Function ProtectSQL(SQLString)
		SQLString = Replace(SQLString, "'", "\'")' replace single Quotes with Double Quotes
		SQLString = Replace(SQLString, ">", "&amp;gt;") ' replace > with &amp;gt;
		SQLString = Replace(SQLString, "<", "&amp;lt;") ' replace < with &amp;lt;
		SQLString = Replace(SQLString, "(", "&amp;#40;") ' replace ( with &amp;#40;
		SQLString = Replace(SQLString, ")", "&amp;#41;") ' replace ) with &amp;#41;
		SQLString = Replace(SQLString, "&amp;", "&amp;amp;")
		SQLString = Replace(SQLString, "%", "&amp;#37;")
		' replace vblf with <br /> (This is mainly used for Memo fields).
		SQLString = Replace(SQLString, vblf,"<br />") 
		SQLString = Trim(SQLString)
		ProtectSQL = SQLString
	End Function

Open in new window


FYI:  I wrote a Response.Write to display the encoded password

strPassword = ProtectSQL(Trim(Request.Form("txtPassword")))

Open in new window


and this is what it looks in a browser...

strPassword = &amp;#40;dog&amp;#41;\'haha\'
so now that I think about it, you actually don't even need the ProtectSQL function since you're using parameterized queries. The reason being is when you use and build PQ's, you're not concatenating any strings of VALUES to the sql query, but rather you're setting the properties of the PQ object to contain your values.

I bet if you try it w/out the function, it'll work.
FYI:  I'm not sure if this will be helpful or not but this is the code I used to UPDATE the password in my database.   As you can see I'm running my password (ie (dog)'haha' ) through the ProtectSQL function first and then storing the returned value in my database.  Is this the correct way to do it?

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include file="openConn.asp" -->
<!--#include file="adovbs.asp" -->
<%

	Dim pwd
	
	Function ProtectSQL(SQLString)
		SQLString = Replace(SQLString, "'", "\'")' replace single Quotes with Double Quotes
		SQLString = Replace(SQLString, ">", "&gt;") ' replace > with &gt;
		SQLString = Replace(SQLString, "<", "&lt;") ' replace < with &lt;
		SQLString = Replace(SQLString, "(","&#40;") ' replace ( with &#40;
		SQLString = Replace(SQLString, ")","&#41;") ' replace ) with &#41;
		SQLString = Replace(SQLString, "&", "&amp;")
		SQLString = Replace(SQLString, "%", "&#37;")
		SQLString = Replace(SQLString, vblf,"<br />") ' replace vblf with <br /> (This is mainly used for Memo fields.
		SQLString = Trim(SQLString)
		ProtectSQL = SQLString
	End Function
	
	
	If Request.Form("protectedPwd") <> "" Then
		pwd = ProtectSQL(Request.form("textPwd"))
	
		Set Conn = CreateObject("ADODB.Connection")
		Conn.ConnectionString = connectionStr
		Conn.Open

		Set sqlUser = Server.CreateObject("ADODB.Command")
		sqlUser.ActiveConnection = Conn
		sqlUser.Prepared = true
		sqlUser.commandtext	= 	"UPDATE login SET password=? WHERE userName = 'lpark'"
		sqlUser.Parameters.Append sqlUser.CreateParameter("@Password", 200, 1, 150, pwd) 
		set rsUser = sqlUser.execute
		
		Conn.close
		set Conn = Nothing
	End If
	
%>

Open in new window

see my last comment
As far as SQL Inject Attacks, do you believe that using Parameterized Queries is sufficient? Using those two function to encode isn't necessary?
ASKER CERTIFIED SOLUTION
Avatar of Big Monty
Big Monty
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you for your help today.  It was very much appreciated.
>> We made an UPDATE password form
Was there an "INSERT" form -- a form to create the account to begin with? If so, did you call ProtectSQL() on the username as well? If not you should have.  The point of those functions of to encode all the data going into the DB, not just specific fields like the password.

So, assuming you called protectSQL() on all those parameters, then when you retrieve the data, you also need to call protectSQL() on all the parameters.  On line 28 of your second code block of your original post you have:
	strLogin = Trim(Request.Form("txtLogin")) ' <=== You need to call ProtectSQL() on this as well
	strPassword = ProtectSQL(Trim(Request.Form("txtPassword")))

Open in new window


Also, I noticed that you are executing:
SELECT ... , updatedPassword ... WHERE password = ?

Open in new window


Should your WHERE clause have updatedPassword instead?

On another note, I agree that those functions are pointless since you are using parameterized queries.
I'm not so sure protectSQL() should be used at all, as I don't believe it works 100%. Changing a single quote to double quotes is not encoding, it is changing the overall data.
>> I'm not so sure protectSQL() should be used at all, as I don't believe it works 100%
Like I said, "I agree".  I remember writing a long comment/feedback on that article before my computer crashed and I didn't bother to go back and re-post my comment.  
a. It is unnecessary when using parameterized queries
b. it modifies the actual data.
c. the reverse function does not work