Using Parameters in Classic ASP/ADO

Posted on

It seems like there’s really a lack of information about using parameters in classic ASP on the Internet. There are a few really big reasons why someone would want to use them namely, it allows you to insert an apostrophe into a database without using the old double-up-your-apostrophies trick (although that one is very effective). Another reason to use parameters, especially when inserting dynamic data into a database (such as user input) is to prevent the dreaded INJECTION ATTACK . This is probably more important than the first reason.

So how do you do it? Here’s an example. I’ll explain it in the comments so read carefully.

<code>
‘In this example we will be adding ‘two fields to a database, a username
‘and a password. Obviously this is
‘perfectly hypothetical.

‘Start by declaring your variables
Dim conn, cmd ‘Database connection and command objects
Dim paramUser, paramPass ‘What we’ll use to store the parameters

‘Create an ADO Connection object
Set conn = Server.CreateObject(” ADODB .Connection”)

‘Hook up the connection object to a database and open it up. conn.open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\inetpub\wwwroot\Databases\
database.mdb;“”,“”,“””

‘Here’s where it starts to get cool
‘We’ll need to create an ado Command ‘object and set its CommandText ‘property to the sql statement you ‘want to execute. ‘What we usually would do is insert ‘our strings right into the sql ‘string, bad move if you don’t want an ‘injection attack. ie. “insert into
‘example_table (username, ‘auth_password) ‘values (strUserName,strPassword)”
‘Instead, we’ll insert some variables
‘using the @ symbol to give them nice
‘little names

Set cmd = Server.CreateObject(” ADODB .Command”) cmd.CommandText = “insert into auth (auth_username, auth_password) values (@ohMyUser,@ohMyPass)”

‘Now this part seems like a little ‘more work, but it’s really worth it.
‘Create two parameters and set their ‘properties
‘The string of parameters after the ‘createparameter method is important. ‘here’s the format:
‘name,type,direction,size,value
‘insert the name of the variable -
‘the @name without the @sign.
‘then the type of data it is
‘next the direction of the parameter,
‘in this case its going into the db
‘now the size of the field
‘and lastly the data to input

Set paramUser = cmd.CreateParameter(“ohMyUser”,adVarChar,adParamInput,len(strUserName),strUserName)

‘Now you have to add this new ‘parameter to the parameters ‘collection. cmd.Parameters.Append paramUser

‘Now do it again for the second ‘parameter, pay attention to the ‘differences
Set paramPass = cmd.CreateParameter(“ohMyPass”,adVarChar,adParamInput,len(strPassword),strPassword)
cmd.Parameters.Append paramPass

‘Finally, set the active connection to ‘the connection object we created ‘earlier and execute the command.
Set cmd.ActiveConnection = conn
cmd.Execute

‘Always clean up (although this ‘practice may be questionable)
Set cmd = Nothing
Set conn = Nothing
conn.Close

</code>

That’s it. It’s a bit more work but it sure pays off! One of the best resources for this is the w3schools ado page.

Email me if you have any problems. I can’t wait to get comments up on the website.