So you wanna learn Regex? – Part 4

Welcome to So You Wanna Learn Regex? Part 4.
In our last exercise, we looked at a simple way to clean a whole bunch of strings. This was accomplished by making a pattern, then removing everything according to that pattern.
This time we are going to add cfqueryparam to a query. Say for example, that you have a junior developer who has been turned loose on her first application and she’s done a good job, except for she didn’t use cfqueryparam. You just found this out and the site has to go live in 10 minutes and you have 200 queries to fix. Do you:

• a) Download the code to your laptop then pull the fire alarm to stall for time?
• b) Start blasting your resume out on Monster.com?
• c) Take a fistfull of aspirin, knowing your forearms will ache in the morning?

If you answered d) none of the above, please keep reading.

Assume this set of declarations:

UPDATE plant
	SET 	Symbol = '#form.symbol#',
			SynonymSymbol = '#form.SynonymSymbol#',
     		ScientificNameWithAuthor = '#form.ScientificNameWithAuthor#',
     		CommonName = '#CommonName#',
     		Family = '#Family#'
WHERE PlantCode = '#form.plantCode#'

What we want, is to turn: ‘#form.symbol#’
into: <cfqueryparam value=”#form.symbol#” cfsqltype=”cf_sql_varchar”>

So as you know, we define this pattern in the gobbledegook of regular expressions. When read one chunk at a time, these actually make sense. We’ll go through the exercise, then look at why it worked.

In Eclipse, perform the following:

1. Open a new file and paste the above set of declarations: ( the whole query, yes, the whole thing)
2. Open the find dialogue (I use CTRL+F) and make sure the Regular Expression option is ticked
3. Enter the following in the Find: Input (‘)([^’]+)(‘)
4. Enter the following in the Replace: Input <cfqueryparam value=”$2″ cfsqltype=”cf_sql_varchar”>
5. Press Find and make sure the pattern matches what we want
6. Lastly, press Replace All

You Should Have This:

UPDATE plant
	SET 	Symbol = <cfqueryparam value="#form.symbol#" cfsqltype="cf_sql_varchar">,
			SynonymSymbol = <cfqueryparam value="#form.SynonymSymbol#" cfsqltype="cf_sql_varchar">,
     		ScientificNameWithAuthor = <cfqueryparam value="#form.ScientificNameWithAuthor#" cfsqltype="cf_sql_varchar">,
     		CommonName = <cfqueryparam value="#CommonName#" cfsqltype="cf_sql_varchar">,
     		Family = <cfqueryparam value="#Family#" cfsqltype="cf_sql_varchar">
WHERE PlantID = <cfqueryparam value="#form.plantID#" cfsqltype="cf_sql_varchar">

(if not, you missed a step. Look at the image and compare with what you have in your Find/Replace dialog. Make sure there is no extra whitespace in the find expression)

Blamo! The code is now all properly CFQueryparamed, Bring It On Hackers!!! The cool thing is you didn’t even get carpal tunnel syndrome! Let’s decode the code, shall we?

Here is the find portion of the regular expression: (‘)([^’]+)(‘)

• (‘)  The first character chunk is a group consisting of a single quote. If you look at the output, we don’t need/want a single quote in it, so we’ll end up ignoring this. It must be grouped though because…
• ([^’]+)  The next chunk is another group defining everything up to the closing single quote. This means we’ll get the innards of the original update values in a second group, addressable as $2..
• (‘)  Last chunk, is another group consisting of a single quote. We’ll ignore this one too, but we could use it as $3 if we wanted.

All of that defines boundaries for a character walking regular expression gnome to start at the beginning of value for each column in the update statement and just grab what is inside the single quotes.

Then in the Replace section, we used: <cfqueryparam value=”$2″ cfsqltype=”cf_sql_varchar”>

• This is pretty much all literals, except for the backreference for the second group, $2.

So in plain English, we asked the regular expression find/replace gnome to: Define everything inside the single quotes, and hold on to it. Then in the replace pass, use the second group to populate a literal string.

I’m sure you can agree this was much easier than a copy/paste extravaganza… Stay tuned for part five…