class asp 의 Parameterized queries 알아보기

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

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:

 

The Exchange of information is power; Power is the Exchange of Knowledge; 
Knowledge is the Power that you have to help others in need of your help. 
The Exchange of Information! 

The information below is for people that want to learn how to use Parameterized Queries in their Classic ASP scripts. I was forced to learn this back in the beginning of 2009 and have taken off with it. There is nothing really tough about it, just read the liner notes and then try it out for yourself.

In this tutorial below, I am giving you several different scenarios and the code for them. It works all out at the end.

What are Parameterized queries?

Parameterized queries are queries that have one or more embedded parameters in the SQL statement. This method of embedding parameters into a SQL statement is less prone to errors than the method of dynamically building up a SQL string.

-- source: Taken from enterprisedb.com

Tutorial Begins

Now, on with the code/lesson... 

Note: If you have any questions please feel free to ask away. For this lesson, you will need to have the ADOVBS.inc file, which can be downloaded from here:
Download ADOVBS.INC

0. Getting Started

Make sure that the above line is at the very top of your page, and only one @ is allowed per page

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>

Open in new window

Make sure that you are using the charset=utf-8, if not then you will lose some of your protection!

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

Open in new window

This our CSS to be used at the bottom of the page

<style type="text/css">
.MyTD{
border:1px #000000 double;
text-align:left;
vertical-align:top;
padding:3px;
color:#999999;
background-color:#333333;
}
</style>

Open in new window

Example of a FORM, none functional at this point

<form>
<input type="hidden" name="MyID" value="<%=getID%>" />
<input type="text" name="loginEmail" value="" />
</form>

Open in new window

Example of a QueryString, none functional at this point, to be used on the Multiple and below

page.asp?ID=1&Email=me@site.com

Open in new window

First, we need to make sure that no one can attack our codes, so we are going to use my custom ProtectSQL script.

This will be used to PROTECT your code/database from being attacked by idiots with nothing better to do.

<%
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
%>

Open in new window

When you call the codes back out, just do the ProtectSQL function in reverse, basically create a new Function and do a reverse on it. This will be used to display the data to the page (To your visitor)

<%
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
%>

Open in new window

These are our Variables for our Parameters. For ALL Examples, as you can see, we have wrapped the ProtectSQL() around each one, so that we can capture ALL the bad things that someone might want to throw at us.

<%
loginEmail = ProtectSQL(request.Form("loginEmail"))
loginPass = ProtectSQL(request.Form("Password"))
myID = ProtectSQL(request.Form("myID"))
%>

Open in new window

1. Using Parameters with text VarChar, with a field length of 25

<%
Set chEmail = Server.CreateObject("ADODB.Command")
chEmail.ActiveConnection=objConn
chEmail.Prepared = true
chEmail.commandtext="SELECT cusEmail, password, mydate FROM ordercavecustomer WHERE cusEmail =?"
chEmail.Parameters.Append chEmail.CreateParameter("@cusEmail", adVarChar, adParamInput, 25, loginEmail)
set rschEmail = chEmail.execute
%>

Open in new window

2. Using Parameters with the Integer (INT)

As you can tell, we are not adding in a number, this is because the INT does not require a length, it can be any length up to 1 billion.

<%

Set chEmail = Server.CreateObject("ADODB.Command")
chEmail.ActiveConnection=objConn
chEmail.Prepared = true
chEmail.commandtext="SELECT cusEmail, password, myID FROM ordercavecustomer WHERE myID =?"
chEmail.Parameters.Append chEmail.CreateParameter("@myID", adInteger, adParamInput, , getmyID)
set rschEmail = chEmail.execute
%>

Open in new window

3. We Are Going to Get Multiple Queries

Let's get these from our QueryString. As you can see, we have the Parameters in order of the way they are listed in our Statement, if not, then it will give you an error.

<%
getID = ProtectSQL(request.QueryString("ID"))
getEmail = ProtectSQL(request.QueryString("Email"))
Set chEmail = Server.CreateObject("ADODB.Command")
chEmail.ActiveConnection=objConn
chEmail.Prepared = true
chEmail.commandtext="SELECT cusEmail, password, myID FROM ordercavecustomer WHERE myID =? and cusEmail=?"
chEmail.Parameters.Append chEmail.CreateParameter("@myID", adInteger, adParamInput, , getmyID)
chEmail.Parameters.Append chEmail.CreateParameter("@cusEmail", adVarChar, adParamInput, 25, loginEmail)
set rschEmail = chEmail.execute
%>

Open in new window

4. INSERT Statement

Once again, we have to have everything in order, to make sure that it gets inserted correctly and without error.

