Classic ASP SQL injection prevention by using query parameter

Hello friends,

Recently I got a client email that his site is affected by malware and Trojan virus. Google safe browsing tool and other firewall security gateway was blocking some of the site URLs.

Site was written in classic asp long ago by other company. When I look into the file contents, no files were modified. Normally if hackers get the ftp details then they modify files and add some iframe code that will load other virus affected sites. But this wasn’t case here. So there may be a case of SQL injection. When I looked into database tables, I found some of the fields with having some html code. That html code was loading other sites which are virus infected. Our site is not allowing anyone to add/edit records, however records were modified. This was done by SQL injection using query string parameter.

I looked into some of the pages, and I found page that lists records and records were filtered by some condition and by query string parameter.

In classic asp, most of the developers write an SQL query like following:

sql = " SELECT * FROM table WHERE id = " & Request("id") 

Executing such query easily opens a way for SQL injection. We must need to validate the variables used in SQL query.

Fortunately, we can do it by adding parameters in SQL query. Basically we need to create ADODB command object. A detailed example is show bellow:

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Your connection string"

set cmd = server.createobject("ADODB.Command")
sql = " SELECT * FROM table WHERE id = ? or name like ?"

cmd.ActiveConnection = conn
cmd.CommandText = qText
cmd.CommandType = adCmdText
cmd.CommandTimeout = 900
cmd.Parameters.Append cmd.CreateParameter("@id", adInteger, adParamInput, ,request("id"))
cmd.Parameters.Append cmd.CreateParameter("@name", adVarchar, adParamInput, 50, "%" & request("name") & "%")

set rs = cmd.Execute

Set rs = Nothing

Set conn = Nothing

Also note that, when you add or append a parameter, name is not much important. Parameter position is the important thing. First ? mark will be replaced by first parameter and second ? mark with second parameter.

Please note that here we have used some VB constants. You need to declare them somewhere. You can include this asp file on the top of your page.

2 thoughts on “Classic ASP SQL injection prevention by using query parameter

  1. Dennis

    Does one have to use ? instead of putting @id into the SQL code? I dislike using ? as it makes the code harder to interpret. However when I do it like that my command query returns no results, ? works however. Annoying…


Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s