ORA-00904: “WM_CONCAT”: invalid identifier

I’ve never run into wm_concat before when using Oracle, but I saw it in a statement similar to this earlier today:

FROM chris.item
WHERE item_type LIKE '%floppy%';

Of course, it didn’t work:

ORA-00904: "WM_CONCAT": invalid identifier

It looks like WM_CONCAT isn’t a supported command (Error when using wm_concat function), and possibly the code I was debugging stopped working when the database was upgraded to 12c.

WM_CONCAT is intended to return the results as a comma-separated list. A strange idea, but it this particular case it almost made sense. A supported replacement that aggregates results into a single string is LISTAGG.

FROM chris.item
WHERE item_type LIKE '%floppy%'

In this case, I did not want the results separated into groups, so I used GROUP BY 1 to put all results in the same group.

There are a number of other string aggregation techniques for Oracle, if you ever find yourself with such a need.

Tags: , , ,

Friday, October 30th, 2015 Oracle No Comments

Connecting to Oracle instance in AWS RDS

I inherited a development Oracle instance in Amazon Web Services’s Relational Database Service (AWS RDS) from a former colleague. I wanted to know what was in the database, and for that, I had to connect to it. I decided the most straightforward way would be with the simplest tool: SQL*Plus.
› Continue reading

Tags: , , , ,

Friday, October 16th, 2015 Oracle No Comments

Holding messages in the Postfix mail queue

Earlier today, someone sent a large number of email messages each containing a 30 megabyte attachment to users on our servers. This put our Postfix servers under a heavy load and caused some messages to be delivered after a substantial delay. (This was in part due to additional processing done by our servers, I’m sure a plain-jane Postfix instance could have handled it without an issue.)

This was no good. The sender–let’s call it bigbulk.test.com–should be able to send such messages, but not at the expense of normal mail delivery. I needed to change the priority of those messages to let other messages take priority.

The first thing I did was to hold all the mail from bigbulk.test.com:

  • Retrieve the mail queue
  • Select only the lines containing bigbulk.test.com
  • Select only the queue ID, the first item listed in each result
  • Pass the queue IDs to the postsuper -h command

mailq | grep bigbulk.test.com | cut -d ' ' -f 1 | xargs -n1 postsuper -h

But what about delivering them? I sent them in small batches so as not to overload the server again.

  • Retrieve the mail queue
  • Select only the lines containing bigbulk.test.com
  • Select only the queue ID (stripping out the hold-indicator)
  • Select only the first 5 results
  • Pass the queue IDs to the postsuper -H command

mailq | grep bigbulk.test.com | cut -d '!' -f 1 | head -n5 | xargs -n1 postsuper -H

Tags: ,

Monday, September 21st, 2015 Tips & Tricks No Comments

Password Form Usability: Duke Energy

When you’re singing up with any online service, picking a password is always trouble. What weird password requirements does this service have? Tonight I had that question with Duke Energy’s sign-up form. Fortunately, they had a Help icon that described the password rules:

Duke Energy Sign-up Form: password rules

Text reads: Password are case-sensitive and must contain at least eight letters or numbers.

OK, minimum of 8 alphanumeric characters. Easy enough.

I use Password Safe as my password manager. I have no idea what any of my passwords are: they are auto-generated random strings. I generated a new random password and entered it into the form:

Duke Energy Form: password feedback mixed messages

Text reads: Must be at least eight characters, contain one letter and one number and no special characters.

OK, so the password rules are a little different than what was initially described. But which symbols are special characters? And this screen is sending mixed-messages: if the password strength is rated Strong, why is it not valid?

I updated the password generation rules in Password Safe to generate a new password, assuming that only alphanumeric characters are allowed:

  • Use lowercase letters, minimum 1
  • Use upper case letters, no minimum
  • Use numbers, minimum 1
  • No symbols

And, since no symbols are included, I increased the password length to 16 characters. This new password was accepted, but the feedback indicates that the password is only moderately strong!

Duke Energy form: a valid password, rated moderately strong

The password is valid, but rated only moderately strong

I’m guessing that the code that generates the password strength indicator is from a 3rd-party and has no knowledge of Duke Energy’s password rules.

My problems with this, from a usability perspective:

  1. The password rules should apparent and described accurately.
  2. The password strength indicator should be aware of any password rules, and should describe a rejected password as such.

From a security perspective, I don’t see why any keyboard characters should be restricted. More characters to choose from means more complexity. Plus, if there were no character restrictions, it would be easier to describe the rules–and use an accurate 3rd-party password-strength tool.

Tags: ,

Saturday, September 12th, 2015 User Interface No Comments

Using getResponseHeader with jQuery’s ajax method

The Canvas API uses pagination for requests that return multiple items. The URL of the next result set is specified in an HTTP response header. I know how to get the response body, but how do I get the response headers from a jQuery AJAX call?