<%
Set chEmail = Server.CreateObject("ADODB.Command")
chEmail.ActiveConnection=objConn
chEmail.commandtext="INSERT into ordercavecustomer(cusEmail, password, myID)values(?,?,?)"
chEmail.Parameters.Append chEmail.CreateParameter("@cusEmail", adVarChar, adParamInput, 25, loginEmail)
chEmail.Parameters.Append chEmail.CreateParameter("@password", adVarChar, adParamInput, 25, loginPass)
chEmail.Parameters.Append chEmail.CreateParameter("@myID", adInteger, adParamInput, , getmyID)
set rschEmail = chEmail.execute
%>

Open in new window

5. UPDATE Statement

Same as before, in order as they are written. The WHERE goes last, and as you can see, it is also last in the parameters list.

<%
Set chEmail = Server.CreateObject("ADODB.Command")
chEmail.ActiveConnection=objConn
chEmail.commandtext="update ordercavecustomer set cusEmail=?, password=? where myID=?"
chEmail.Parameters.Append chEmail.CreateParameter("@cusEmail", adVarChar, adParamInput, 25, loginEmail)
chEmail.Parameters.Append chEmail.CreateParameter("@password", adVarChar, adParamInput, 25, loginPass)
chEmail.Parameters.Append chEmail.CreateParameter("@myID", adInteger, adParamInput, , getmyID)
set rschEmail = chEmail.execute
%>

Open in new window

6. DELETE Statement

This example will DELETE the item with the ID of whatever it is in the QueryString (or) FORM

<%

Set chEmail = Server.CreateObject("ADODB.Command")
chEmail.ActiveConnection=objConn
chEmail.commandtext="delete from ordercavecustomer where myID=?"
chEmail.Parameters.Append chEmail.CreateParameter("@myID", adInteger, adParamInput, , getmyID)
set rschEmail = chEmail.execute
%>

Open in new window

7. Display the information to the visitor with the ReverseSQL in place.

<%

Set chEmail = Server.CreateObject("ADODB.Command")
chEmail.ActiveConnection=objConn
chEmail.Prepared = true
chEmail.commandtext="SELECT cusEmail, password, username, mydate, fname, lname FROM ordercavecustomer WHERE cusEmail =?"
chEmail.Parameters.Append chEmail.CreateParameter("@cusEmail", adVarChar, adParamInput, 25, loginEmail)
set rschEmail = chEmail.execute
' first we need to make sure that a record exist for the Query
if not rschEmail.eof then
' Now. We want to show our information back to our visitor, so we need to reverse what we have protected. So we wrap our recordsets with the ReverseSQL Function
strEmail = ReverseSQL(rschEmail("cusEmail"))
strpassword = ReverseSQL(rschEmail("password"))
strusername = ReverseSQL(rschEmail("username"))
strmydate = rschEmail("mydate")
strfname = ReverseSQL(rschEmail("fname"))
strlname = ReverseSQL(rschEmail("lname"))
elseif rschEmail.eof then
response.Write "Sorry, the user does not exist in our system, Sorry! Please try again later."
end if
%>
<table>
<tr><td class="MyTD">Full Name</td><td class="MyTD"><%=strfname&" "&strlname%></td></tr>
<tr><td class="MyTD">Email</td><td class="MyTD"><%=strEmail%></td></tr>
<tr><td class="MyTD">Username</td><td class="MyTD"><%=strusername%></td></tr>
<tr><td class="MyTD">Password</td><td class="MyTD"><%=strpassword%></td></tr>
<tr><td class="MyTD">Date Joined</td><td class="MyTD"><%=strmydate%></td></tr>
</table>

Open in new window

The copy/paste version is below.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%'Make sure that the above line is at the very top of your page, and only one @ is allowed per page%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtm<wbr ></wbr>l1/DTD/xht<wbr ></wbr>ml1-transi<wbr ></wbr>tional.dtd<wbr ></wbr>">
<html xmlns="http://www.w3.org/1<wbr ></wbr>999/xhtml"<wbr ></wbr>>
<head>
<%'Make sure that you are using the charset=utf-8, if not then you will loose some of your protection!%>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<%'our CSS to be used at the bottom of the page%>
<style type="text/css">
.MyTD{
border:1px #000000 double;
text-align:left;
vertical-align:top;
padding:3px;
color:#999999;
background-color:#333333;
}
</style>
</head>
<body>
<%'Example of a FORM, none functional at this point%>
<form>
<input type="hidden" name="MyID" value="<%=getID%>" />
<input type="text" name="loginEmail" value="" />
</form>
<%'Example of a Querystring, none functional at this point, to be used on the Multiple and below%>
page.asp?ID=1&Email=me<wbr ></wbr>@site.com
<%
' First thing is First, we need to make sure that no one can attach our codes
' So we are going to use my custom ProtectSQL script.

' This will be used to PROTECT your code/database from being attacked by idiots with nothing better to do.
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

