cdemott33
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:
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: (dog)& #39;haha&a mp;#39;
Now, when I attempt to login using the password (dog)'haha' the login fails.
Here's my code for the login page:
Could someone please help me and point me to what I'm doing incorrectly. Thanks!
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, "'", "&#39;") ' 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;")
' 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, "&#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
%>
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: &#40;dog&#41;&
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, "'", "&#39;") ' 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;")
' 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, "'", "'")
SQLRevString = Replace(SQLRevString, ">", ">")
SQLRevString = Replace(SQLRevString, "<", "<")
SQLRevString = Replace(SQLRevString, "(","(")
SQLRevString = Replace(SQLRevString, ")",")")
SQLRevString = Replace(SQLRevString, "&", "&")
SQLRevString = Replace(SQLRevString, "%", "%")
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
Could someone please help me and point me to what I'm doing incorrectly. Thanks!
ASKER
Hi Big Monty -
My updated function is below, however I'm still not able to login using that password:
FYI: I wrote a Response.Write to display the encoded password
and this is what it looks in a browser...
strPassword = &#40;dog&#41;\'hah a\'
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, ">", "&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;")
' replace vblf with <br /> (This is mainly used for Memo fields).
SQLString = Replace(SQLString, vblf,"<br />")
SQLString = Trim(SQLString)
ProtectSQL = SQLString
End Function
FYI: I wrote a Response.Write to display the encoded password
strPassword = ProtectSQL(Trim(Request.Form("txtPassword")))
and this is what it looks in a browser...
strPassword = &#40;dog&#41;\'hah
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.
I bet if you try it w/out the function, it'll work.
ASKER
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, ">", ">") ' replace > with >
SQLString = Replace(SQLString, "<", "<") ' replace < with <
SQLString = Replace(SQLString, "(","(") ' replace ( with (
SQLString = Replace(SQLString, ")",")") ' replace ) with )
SQLString = Replace(SQLString, "&", "&")
SQLString = Replace(SQLString, "%", "%")
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
%>
see my last comment
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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:
Also, I noticed that you are executing:
Should your WHERE clause have updatedPassword instead?
On another note, I agree that those functions are pointless since you are using parameterized queries.
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")))
Also, I noticed that you are executing:
SELECT ... , updatedPassword ... WHERE password = ?
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
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
SQLString = Replace(SQLString, "'", "&#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