How to fix Provider error '80020005' Type Mismatch

Question

For my assignment, I was given a website which I need to make secure against SQL injection. I am attempting to do so using parameters, however I am getting an error.

The original code (below) works just fine:

function logInUser(name,pwd) {
    var DBConn = getDBConnection();
    var SQL    = "SELECT * FROM Users WHERE UserName = '" + name + "' and UserPwd = '" + pwd + "'";
    var RS     = DBConn.Execute(SQL);
    var valid  = !RS.Eof;
    if (valid) {
       Session("UserID")   = RS("UserID").value;
       Session("UserName") = RS("UserName").value;
       Session("UserFullName") = RS("UserFirstName").value + ' ' + RS("UserLastName").value;
    }
    DBConn.Close;
    return valid;
  }

I have attempted to modify it in this way: function logInUser(name,pwd) { var DBConn = getDBConnection();

    var uName = name;
    var uPwd = pwd;
 
    var SQL    = "SELECT * FROM Users WHERE UserName = @0 and UserPwd = @1";
 
    var RS     = DBConn.Execute(SQL,uName,uPwd);
    var valid  = !RS.Eof;
    if (valid) {
       Session("UserID")   = RS("UserID").value;
       Session("UserName") = RS("UserName").value;
      Session("UserFullName") = RS("UserFirstName").value + ' ' + RS("UserLastName").value;
    }

When I do so I get:

Provider error '80020005' Type Mismatch.

I have also tried modifying the statements to take one parameter, but then I get Engine error '80040e10' No value given for one or more required parameters.

Answer 1

EDITED:::

var oCmd = Server.CreateObject("ADODB.Command")
var SQL = "SELECT * FROM Users WHERE UserName = ? and UserPwd = ?";
oCmd.CommandText = SQL
oCmd.ActiveConnection= DBconn
var oPar = oCmd.CreateParameter("UserName ", uName);
oCmd.Parameters.Append(oPar);
var oPar2 = oCmd.CreateParameter("UserPwd", uPwd);
oCmd.Parameters.Append(oPar2);

var RS =oCmd.Execute()

ORIGINAL::: Ok, I'll admit I don't know the database connection model you are looking for but I will bet you need to put your uName and uPwd into an object:

var credentials = {
  0: uName,
  1: uPwd
}

Maybe even:

var credentials = {
  '@0': uName,
  '@1': uPwd
}

possibly an array

var credentials = [uName, uPwd]

then:

var RS = DBConn.Execute(SQL,credentials);