' When you call the codes back out, just do the ProtectSQL function in reverse, basically create a new Function and do a reverse on it.
' This will be used to display the data to the page (To your visitor)
Function ReverseSQL(SQLString)
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)
Reverse = SQLRevString
End Function




' These are our Variables for our Parameters. For ALL Examples, as you can see, we have wrapped the ProtectSQL() around each one, so that we can capture ALL the bad things that someone might want to throw at us.
loginEmail = ProtectSQL(request.Form("l<wbr ></wbr>oginEmail"<wbr ></wbr>))
loginPass = ProtectSQL(request.Form("P<wbr ></wbr>assword"))<wbr ></wbr>
myID = ProtectSQL(request.Form("m<wbr ></wbr>yID"))


' Example #1. using Parameters with text VarChar, with a field length of 25
Set chEmail = Server.CreateObject("ADODB<wbr ></wbr>.Command")<wbr ></wbr>
chEmail.ActiveConnection=o<wbr ></wbr>bjConn
chEmail.Prepared = true
chEmail.commandtext="SELEC<wbr ></wbr>T cusEmail, password, mydate FROM ordercavecustomer WHERE cusEmail =?"
chEmail.Parameters.Append chEmail.CreateParamet<wbr ></wbr>er("@cusEm<wbr ></wbr>ail", adVarChar, adParamInput, 25, loginEmail)
set rschEmail = chEmail.execute

'Example #2. using Parameters with the Integer (INT)
' As you can tell, we are not adding in a number, this is because the INT does not require a length, it can be any length up to 1 billion.

Set chEmail = Server.CreateObject("ADODB<wbr ></wbr>.Command")<wbr ></wbr>
chEmail.ActiveConnection=o<wbr ></wbr>bjConn
chEmail.Prepared = true
chEmail.commandtext="SELEC<wbr ></wbr>T cusEmail, password, myID FROM ordercavecustomer WHERE myID =?"
chEmail.Parameters.Append chEmail.CreateParamet<wbr ></wbr>er("@myID"<wbr ></wbr>, adInteger, adParamInput, , getmyID)
set rschEmail = chEmail.execute

' Example #3. We are going to get multiple Queries
' Lets get these from our QueryString
' As you can see, we have the Parameters in order of the way they are listed in our Statement, if not, then it will give you an error.
getID = ProtectSQL(request.QuerySt<wbr ></wbr>ring("ID")<wbr ></wbr>)
getEmail = ProtectSQL(request.QuerySt<wbr ></wbr>ring("Emai<wbr ></wbr>l"))

Set chEmail = Server.CreateObject("ADODB<wbr ></wbr>.Command")<wbr ></wbr>
chEmail.ActiveConnection=o<wbr ></wbr>bjConn
chEmail.Prepared = true
chEmail.commandtext="SELEC<wbr ></wbr>T cusEmail, password, myID FROM ordercavecustomer WHERE myID =? and cusEmail=?"
chEmail.Parameters.Append chEmail.CreateParamet<wbr ></wbr>er("@myID"<wbr ></wbr>, adInteger, adParamInput, , getmyID)
chEmail.Parameters.Append chEmail.CreateParamet<wbr ></wbr>er("@cusEm<wbr ></wbr>ail", adVarChar, adParamInput, 25, loginEmail)
set rschEmail = chEmail.execute

'#4 INSERT Statement
' Once again, we have to have everything in order, to make sure that it gets inserted correctly and without error.
Set chEmail = Server.CreateObject("ADODB<wbr ></wbr>.Command")<wbr ></wbr>
chEmail.ActiveConnection=o<wbr ></wbr>bjConn
chEmail.commandtext="INSER<wbr ></wbr>T into ordercavecustomer(cusEmail<wbr ></wbr>, password, myID)values(?,?,?)"
chEmail.Parameters.Append chEmail.CreateParamet<wbr ></wbr>er("@cusEm<wbr ></wbr>ail", adVarChar, adParamInput, 25, loginEmail)
chEmail.Parameters.Append chEmail.CreateParamet<wbr ></wbr>er("@passw<wbr ></wbr>ord", adVarChar, adParamInput, 25, loginPass)
chEmail.Parameters.Append chEmail.CreateParamet<wbr ></wbr>er("@myID"<wbr ></wbr>, adInteger, adParamInput, , getmyID)
set rschEmail = chEmail.execute

'#5 UPDATE Statement
' Same as before, in order as they are written.
' The WHERE goes last, and as you can see, it is also last in the parameters list.
Set chEmail = Server.CreateObject("ADODB<wbr ></wbr>.Command")<wbr ></wbr>
chEmail.ActiveConnection=o<wbr ></wbr>bjConn
chEmail.commandtext="updat<wbr ></wbr>e ordercavecustomer set cusEmail=?, password=? where myID=?"
chEmail.Parameters.Append chEmail.CreateParamet<wbr ></wbr>er("@cusEm<wbr ></wbr>ail", adVarChar, adParamInput, 25, loginEmail)
chEmail.Parameters.Append chEmail.CreateParamet<wbr ></wbr>er("@passw<wbr ></wbr>ord", adVarChar, adParamInput, 25, loginPass)
chEmail.Parameters.Append chEmail.CreateParamet<wbr ></wbr>er("@myID"<wbr ></wbr>, adInteger, adParamInput, , getmyID)
set rschEmail = chEmail.execute

