Tag Archives: query

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.

Binding ColdFusion query to DataGrid in Flex

In this post I’ll explain you how to bind ColdFusion data into Flex grid.
First, get ready with your cfc.
Your cfc function may look like:

<cffunction name="getCategoryList" access="remote" output="false" returntype="query">
<cfset var qCategoryList = "" />
	<cfquery name="qCategoryList" datasource="#application.dsn#" username="#application.dbuid#" password="#application.dbpwd#">
		FROM	tblCategory
	<cfreturn qCategoryList />

Please note here that we are returning a ColdFusion query.

Now I’m setting up mxml page to show the grid and fetch the ColdFusion data with RemoteObject.

<?xml version="1.0" encoding="utf-8"?>
<s:Panel xmlns:fx="http://ns.adobe.com/mxml/2009" 
		<s:RemoteObject destination="ColdFusion" source="cfc.categoryGateway" id="categoryGateway">
			<mx:method name="getAllForGrid" result="returnHandler(event)"
			import mx.controls.Alert;
			import mx.rpc.events.ResultEvent;
			private function returnHandler(e:ResultEvent):void 
				dgCategory.dataProvider = e.result;
		<mx:DataGrid id="dgCategory">
				<mx:DataGridColumn headerText="Category ID" dataField="categoryId"/>
				<mx:DataGridColumn headerText="Category Name" dataField="categoryName"/>
				<mx:DataGridColumn headerText="Description" dataField="Description"/>

Easy enough?

Now let me point out important notes:

Remember one thing that ActionScript 2.0 and 3.0 are now case sensitive!

So if you write “description” instead of “Description” in DataGridColumn, it wouldn’t bind!

Also If your cfc method returns a ColdFusion structure, then you can get the value of structure elements by typing element name in capital later.

For e.g.

Your cfc method:

<CFSET var myStruct = structNew()>
<CFSET myStruct.query = qGetAllRecord>
<CFSET myStruct.RowCount = qGetAllRecord.recordCount>
<CFRETURN myStruct>

And your ActionScript code would be:

dgCategory.dataProvider = e.result.QUERY;