Prevent SQL Injection in ColdFusion/Using AntiSamy & amp; n

Reprint sun GE's blog --

 

I recently read some ColdFusion Security Information and accidentally read this article on the Internet. I did not expect ColdFusion to prevent SQL injection! It is much more convenient than ASP/PHP. I thought it was easy to use PreparedStatement in Java/JSP to pre-compile and prevent SQL injection. I didn't expect ColdFusion to be more direct! Haha !~ Reached!

ColdFusion security information in China is rare, so you can only view the bird edition written by foreigners!

I was just on a web site (no, not a ColdFusion powered site, and no I will not name names) browsing for specific content. the URLs used typical name = value query string conventions, and so I changed the value to jump to the page I wanted. and I made a typo and added a character to the numeric value. the result? An invalid SQL error message.

Thats bad. very very bad. it means that I was able to create a SQL statement that was submitted to the database for processing, a SQL statement that was passed to the database as is, unchecked.

Youd think that by now wed have learned to lock down our code so as to prevent SQL injection attacks, but apparently this is not the case. You do not know what a SQL injection attack is? Well, read on.

Consider the following simple dynamic ColdFusion query:

SELECT *
FROM MERs
WHERE CustID = # URL. custid #

Here a WHERE clause is being populated dynamically using a URL parameter. This type of code is common and popular, and is often used in data drill-down interfaces. If the URL was:

Http: // domain/path/file. cfm? Custid = 100

The resulting SQL statement wocould be:

SELECT *
FROM MERs
WHERE CustID = 100

But what if someone tampered with that URL so that it read:

Http: // domain/path/file. cfm? Custid = 100; DELETE + MERs

Now the resulting SQL wocould be:

SELECT *
FROM MERs
WHERE CustID = 100;
DELETE MERs

And depending on the DBMS being used, you cocould end up executing two statements-first the SELECT, and then DELETE MERs (which wowould promptly delete all data from the Customers table ).

Scared? You shoshould be. SQL statements are not just used for queries. they are also used by most DBMSs to create and drop tables, create user logins, change passwords, set security levels, manage scheduled events, even creating and dropping entire databases. and whatever features are supported by your DBMS may be accessible this way.

Before I go further I must point out that this is not a ColdFusion vulnerability at all. in fact, it is not even a bug or a hole. this is truly a feature-writable DBMS do indeed allow queries to contain more than a single operation, this is legal and by design.

Of course, you shoshould always be checking parameters anyway before passing them to your DBMS. passing client supplied data (URL parameters, FORM fields, and even cookies) through unchecked is programmatic suicide. attacks aside, it is flat out unsafe to ever assume that data submitted by a client can be used as is.

As such, you shoshould already be using code like this:

<Cfparam name ="URL. CustID" Type ="Integer">

This single line of code will lock SQL injection attacks out. How? Think about it, SQL injection (within ColdFusion apps) is really only an issue with non textual fields. if a text value is tampered with youll end up with tampered text, but that text will all be part of the core string (within quotes) passed as a value, and will therefore not be executed as separate statements. numbers, on the other hand, are not enclosed within quotes, and so extraneous text can be tampered with to create an additional SQL statement. and <cfparam> can protect you.

Of course, you may want more control, in which case you cocould use code like this:

<Cfif IsDefined ("URL. CustID")
And not IsNumeric (URL. CustID)>
... Throw an error or something...
</Cfif>

And as an additional line of defense you can use <cfqueryparam>, as seen here:

<Cfquery...>
SELECT *
FROM MERs
WHERE CustID =<Cfqueryparam value ="# URL. CustID #" Cfsqltype ="CF_ SQL _INTEGER">
</Cfquery>

If the previous tampered URL was passed to the this query, the value wocould be rejected and an error wocould be thrown. the CFSQLTYPE (aside from binding variables) performs data type validation checks, and values that do not match the type are rejected. thats it, only integers allowed, and malicious tampered URL parameters are not integers.

The bottom line is that SQL injection attacks have been around for as long as dynamic SQL itself. coldFusion has made it incredibly easy to protect yourself against such attacks. be it <cfparam> or <cfqueryparam> or your own conditional processing, its simple to protect yourself, and your responsibility to do so.

If you have not been paying attention to this risk, stop whatever you are doing, fire up your IDE, and do a search for every single <cfquery> in your code. then quickly scan to find any that contain # s in them (that are not enclosed in quotes or passed to <cfqueryparam>), and make a list of the variables used. if any of them are URL parameters or FORM fields, create a <cfparam> for each (at the top Of the page, or before the <cfquery>). Its that simple. Really. There is no legitimate reason not to protect yourself, so just do it. Now! And I mean right now, before you leave for the day or take off for the holidays, and despite whatever project you are working on or deadline you