How to update using ADODB

jmhemadri · September 9th, 2008, 06:31 AM

Hi All

Generally we are using the following code to update using ADODB

sql = "SELECT * FROM customers WHERE CustomerID=" + custID;
recordSet.Open(sql, connection, 1, 2);
    recordSet("FirstName") = firstName;
    recordSet("MiddleInitial") = middleInitial;
recordSet.Update();
recordSet.Close();
But, we are facing sql injection problem. so what we can't pass value directly to query. So I have changed to
var sqlcmd = Server.CreateObject("ADODB.Command");
                        sqlcmd.CommandText = sql;
                        sqlcmd.CommandType = 1;
                        sqlcmd.Parameters.Append(sqlcmd.CreateParameter("@ column1",200,1 ,10,custID));

My Doubt is, How to update sqlcmd using recordset. can you please explain?

Old Pedant · September 9th, 2008, 06:07 PM

First of all, to fix your SQL Injection problems with your original code, you could just do this:

Code:

<%
...
custid = parseInt(custid); // ensure it really *IS* an integer
if ( isNaN(custid) )
{
    Response.Redirect( "badCustomerId.html" ); // or wherever
    Response.End( );
}
sql = "SELECT * FROM customers WHERE CustomerID=" + custid;
...
%>

***********************

But to do it the other way:

Code:

<%
...
var sql = "UPDATE customers SET FirstName=@fname, LastName=@lname WHERE CustomerId=@custid"
var sqlcmd =  Server.CreateObject("ADODB.Command");
sqlcmd.CommandText = sql;
sqlcmd.CommandType = 1;
sqlcmd.Parameters.Append(sqlcmd.CreateParameter("@fname",200,1 ,100,firstName));
sqlcmd.Parameters.Append(sqlcmd.CreateParameter("@lname",200,1 ,100,lastName));
sqlcmd.Parameters.Append(sqlcmd.CreateParameter("@custid",3,1,,custID));
sqlcmd.Execute();
...
%>

That's if you are using SQL Server. If you are using Access, I *believe* you would need to use

var sql = "UPDATE customers SET FirstName=?, LastName=? WHERE CustomerId=?"

***************************

FINALLY...

You *COULD* do it the simplest way of all, you know:

Code:

<%
...
var sql = "UPDATE customers SET "
        + " FirstName='" + firstName.replace(/\'/g,"''") + "',"
        + " LastName='" + lastName.replace(/\'/g,"''") + "' "
        + " WHERE CustomerId=" + parseInt(custID);
connection.Execute( sql );
...
%>