Classic Asp Secure Insert Statement Form to Database

Question

I have an online form, data from there needs to be entered into a MSSQL database. I'm trying to create a secure insert statement. With the following code I get this error.

Microsoft OLE DB Provider for SQL Server error '80040e5d' Parameter name is unrecognized.

<%@language="vbscript" codepage="65001" %>
<% option explicit %>

Dim Form_Name
Dim Form_Email
Form_Name= ProtectSQL(request.Form("Name"))
Form_Email= ProtectSQL(Request.Form("Email"))

Dim objConn
set objConn = Server.CreateObject("ADODB.Connection")

Dim strConn
strConn="provider=SQLOLEDB;Server=localhost;Database=dbname;Uid=username;Pwd=password;"

objConn.open strConn

Dim DateSubmitted
DateSubmitted=now()

Dim strSQL
strSQL = "INSERT INTO tablename(DateSubmitted, Name, Email) VALUES('" & DateSubmitted & "', ?, ?)"

Dim objCmd 
set objCmd = Server.Createobject("ADODB.Command")
objCmd.ActiveConnection = objConn 
objCmd.CommandText = strSQL 
objCmd.CommandType = adCmdText
objCmd.NamedParameters = true

Dim objParam1
Set objParam1 = objCmd.CreateParameter("Name", adVarChar, adParamInput, Len(Form_Name), Form_Name)
objCmd.Parameters.Append objParam1

Dim objparam2
Set objparam2 = objCmd.CreateParameter("Email", adVarChar, adParamInput, Len(Form_Email), Form_Email)
objCmd.Parameters.Append objparam2

objCmd.Execute, , adCmdText And adExecuteNoRecords

objConn.close
Set objConn = Nothing

I have also tried

strSQL = "INSERT into tablename(DateSubmitted,Name,Email)values('" & DateSubmitted & "',@Name,@Email)"

objCmd.Parameters.Append objCmd.CreateParameter("@Name",adVarChar,adParamInput,100,Form_Name)

objCmd.Parameters.Append objCmd.CreateParameter("@Email",adVarChar,adParamInput,100,Form_Email)

With this I get Must declare the scalar variable "@Name".

Both error messages reference the objCmd.Execute line

Is there a better way to do an insert statement? I don't need a recordset with this.

Answer 1

You have 5 placeholders in

strSQL = "INSERT INTO tablename(DateSubmitted, Name, Email) VALUES('" & DateSubmitted & "', ?, ?, ?, ?, ?)"

but add only 2 parameters.

Answer 2

I use code like the following code to perform parameterized queries in classic asp:

public sub sql_execute(sql, parameterArray)

    dim cnx
    Set cnx=CreateObject("ADODB.Connection")
    cnx.Open wfDataConnection           
    if isArray(parameterArray) then
        dim cmd, i
        Set cmd = CreateObject("ADODB.Command")
        With cmd    
            .CommandText = sql
            .CommandType = adCmdText
            .ActiveConnection = cnx 
            for each i in parameterArray
                .Parameters.Append .CreateParameter(i(0), i(1), i(2), i(3), i(4))
            next
        end with        
        cmd.execute rowsAffected, , adExecuteNoRecords
    else
        cnx.execute sql, rowsAffected, adExecuteNoRecords       
    end if
end sub

Calling it like so:

dim sql, parameterArray
sql = "INSERT INTO table (val1, val2) VALUES (?, ?)"
paramaterArray = Array(_
                     Array("@p1", adInteger, adParamInput, , val1)_
                    , Array("@p2", adVarChar, adParamInput, 255, val2)_
                 )

sql_execute sql, parameterArray

I'm not too sure about the variable names (@p1, @p2, etc) when creating parameters. It doesn't seem to matter what you call the variables, but they do require some kind of name in order for it to work.