'#6 DELETE Statement
' This example will DELETE the item with the ID of whatever it is in the Querystring (or) FORM
Set chEmail = Server.CreateObject("ADODB<wbr ></wbr>.Command")<wbr ></wbr>
chEmail.ActiveConnection=o<wbr ></wbr>bjConn
chEmail.commandtext="delet<wbr ></wbr>e from ordercavecustomer where myID=?"
chEmail.Parameters.Append chEmail.CreateParamet<wbr ></wbr>er("@myID"<wbr ></wbr>, adInteger, adParamInput, , getmyID)
set rschEmail = chEmail.execute

'#7 Display the information to the visitor with the ReverseSQL inplace.
Set chEmail = Server.CreateObject("ADODB<wbr ></wbr>.Command")<wbr ></wbr>
chEmail.ActiveConnection=o<wbr ></wbr>bjConn
chEmail.Prepared = true
chEmail.commandtext="SELEC<wbr ></wbr>T cusEmail, password, username, mydate, fname, lname FROM ordercavecustomer WHERE cusEmail =?"
chEmail.Parameters.Append chEmail.CreateParamet<wbr ></wbr>er("@cusEm<wbr ></wbr>ail", adVarChar, adParamInput, 25, loginEmail)
set rschEmail = chEmail.execute
' first we need to make sure that a record exist for the Query
if not rschEmail.eof then
' Now. We want to show our information back to our visitor, so we need to reverse what we have protected. So we wrap our recordsets with the ReverseSQL Function
strEmail = ReverseSQL(rschEmail("cusE<wbr ></wbr>mail"))
strpassword = ReverseSQL(rschEmail("pass<wbr ></wbr>word"))
strusername = ReverseSQL(rschEmail("user<wbr ></wbr>name"))
strmydate = rschEmail("mydate")
strfname = ReverseSQL(rschEmail("fnam<wbr ></wbr>e"))
strlname = ReverseSQL(rschEmail("lnam<wbr ></wbr>e"))
elseif rschEmail.eof then
response.Write"Sorry, the user does not exist in our system, Sorry! Please try again later."
end if
%>
<table>
<tr><td class="MyTD">Full Name</td><td class="MyTD"><%=strfname&"<wbr ></wbr> "&strlname%></td></tr>
<tr><td class="MyTD">Email</td><td<wbr ></wbr> class="MyTD"><%=strEmail%><wbr ></wbr></td></tr><wbr ></wbr>
<tr><td class="MyTD">Username</td><wbr ></wbr><td class="MyTD"><%=strusernam<wbr ></wbr>e%></td></<wbr ></wbr>tr>
<tr><td class="MyTD">Password</td><wbr ></wbr><td class="MyTD"><%=strpasswor<wbr ></wbr>d%></td></<wbr ></wbr>tr>
<tr><td class="MyTD">Date Joined</td><td class="MyTD"><%=strmydate%<wbr ></wbr>></td></tr<wbr ></wbr>>
</table>

Open in new window

Conclusion

All the codes that I show people how to do here and on other threads throughout EE, is what I use in my real-world applications, I WILL NOT give someone code that I would not trust and use myself on my own sites.

CFF Coding Source

Have Fun & Happy Programming

Carrzkiss

Additional Resources

(please view the following code tutorial examples that I have written for EE) 

#1: Shows basically what we are doing here in this article.

SELECT, INSERT, UPDATE, DELETE w/SQL & XSS Injection Prevention

Marked up with all the information that is here plus a LOT more.

http://ee.cffcs.com/Q_24801116/Q_24801116.asp

code

http://ee.cffcs.com/Q_24801116/Q_24801116.zip

#2: Protect your members online with a cookie that is also protected from SQL and XSS Injections.

http://ee.cffcs.com/Q_24252782/login.asp

http://ee.cffcs.com/Q_24252782/Q_24252782.zip

#3: Download without the physical path being known

http://ee.cffcs.com/Q_26208870/Q_26208870.asp

(Click on the =>Download your file here<=)

Updated Article 08-03-2012.

From this

chEmail.Parameters.Append getMyProfile.CreateParameter

To this

chEmail.Parameters.Append chEmail.CreateParameter

This is a HUGE Mistake on my part, and I wish to apologize to everyone that has come in here and learned from a mistake that I made.

Thank you to "rrhandle8" for pointing this out to me.