Parameters in SQL query on ASP/VBScript
-
I've been given the task of fixing potential (and exploited) SQL-injection errors in an existing ASP (not ASP.NET) project. I can get the following code to execute (no parameters, no concatenation):
connString = "Driver={SQL Native Client};Server=server;Database=ACCT;Trusted_Connection=yes;" Set objConn = Server.CreateObject("ADODB.Connection") objConn.Open connString query = "SELECT SystemCode, SystemName FROM tblSystem WHERE Year = 5" Set cmd = Server.CreateObject("ADODB.Command") cmd.CommandText = query cmd.ActiveConnection = objConn Set rs = cmd.ExecuteHowever, when I try to convert it to use a parameter, I get an error on the cmd.CreateParameter line of "ADODB.Command (0x800A0BB9) Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."query = "SELECT SystemCode, SystemName FROM tblSystem WHERE Year = @YEAR" Set cmd = Server.CreateObject("ADODB.Command") cmd.CommandText = query cmd.Parameters.Append cmd.CreateParameter("@YEAR", adInteger, adParamInput, ,5) cmd.ActiveConnection = objConn Set rs = cmd.ExecuteAny idea how I can make this work, preferably quickly and easily? Thanks. --G -
I've been given the task of fixing potential (and exploited) SQL-injection errors in an existing ASP (not ASP.NET) project. I can get the following code to execute (no parameters, no concatenation):
connString = "Driver={SQL Native Client};Server=server;Database=ACCT;Trusted_Connection=yes;" Set objConn = Server.CreateObject("ADODB.Connection") objConn.Open connString query = "SELECT SystemCode, SystemName FROM tblSystem WHERE Year = 5" Set cmd = Server.CreateObject("ADODB.Command") cmd.CommandText = query cmd.ActiveConnection = objConn Set rs = cmd.ExecuteHowever, when I try to convert it to use a parameter, I get an error on the cmd.CreateParameter line of "ADODB.Command (0x800A0BB9) Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."query = "SELECT SystemCode, SystemName FROM tblSystem WHERE Year = @YEAR" Set cmd = Server.CreateObject("ADODB.Command") cmd.CommandText = query cmd.Parameters.Append cmd.CreateParameter("@YEAR", adInteger, adParamInput, ,5) cmd.ActiveConnection = objConn Set rs = cmd.ExecuteAny idea how I can make this work, preferably quickly and easily? Thanks. --GHi Glen What data-type is the Year column? If it is "smallint" then you should use adSmallint. Regards Andy
-
I've been given the task of fixing potential (and exploited) SQL-injection errors in an existing ASP (not ASP.NET) project. I can get the following code to execute (no parameters, no concatenation):
connString = "Driver={SQL Native Client};Server=server;Database=ACCT;Trusted_Connection=yes;" Set objConn = Server.CreateObject("ADODB.Connection") objConn.Open connString query = "SELECT SystemCode, SystemName FROM tblSystem WHERE Year = 5" Set cmd = Server.CreateObject("ADODB.Command") cmd.CommandText = query cmd.ActiveConnection = objConn Set rs = cmd.ExecuteHowever, when I try to convert it to use a parameter, I get an error on the cmd.CreateParameter line of "ADODB.Command (0x800A0BB9) Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."query = "SELECT SystemCode, SystemName FROM tblSystem WHERE Year = @YEAR" Set cmd = Server.CreateObject("ADODB.Command") cmd.CommandText = query cmd.Parameters.Append cmd.CreateParameter("@YEAR", adInteger, adParamInput, ,5) cmd.ActiveConnection = objConn Set rs = cmd.ExecuteAny idea how I can make this work, preferably quickly and easily? Thanks. --GI think the type is wrong on your parameter, you should be using dbtype.int or SQLdbType.int32
Grady Booch: I told Google to their face...what you need is some serious adult supervision. (2007 Turing lecture) http://www.frankkerrigan.com/[^]