Securing You ASP Scripts for SQL Injection

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:

[code:vb]sql = " SELECT * FROM table WHERE id = " & Request("id") [/code]

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 below:

[code:vb]<%
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
%>
[/code]

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.