You have a button to set as primary key, why do we have to scroll down through 3/4 of the properties, click the plus under "Identity", and then choose "Yes" for such a common operation?

Thank You

It was nice to see that they have code samples for ColdFusion!

Some potential uses for this new service:

• Mailing Address Verification - you can send an automated letter with a personalized code, and have them enter in the code to your web site.
• Security Nofications - You can send a letter to a users mailing address, when the account email or password is changed for verification purposes.
• Promotional Codes - Setup a scheduled task that runs through your customers and sends out a certain amount of coupons each month.
• Lots More - What would you use it for?

Fortunately SQL Injection is very easy to prevent in CFML using the cfqueryparam tag, and many people have pointed out some of the simple use cases for the tag. But there are a few cases where you can't use the cfqueryparam tag. In those cases, ColdFusion might throw an exception that looks like this:

[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '@P1'.
[Macromedia][SQLServer JDBC Driver][SQLServer]Statement(s) could not be prepared.

Let's take a look at some of these special cases, and how to get around them:

SELECT TOP

If you are passing a variable into a SELECT TOP statement, you can't use cfqueryparam, instead consider using the Val function. This is a really handy function that will return 0 whenever it gets a non-numeric value, and will convert decimal values into integers.

SELECT TOP #Val(url.max_rows)# first_name FROM people

ORDER BY

When attempting to use a cfqueryparam tag in the ORDER BY statement you might receive an error such as:

[Macromedia][SQLServer JDBC Driver][SQLServer]The SELECT item identified
by the ORDER BY number 1 contains a variable as part of the expression
identifying a column position. Variables are only allowed when ordering
by an expression referencing a column name.

A good way to get around this limitation is to use the ListFindNoCase function, to limit the sortable column names, for example:

<cfset sortable_column_list = "age,height,weight,first_name">
<cfquery ...>
  SELECT first_name, age, height, weight
  FROM people
  ORDER BY <cfif ListFindNoCase(sortable_column_list, url.sort_column)>#url.sort_column#<cfelse>first_name</cfif>
</cfquery>

Passing Value Lists using IN

What to do when your variable contains a list of values to be used with a SQL IN expression? The cfqueryparam actually makes it very easy to pass a list, you don't even need to put single quotes around each element if for text lists, it takes care of that for you. To use cfqueryparam with an IN simply add list="true" to your cfqueryparam tag.

<cfset name_list = "Bob,Fred,Pete">
<cfquery ...>
  SELECT first_name, age, height, weight
  FROM people
  WHERE first_name IN (<cfqueryparam value="#name_list#" list="true" cfsqltype="cf_sql_varchar">)
</cfquery>

Cached Queries

ColdFusion 8 now allows cfqueryparam in cached queries, but if you are running earlier versions, you won't be able to use it with cached queries.

If the variables passed into the query are integer only, then you can use the Val function to protect against SQL Injection. Or if the possible string values are limited you can use the ListFindNoCase function as shown above.

The best workaround is to remove the caching, upgrade to CF8, or cache them in the application scope, as follows:

<cfif NOT IsDefined("application.my_cached_query")>
  <cfquery name="application.my_cached_query">
    ...
  </cfquery>
</cfif>

This will keep the query cached until the application is reinitialized, or the variable is overwritten.

Passing NULL's

The cfqueryparam lets you pass null values into your database using the null="true" attribute. For example:

UPDATE people
SET age = <cfif IsValid("integer", form.age) AND form.age NEQ 0>
  <cfqueryparam value="#form.age#" cfsqltype="cf_sql_integer">
<cfelse>
  <cfqueryparam null="true" cfsqltype="cf_sql_integer">
</cfif>

Those are some of the more common gotcha's that you will run into with cfqueryparam. Please post a comment with any other cfqueryparam tricks, or special cases.

You can also filter searches by language, for example if you just wanted to search for ColdFusion code you can add: lang:coldfusion to your query.

• Thursday @ 2:45p - Writing Secure CFML
• Friday @ 8:30a - Image Manipulation with ColdFusion 8

See you there!

RPC:Completed exec sp_execute 1,2,4

The reason for this is that ColdFusion's JDBC Driver for SQL Server passes SQL statements through the sp_execute stored procedure, so you are only seeing the stored procedure call. You need to enable an event that is not enabled by default, the SP:StmtCompleted event.

When you start your trace click on the Event Selection tab, and then check Show all events then find SP:StmtCompleted under the Stored Procedures node.

The above was tested on SQL Server 2005, you may have a different UI on previous versions.

Now when you run your trace you should see your SQL Statements in the TextData column where EventClass is SP:StmpCompleted

The first is sepia tone, you can see an example image to the left. Sepia tone was popular in the 1800's (used to give black and white photos a bit of color), but thanks to modern photoediting technology is making a comeback. The idea for this effect came from a customer suggestion.

I also added two other effects to the component which can lighten or darken an image or photo.

You can score all 8 of the effects (rounded corners, gradients, reflections, drop shadows, etc) for $39.99 until Thursday May 1st, when the price increases to $49.99

Existing customers get a free upgrade, so just login to your account, and download the latest code.

Visit foundeo.com to order.

Specifically, when we encounter a <FORM> element on a high-quality site, we might choose to do a small number of queries using the form. For text boxes, our computers automatically choose words from the site that has the form; for select menus, check boxes, and radio buttons on the form, we choose from among the values of the HTML.

They also listed some important limitations, which makes this seam much less extreme as it did when I first read about it.

The Googlebot will NOT submit forms that:

• Use method="post"
• Have password fields
• "Use terms commonly associated with personal information such as logins, userids, contacts, etc"
• Have a form action that is forbidden in robots.txt

• The cfimage tag and image functions now retain EXIF data after operating on an image.
• The height and width attributes are now optional for cfimage action=captcha. If you omit these options ColdFusion sizes the Captcha image to fit the generated text. This is nice because if you specified a height or width that was too small it would throw an exception!

They also added two other enhancements / fixes in this update:

• The cfimage tag now supports alt, style, and other standard HTML image attributes.
• If you specify an image that uses the CMYK color space for the image border, ColdFusion converts it to the RGB color space. The source image is not changed.