CF Admin SQL injection flaw

Nathan Mische has blogged about a surprising ‘flaw’ in the ColdFusion Administrator that’s supposed to allow you to disable SQL commands coming from CF such as delete, drop, alter, update, etc. Apparently this is ineffective against multi-line SQL injection attacks – the most common sort of injection.

Perhaps ‘flaw’ is the wrong word, but these settings do lead you to believe that any sql with delete, drop, etc in it would be prevented from being sent to the database.

The bottom line is to always use cfqueryparam or stored procedures and sanitise user input from forms and urls. Don’t trust users – assume they’re all up to no good! ;-)

Optimal SQL to page through large record set

This is a tip for web application developers who want to page through results coming back from a MS SQL database. If there are 100 or 500 records coming back and you just want to show 25 at a time to the user then you can achieve that through your application code (CFML, PHP, etc). But what if there are 1000’s of records, it’s deeply inefficient to request that many records from your database if you only want to display 25 every time the user requests the next page, or jumps forwards several pages. If you want the results to be ordered by a user-defined column and not by the Row ID then the solution may not be obvious.

MySQL can use limit(start,count) but MS SQL doesn’t support that. Someone suggested using a memory table, someone else suggested a cursor. A bit of Googling revealed MS SQL 2005 onwards has a new function called row_number(). Here’s how it works in this example:

SELECT * FROM (
 SELECT row_number() over (
  order by users.surname, users.firstname
 ) AS rowNumber, users.id, users.firstname, users.surname
 FROM users
) table1
WHERE table1.rowNumber BETWEEN 150 AND 175

It will only return records 150 to 175 and importantly it will order it by surname then firstname. And it’s very, very fast.