Here is something I discovered the other day that surprised me a bit. ColdFusion data sources have an advanced option named "Allowed SQL" which, according to the documentation, defines "the SQL operations that can interact with the current data source." I know some shops use this setting to help protect against SQL injection attacks. For example they may limit a data source to only allow SELECT and Stored Procedures. While you may think that this would go a long way toward protecting the data source against SQL injection, this may not be the case. If the database credentials used for the data source have additional permissions these statements may be executed via SQL injection. For example, consider the following setup:

• A SQL Server database: TESTDB
• A table TESTDB.TestTable
• A database user test_user with SELECT, INSERT, UPDATE, and DELETE permissions to TestTable
• A ColdFusion data source "test" connected to TESTDB using the test_user credentials and the Allowed SQL option set to only SELECT and Stored Procedures.

Now, consider the following query.

You may think the above query would be protected against SQL injection because the data source limits the Allowed SQL to SELECT and Stored Procedures, but you would be wrong. Given this setup, the above this code is actually susceptible to SQL injection. Assuming this query were in a template index.cfm, you could easily delete all records in TestTable by issuing a request like index.cfm?testID=1%20DELETE%20FROM%20TestTable.

Now there are a number of ways you could protect against this attack, one of the easiest being the cfqueryparam tag, another being to limit the database user's permissions, but that isn't the point of this post. The point is you can't rely on the Allowed SQL advanced option to protect against SQL injection. You have been warned. (Note: I have only tested this against MS SQL Server so it may not apply to all database engines.)

(This is a re-post from my work blog, but I thought I'd post it here for everyone else.)

ANT has a SQL task which allows you to execute SQL statements via JDBC which can be very handy in a number of projects. Currently I'm using this task to execute a directory of generated sql files, each of which defines a stored procedure for MS SQL Server. Here is what a typical one of these stored procdure SQL files looks like:

[More]

The new beta for Firebug was released over the weekend so I'm happy to say I'm releasing ColdFire 1.2 into the wild. Head over to the RIA Forge site to check it out.

[More]

So here is something I ran into recently. Take a look at the following queries:

<cfquery name="insertNow1" datasource="scratch">

    INSERT INTO TestTable

    (

        TestText

        ,TestDateTime

    )

    VALUES

    (

        'Test without queryparam'

        ,#Now()#

    )

</cfquery>



<cfquery name="insertNow2" datasource="scratch">

    INSERT INTO TestTable

    (     

        TestText

        ,TestDateTime

    )

    VALUES

    (

        <cfqueryparam value="Test with queryparam" cfsqltype="cf_sql_varchar">

        ,<cfqueryparam value="#Now()#" cfsqltype="cf_sql_timestamp">

    )

</cfquery>

You may expect the above two queries to give you the same results, but they don't. If you don't use cfqueryparam the datetime value is only stored to the second precision.

ID     TestText                           TestDateTime                                           
------ ---------------------------------- ----------------------------
1      Test without queryparam            2008-02-12 15:31:51.000
2      Test with queryparam               2008-02-12 15:31:51.717

This is because the string representation of a coldfusion.runtime.OleDateTime object is only precise to the second. (Try dumping Now() and you will see you get a time stamp similar to {ts '2008-02-12 15:31:51'}. Notice no milliseconds.) This is just something to keep in mind, especially if your application depends on timestamps.

I ran into something today that I've never had to do before: I needed to select rows from a SQL Server database table where a column had a newline character (CR+LF). After a little searching I found one approach.

SELECT * 

FROM Table 

WHERE PATINDEX('%'+CHAR(13)+CHAR(10)+'%',Column) > 0

I hadn't used SQL Server's PATINDEX function before, but it looks like it could be pretty useful. If anyone has another approach please let me know.

This is something that drives me crazy. The other day I needed to debug a problem report so I asked the developer to send me SQL he was using to generate the report. The SQL looked something like this:

[More]

In MS SQL Query Analyzer for SQL 2000 you can search for just about any database object using the Object Search. To access Object Search just press the F4 key while in Query Analyzer. Since my co-worker turned me on to this feature I've found myself using it quite a bit. It is great for finding column and table names in large databases as it allows you to do wildcard searches. Say for example you can't remember if a column is named "LastLogIn" or "LastLoggedIn" and say you also can't remember which table it is stored in. Object Search to the rescue. Just select the "Column" object type and search for "LastLog%" and you will get a list of all columns that start with LastLog, as well as what tables they are stored in.

[More]

Here is another update for the CF Debug Extension to the SQL Explorer Plugin. The extension wasn't properly handling yes/no true/false values for cf_sql_bit queryparams. This update fixes the issue.

To get the latest version of the extension click the download link at the bottom of this entry, between the send and del.icio.us links. (You may need to right click and "Save Link As...")

Well, I found out some other people are using my CF Debug SQL Explorer Extension so I'm releasing this update. In my original extension I forgot to escape single quotes, so this version fixes that. (I noticed I forgot to handle this while looking through Ben Nadel's code for a bookmarklet which does something similar to this extension.) This release also includes the source if anyone is interested, just look in the .jar. Installation instructions are still the same and you can find those in the related entry. Enjoy!

Have you ever wanted to be able to copy a query from ColdFusion's debugging output and run it in SQL tool to see the results? No big deal really, unless you happen to be using cfqueryparam. (You are using cfqueryparam aren't you?) If your query does use cfqueryparam you will notice that the SQL listed in the debugging output has question marks wherever you used a queryparam. The type and value of each queryparam is listed in the debugging output just below the query, but in order to run your query in a SQL tool you have replace each of the question marks in the SQL statement with the actual queryparam value, remembering to quote values as necessary. Still not a big deal, until you have to do this for a 500 line SQL statement with 50 or more queryparams. Then it gets to be a little annoying.

[More]