A CFM to cleanse database records attacked by SQL Injection

This is nothing too fancy, but may help anyone who has to deal with this issue. Recently a client’s [very old] website was compromised by a sql injection attack. The infected records all had a script appended to their TEXT field, “body”. Instead of cleaning these manually as I first thought I would, I came up with this simple CF page.

<cfquery name="read" dataSource="#session.datasource#">
SELECT id, body
FROM news_pr
WHERE (body LIKE '<script>')
</cfquery>

<cfoutput query="read">
	#id#:
	#findNoCase("<script>", body)#:
	<cfset newString = left(body,findNoCase("<script>", body)-1)>
	#left(newString,50)#...#right(newString,100)#
	<br><br>

	<cfset form.body = newString>
	<cfset form.id = read.id>
	<cfupdate tableName="news_pr" dataSource="#session.datasource#">

	<cfflush>
</cfoutput>

Quick and dirty, I know. But it worked.

Of course, this only works if the injected material is at the end of the text field.

Random SQL Server results – a one word command

I learned a great way to randomize SQL results.

In the past, I would have followed this routine:

Run a query pulling all of the results (tens? hundreds? thousands?).
Create an array.
Loop until 5 good random results are made:
Get 1 random query result.
If it’s not in the array, put it in.
If it is in the array, ignore it and try again.
Then I’d output the data from the array, rather than the query.

I suppose I could simply fill an array then randomize it. But that’s not really the point.

As you see, with this version we have to pull back so many more results than we’re actually going to use. That’s wasteful – especially if you want to pull back lots of data in each row.

So what’s this great super duper new way I’ve discovered to simplify code, only pull back the 5 results from SQL Server, and run fast? It’s really simple: tell SQL Server to do the work for you, with one word. No post parsing needed!

Just order the results by NewID(). Yeah, that’s it.

SELECT TOP 5
	id, name, description
FROM
	companies
WHERE
	active=1
ORDER BY
	NewId()

This query will only return 5 random results – even if there are 1000 companies that are active – and in random order.  So you can simply output your query like normal with your server language. Sweet.

Pagination with ColdFusion and MSSQL (faux MySQL’s LIMIT X,Y)

Pagination is something that has always been somewhat difficult for
me. Especially optimizing it for large sites. To make it even harder,
the customer wants the first page to show three items, and every
subsequent page to show ten. Not hard enough? OK, they want page number
links instead of simple left/right arrow links, such as:

1 2 3 [4] Next »

Still Easy?

  • Only show 4 page links at a time
  • When you click ‘next’, show the next block of 4 pages
  • When you click ‘prev’, show the previous block of 4 pages
  • If you are on the first block of links, do not show the ‘prev’ link
  • If you run out of records, only show the correct number of page links in that block
  • If you run out of records, do not show the ‘next’ link

Remember, page 1 has 3 items. Page 2 has 10. So the math gets kinda
tricky when you don’t want to do ‘what page are we on’ statements all
over the place.

Here is my solution. All you have to do is enter your records per
page (“allperpage”), number of page links at a time (“blocksof”), and
the number of items on the first page (“firstperpage”).

The SQL query has been optimized to get as close to MySQL’s ‘LIMIT’
function as possible. We’re doing an INNER JOIN here, and sorting on
columns from both tables. I haven’t seen any examples of this
optimization technique with an inner join, or even sorting on more than
one column. We pull back the max rows we’d need, then trim the top with
a reverse sort, finally un-reverse the sort for the web page. WHEW.

Onto the code…

In this example, we’re going to show page blocks of 4. The first page has 3 items. Every other page has 6. The example uses articles which are in publications.
It looks like a monster. But half of it is comments.

<!--- CURRENT PAGE --->
<cfparam name="url.page" default="1">
<!--- RECORDS PER PAGE (ON ALL PAGES) --->
<cfparam name="variables.allperpage" default="6">
<!--- RECORDS PER PAGE (ON FIRST PAGE) --->
<cfparam name="variables.firstperpage" default="3">
<!--- NUMBER OF PAGE LINKS AT A TIME --->
<cfparam name="variables.blocksof" default="4">

<!--- COUNT NUMBER OF RECORDS TOTAL --->
<cfquery name="articlecount" datasource="MagLibrary">
SELECT count(articles.articleid) as articlecount
FROM articles INNER JOIN publications ON articles.publicationID = publications.publicationID
WHERE articles.active = 1
</cfquery>

