Turning On Multiple Statements In ColdFusion 8 MySQL 4/5 Datasource
Posted April 24, 2008 at 2:31 PM
Lately, I have been doing a lot of work in ColdFusion 8 and MySQL 5. One of the things that I noticed immediately was that MySQL didn't seem to allow multiple statements within one CFQuery tag. I read that this is apparently done to prevent SQL injection attacks, which as a ColdFusion developer seems kind of silly. Anyway, it took just a little bit of digging to figure out how to turn this feature on. Based on a post by Cameron Childress, I figured out that if you add "allowMultiQueries=true" to the Connection String box in the ColdFusion 8 MySQL 4/5 datasource administrator, everything works quite nicely:
| | | | ||
| |  | | ||
| | | |
Comments (10) | Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Dynamically Evaluating Image Functions In ColdFusion 8
ColdFusion Image Manipulation Functions Return Nothing
Reader Comments
Thanks for the tip!
I recently converted a site from MS SQL to MySQL 5 and ran into that issue. At the time I just tweaked the CF code to work around it, but I know I will use this in the future.
Posted by Terry Schmitt on Apr 24, 2008 at 3:22 PM
@Terry,
Glad to help in some way :)
Posted by Ben Nadel on Apr 24, 2008 at 4:08 PM
Lest someone get the wrong idea from your post, ColdFusion developers *definitely* need to worry about SQL injection attacks. CF is just as vulnerable to poorly coded pages as PHP or any other language. CF does however have the best weapon in this battle: the CFQUERYPARAM tag. Correct use of CFQUERYPARAM can protect your apps from SQL injection better than this MySQL connection parameter ever could.
Posted by Brian Panulla on Apr 24, 2008 at 4:37 PM
@Brian,
Yes, very true. Sorry if I came off a bit too cocky in my post. It just seemed like a precaution that was swinging WAY too far in the other direction.
Posted by Ben Nadel on Apr 24, 2008 at 5:51 PM
@Ben,
Yes.. probably too far. I know I use the technique often for grabbing autogenerated primary key values directly after inserts with SELECT LAST_INSERT_ID().
That being said, I think there is something to be said for safe defaults in software, letting the advanced user enable capabilities that only they will know to look for. Hopefully the JDBC connector threw a very helpful error message. Did it at least tell you how to enable what you wanted to do, or did it just flat out fail with no explanation?
Posted by Brian Panulla on Apr 24, 2008 at 6:02 PM
@Brian,
The error message was actually very little use. All it told me was that I had a syntax error near INSERT. A useful error would have been something like:
"You are attempting to use multiple statements as defined by ';' This option needs to be enabled in your database connection."
Now, something like that would have been sweeeeeet.
Posted by Ben Nadel on Apr 24, 2008 at 6:27 PM
Brians suggestion of how to use the SELECT LAST_INSERT_ID() would be very helpful. Up to this point I've always had one or a combination of fields that i knew were distinct but i may not always have that luxury. It didn't occur to me that i could put 2 queries together. I am curious how the results from the second query are stored... same name as the first query or no?
Posted by Dustin on Apr 25, 2008 at 12:14 PM
@Ben,
I posted a more thorough explanation of my technique to my blog, if you'll allow the link:
http://ghostednotes.com/index.cfm/2008/4/25/CFQUERY-and-autonumber-primary-keys
Posted by Brian Panulla on Apr 28, 2008 at 8:48 AM
So this means multiple queries can be run in one cfquery, delimited by (I assume) a semicolin?
Posted by Eric Hynds on May 7, 2008 at 7:30 PM
@Eric,
Oh heck yeah.
Posted by Ben Nadel on May 7, 2008 at 7:41 PM