The jQuery ajax documentation indicates that the “[jqXHR] object returned by $.ajax() as of jQuery 1.5 is a superset of the browser’s native XMLHttpRequest object.” And the XMLHttpRequest object includes a getResponseHeader method.

At first I tried calling getResponseHeader as a method of the data object:

}).done(function (data) {

But data contained just the response body, and data.getResponseHeader was null.

A closer look at the jQuery documentation indicated that additional objects are passed to the done function, including the jqXHR object. Accessing that object’s getResponseHeader method worked:

}).done(function (data, textStatus, xhr) { 

Tags: , , ,

Saturday, August 30th, 2014 Javascript No Comments

ColdFusion session fixation and jsessionid

A web application is vulnerable to a session fixation attack if an unauthenticated user’s session ID does not change after authentication. A malicious user could start an unauthenticated session and give the associated session ID to the victim. Once the victim authenticates, the malicious user now shares that authenticated session.

ColdFusion introduced SessionRotate in CF10 to mitigate such attacks. However, it only applies to CFID and CFTOKEN (ColdFusion sessions). If you are using J2EE sessions, there is no SessionRotate. This is unfortunate, as the ColdFusion documentation has stated for years that J2EE sessions have advantages over ColdFusion sessions (see Configuring and using session variables), the foremost being that J2EE sessions are not persistent. It’s also unfortunate, because this is an important security-related tag that only works under certain server configurations–and fails silently under others!

As a side note, J2EE has been called JEE (Java Enterprise Edition) since Java 1.4. The ColdFusion administrator continues to refer to it as J2EE, but you are likely to see references to JEE in reference material online.

Session rotation basically consists of:

  • Deleting the user’s current session
  • Creating a new session containing the same data, but with a different ID

Jason Dean at 12robots.com wrote about mitigating session fixation on ColdFusion in Session token rotation REVISITED and other articles. His posts were helpful in explaining the issue and offering a solution for rotating ColdFusion sessions on pre-CF10 systems. However, he posted no solution for JEE sessions.

But according to the CF documentation on Ending a session: You cannot destroy the session and create a session on the same request, as creating a new session involves sending session cookies back.

That’s problematic. Once a user authenticates, we can destroy the user’s session. But how do we create the new, authenticated session?

An answer posted in response to ColdFusion Session Fixation on StackOverflow suggests the solution is to store necessary data in a secure way and pass the data, or a token associated with that data, on to a new HTTP request. I considered, for example, generating a UUID as a nonce and storing it in a database along with the username and a near-future timestamp. The user would then be directed to a URL with the nonce appended as a token, which could be validated against the database to authenticate the user.

Fortunately, a simpler solution exists. Pete Freitag posted SessionRotate solution for JEE Sessions in March, 2014. By accessing properties of the Java servlet session running under ColdFusion, his methods perform the following steps:

  • Copy the session scope into a temporary variable
  • Remove the ID and URL tokens from the temporary variable
  • Invalidate the existing session
  • Create a new session
  • Copy the attributes of the temporary variable into the new session

The final step is somewhat more complicated, as the new session is apparently not immediately available, and copying the attributes happens in the Application.cfc’s OnSessionStart. Still, it can happen in a single request! The JEE documentation on Creating and Managing User Sessions has more information about the underlying JEE sessions.

When I implemented Pete Freitag’s solution, I made the following modifications:

  • I created a jeeSession.cfc containing:
    1. rotate (originally named jeeSessionRotate)
    2. copy (containing most of onSessionStart)
  • I created jeeSecureApp.cfc containing:
    1. onSessionStart
    2. an instance of jeeSession.cfc

I can modify the cfcomponent tag in the Application.cfc of any app to include:

Then jeeSession.rotate() is available.


  • If your application already extends a CFC, you’d have to adjust this approach.
  • If your application already defines an onSessionStart, you’d want to add a call to super.onSessionStart

Tags: , , , , , ,

Tuesday, July 22nd, 2014 ColdFusion, security No Comments

createDateTime is not a valid date format

While transferring an application from a ColdFusion 9 server to a ColdFusion 10 server, it produced the following error:
createDateTime is not a valid date format

I was able to trace the error to the following line:
parseDateTime(myDate, 'yyyymmdd')

(Of note is that the troublesome line does not actually call createDateTime. Maybe it does under the hood?)

The myDate variable was the result of a createDate call, and looked like this:
{ts '1985-10-26 00:00:00'}

I have no idea why ‘yyyymmdd’ was specified as the expected format, nor do I know why CF9 was more forgiving about it than CF10. If you run into this error, the format parameter is definitely something to check.

As an aside, the Adobe documentation is terrible as usual: https://wikidocs.adobe.com/wiki/display/coldfusionen/ParseDateTime

It lists 2 parameters, date/time string and pop-conversion, but gives no examples of the latter. And of course there is a 3rd parameter, the date format. This is the documentation for CF11–as far as I can tell there is no pop-conversion parameter in CF10 or earlier–but the page does not clearly indicate which version it covers, nor does it provide a link to the same content for earlier versions.

Tags: ,

Friday, June 20th, 2014 ColdFusion No Comments

Event processing, interval processing in Excel

(And by Excel, I mean MS Excel, Open Office, and Google Docs.)

I was recently working with a large amount of computer-generated event data. I wanted to analyze the data, but was only concerned with events (rows) that occurred within intervals demarcated by certain start and end events.

At the time, I had no answer for this in Excel. I wrote a small computer program that read the file one line at a time and ignored lines that occurred outside the intervals of interest. Recently I came up with a solution for this problem in Excel, so I thought I would share it here.

In this example, I am going to use a highly simplified traffic study as my example. A computer at a traffic light records 2 kinds of events:

sensor events
on or off, indicating whether or not there is a car in the intersection
light events
red, amber, or green, indicating the new light color

Here are some sample data collected by this computer:

seconds event state
0 light green
7 sensor on
8 sensor off
15 sensor on
16 sensor off
25 light amber
30 light red
60 light green
85 light amber
90 light red
92 sensor on
93 sensor off
120 light green
145 light amber
150 light red
180 light green
199 sensor on
200 sensor off
204 sensor on
205 light amber
206 sensor off
210 light red
240 light green
265 light amber
269 sensor on
270 light red
271 sensor off
300 light green

Let’s say we want to find out how many cars drove through a red light–that is, the light was red when the car started driving through the intersection.

First, add a new column. This column will indicate the current state of the light for each event. That’s trivial for each light event, but associating the state of the light with each sensor event is what we’re after. In this column, add the following formula:

Excel and Google Sheets:

Open Office Spreadsheets:
=IF(B2="light"; C2; D1)

That formula means:

  • IF the current event is a light event
  • THEN set this cell to the current state
  • ELSE set this cell to the most recent light state.

Next, add another column. This column will indicate whether the row represents a driving through a red light. In this column, add the following formula:

Excel and Google Sheets
=IF(B2="sensor", IF(C2="on", IF(D2="red", 1, 0), 0), 0)

Open Office Spreadsheets
=IF(B2="sensor"; IF(C2="on"; IF(D2="red"; 1; 0); 0); 0)

The above is a nested series of if statements:

  • IF the row contains a sensor event AND
  • IF the sensor event is an on event AND
  • IF the current state of the light is red
  • THEN it is a traffic violation
  • ELSE it is not a traffic violation

Copy these formulae to the other rows, via Edit–Fill–Down (Excel and Open Office) or ctrl-d (or cmd-d on Mac). The spreadsheet should now indicate that there was one incident of running a red light, which occurred at second 92.

Tags: , , , ,

Friday, January 3rd, 2014 Tips & Tricks No Comments

Using group expressions in regular expression pattern matching

I’ve used group expressions in regexes many times, but only for replacement. Yesterday I learned that they can also be used for matching.

For example, let’s say you have the text:

Banananananas don’t grow in Mississississippi because banananas are afraid of getting turned into Missississippi’s famous bananana pudding.

The following regular expression will find instances of iss or an that are repeated more than twice.


You can use \1\1 as the replacement (or $1$1 in Dreamweaver, which uses backslashes to identify groups in match expressions, but dollar signs to represent groups in replace expressions) to turn the misspelled words into Mississippi and banana(s).

Another example might be applying consistent formatting to phone numbers or dates.

Phone numbers
Let’s say you usually use 555-555-1212 as the format for phone numbers and sometimes you use 555.555.1212, but the new trend is to use spaces instead of dashes or dots as separators:

Find: ([\d]{3})([-\.])([\d]{3})\2([\d]{4})
Replace: \1 \3 \4

Let’s say you usually use 12/5/2013 as the format for dates, dabbled with 12.5.2013, but now you’ve decided that dashes are clearer:

Find: ([\d]{1,2})([\./])([\d]{1,2})\2([\d]{4})
Replace: \1-\3-\4

In both cases you could just repeat the bracketed character class, but then you could end up matching strings you didn’t intend to:

  • 555-555.1212
  • 12.5/2013

Tags: , ,

Friday, December 6th, 2013 Tips & Tricks No Comments

Converting lines to a list in ColdFusion

I’m so used to dealing with comma-delimited lists in ColdFusion that I would sometimes take a data file that had one item per line and replace the newline characters with commas.

It’s easy to use the carriage return [chr(13)] and line feed [chr(10)] characters as list delimiters, though, and remove the intermediary step. Here’s a quick example:

<cfsavecontent variable="data">

        <cfloop list="#data#" delimiters="#chr(13)##chr(10)#" index="line">

Which produces the following:

  1. this
  2. is
  3. a
  4. list
  5. with
  6. one
  7. word
  8. per
  9. line

(I can’t believe I didn’t think of this until today!)

Tags: ,

Monday, November 11th, 2013 ColdFusion 2 Comments