<!--- RETURN ONLY THE NUMBER OF RECORDS WE NEED --->
<!--- THIS IS AS CLOSE TO MYSQL'S LIMIT X,Y AS WE'RE GONNA GET --->
<cfquery name="latest" datasource="#session.datasource#">
<!--- PAGE 1 IS SIMPLE. SUBSEQUENT PAGES GET COMPLICTED SINCE THE FIRST PAGE HAS A DIFFERENT NUMBER OF RECORDS --->
<!--- THIRD, ORDER OUR RESULTS --->
SELECT *
FROM (
	<!--- SECOND, ONLY RETURN THE AMOUNT OF RECORDS WE NEED FOR THIS PAGE --->
	SELECT TOP
	<!--- THIS STEP ENSURES THE LAST PAGE HAS THE CORRECT AMOUNT OF RECORDS.
		IGNOREING THIS CHECK ALWAYS GIVES THE LAST PAGE THE #PERPAGE# RECORDS
		SO YOU'D END UP WITH SOME OF THE PREVIOS PAGE'S RECORDS --->
	<cfif articlecount.articlecount lt (firstperpage + (allperpage * (url.page-1)))>
		#articlecount.articlecount - (firstperpage + (allperpage * (url.page-2)))#
	<Cfelse>
		<cfif url.page is 1>
			#firstperpage#
		<cfelse>
			#allperpage#
		</cfif>
	</cfif> *
	FROM (
		<!--- FIRST, GET UP TO THE AMOUNT WE NEED WITH CORRECT SORTING --->
		SELECT TOP #firstperpage + (allperpage * (url.page-1))#
			articles.title, articles.articleid, publications.title as pubtitle, publications.rank
		FROM articles INNER JOIN publications ON articles.publicationID = publications.publicationID
		WHERE articles.active = 1
		<!--- OUR PREFERED ORDERING --->
		ORDER BY publications.rank, pubtitle, articles.title, articles.articleid
	) as t1
	<!--- REVERSE THE ORDERING SINCE WE'RE SELECTING THE TOP --->
	ORDER BY rank DESC, pubtitle DESC, title DESC, articleid DESC
) as t2
<!--- AND REORDER FOR DISPLAYING --->
ORDER BY rank, pubtitle, title, articleid
</cfquery>

<!--- 'TOP' IS THE LAST PAGE NUMBER LINK --->
<Cfset top = blocksof * ceiling((url.page+1)/blocksof)>

<cfoutput>

<!--- IF WE'RE PAST THE FIRST BLOCK OF PAGES, SHOW THE PREV LINK --->
<cfif url.page gt blocksof>
	<a href="?page=#top-blocksof#" class="fullstorylink" style="text-decoration:underline;">« Prev</a>
</cfif>

<!--- LOOP THROUGH THE PAGES IN THIS BLOCK --->
<cfloop from="#top-blocksof+1#" to="#top#" index="a">
<cfif url.page is not a>
	<a href="?page=#a#" class="fullstorylink" style="text-decoration:underline;">#a#</a>
<Cfelse>
	<b>[#a#]</b>
</cfif>
<!--- IF WE'RE ON THE LAST BLOCK AND DON'T HAVE ENOUGH RECORDS TO COMPLETE THE BLOCK,
	STOP CREATING THE LINKS FOR MORE PAGES, AND SET A FLAG TO NOT SHOW THE LINK FOR THE NEXT BLOCK --->
<cfif ((a-1) * allperpage) + firstperpage gte articlecount.articlecount>
	<Cfset noNext=1><cfbreak>
</cfif>
</cfloop>

<!--- IF WE RAN OUT OF RECORDS, DO NOT SHOW THE LINK FOR THE NEXT BLOCK OF PAGES --->
<cfif not isdefined("noNext")>
	<a href="?page=#top+1#" class="fullstorylink" style="text-decoration:underline;">Next »</a>
</cfif>

</cfoutput>

<!--- NOW IT'S A SIMPLE MATTER OF SHOWING THE RECORDS --->
<br><br>

<!--- WE'LL GROUP THE ARTICLES UNDER THEIR PUBLICATION--->
<cfoutput query="latest" group="pubtitle">
<h2>#pubtitle#</h2>
<cfoutput>
<a href="details.cfm?articleid=#articleid#">#title#</a>
</cfoutput>
</cfoutput>

Comments, suggestions, improvements are gladly welcomed.