Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at cf.Objective() 2012 (Minneapolis, MN) with:

Turning On Multiple Statements In ColdFusion 8 MySQL 4/5 Datasource

By Ben Nadel on
Tags: ColdFusion, SQL

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:


 
 
 

 
Turn On Multiple Statements For MySQL 5 Datasources In ColdFusion 8 Administrator  
 
 
 



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.

Reply to this Comment

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.

Reply to this Comment

@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.

Reply to this Comment

@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?

Reply to this Comment

@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.

Reply to this Comment

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?

Reply to this Comment

Thanks for the tidbit Ben. I have always worked around this limitation in the past, but I absolutely need multiple statements for something I'm working on. I did a google search for "mysql 5 allow multiple statements" and your post came up.

Reply to this Comment

More string parameter can be found in the MySQL Jconnector documentation. http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-configuration-properties.html

I am not sure this is the good place for this question but it could be related to the jconnector. I have a new trouble now with the new MySQL 4/5 connector with decimal,flaot,numeric notation. This connector return scientific notation with a MySQL 5.1. Do you have an idea to convert scientific notation to decimal notation ?

Reply to this Comment

By the way like other said, you must be 100% sure your CF web application is secure to turn on this kind of settings. I could imagine how destructive multiple queries could be.

Reply to this Comment

This doesn't seem to be the case for CF 7, however, CF 8 works just as you desribe. I tried adding the line to the Connection String in each server's datasource and only CF 8 would honor the request for multiple statements. CF 7 errors out.

Reply to this Comment

@Brad,

I might be the MySQL drivers. I think in CF7 there is not built-in MySQL 4/5 driver. Maybe earlier versions of MySQL don't support this?

Reply to this Comment

Another Kinky-archive post to the rescue! I had no idea this was fixable, just knew it didn't...quite... work... as ... expected. Not hard to work around but this is better.

You rock, Ben. (but you know that already)

Reply to this Comment

Ben, this works fine on my production server, but one weird thing I'm running into is it won't work on my development machine. I'm using CF8/MySQl5.

I set the connection string just like on production, and it errors out on my multi statement. Weird!

Thanks!

Reply to this Comment

@Will,

Hmm, that is odd. Are you sure it's MySQL 5? I can't think of any reason why it wouldn't be the same if you use the same connection string?

Reply to this Comment

Ben, I figured out how to fix it. I simply deleted the existing DSN, then recreated it using the connection setting. That seemed to fix it.

Thanks!

Reply to this Comment

This trick doesn't return multiple resultsets.
........................................

<cfset query={}/>
<cfquery name="query.resultSet" result="query.details" datasource="dev">
select 0;
select 1;
</cfquery>
<div>query: <cfdump var="#query#"/></div>
........................................

You can only return multiple resultsets from cfstoredproc.

Luckily, there are some easy ways to do this.

You can also maintain your parameters if you want to get fancy. I just demonstrate the basics.

Microsoft SQL Server (MSSQL)
http://msdn.microsoft.com/en-us/library/aa933299%28SQL.80%29.aspx
........................................

<cfset query={}/>
<cfsavecontent variable="query.sql">
select 0;
select 1;
</cfsavecontent>
<cfset query.resultSets={}/>
<cfstoredproc result="query.details" returncode="true" datasource="dev" procedure="[dbo].[sp_executesql]">
<cfprocparam dbvarname="@stmt" value="#query.sql#" cfsqltype="CF_SQL_LONGVARCHAR"/>
<cfprocresult name="query.resultSets.i1" resultset="1"/>
<cfprocresult name="query.resultSets.i2" resultset="2"/>
</cfstoredproc>
<div>query: <cfdump var="#query#"/></div>
........................................

It's a little harder in MySql, because you'd have to create your own procedure to do it.

MySQL
http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html

Reply to this Comment

@Alex,

To be honest, I've never used a stored procedure in my life; but, I have heard that the one big benefit that they offer is the return of multiple result sets and multiple values. I am not sure how often I would use such a feature, though.

Reply to this Comment

@ben

> stored procedures

The example I've shown using prepared statements is just directly using the same method ColdFusion/Java uses to do parameterized queries. This isn't really using stored procedures. This is just a way to do multiple statements/resultsets in a single database call.

Usually, you make a stored procedure to handle some database stuff you want to encapsulate, or hide, do frequently, or optimize.

They can also be part of an authorization/access scheme. You can grant execution on specific stored procedures and grant select on specific views, without granting access to the underlying tables. This is especially helpful if there are diverse needs, e.g., administrator/editor, guest/reader.

The main thing they're good for is create/update/delete scripts, since these almost never change.

They are harder to use for custom queries like searches or report builders. Albeit not impossible, their performance benefits are lessened.

The big benefit is cached execution plans. The database server can cache how it executed a certain statement, so the next time the statement is run, it knows exactly what to do.

Another benefit is transactions for multiple statements.

Also beneficial are a custom return value, input/output parameters, and multiple resultsets.

Reply to this Comment

@Alex,

Ahh, I see what you're doing - executing the SQL statement. I was distracted by the use of the storedproc tags to see what it was actually doing.

Reply to this Comment

hi i have followed your example and done as you instructed but i still get error. maybe am missing some thing. please guide. here is my code.

<cfcomponent>
<cffunction name="listProperty" access="remote" returntype="string">
<cfargument name="ctry_name" type="string" required="yes"/>
<cfargument name="info" type="struct">
<!---<cfargument name="location" type="string" required="yes"/>
<cfargument name="plot_number" type="string" required="yes"/>--->

<!--- Query's Where statement is Checking to see if the username already exists in the database --->
<cfquery name="getproperty" datasource="realestate">
SELECT location, plot_number
FROM homes
WHERE location = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.info.location#">
AND plot_number = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.info.plot_number#">
</cfquery>
<!--- If our query shows a username match then using recordCount we can tell the user that his username already exists in our database --->
<cfif getproperty.recordCount GT "0">
<cfreturn "Sorry that Property is already listed.">
<!--- If our recordCount is not greater than "0" we continue on and process the new member--->
<cfelse>
<!--- Insert Data (preferably after cleaning it) see my "advanced form checking tutorial" if you are not sure how to approach this task--->
<cftransaction>
<cfquery name="ctry_list" datasource="realestate">
SELECT ctry_id, ctry_name
FROM country
WHERE ctry_name = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.ctry_name#">
</cfquery>

<cfquery name="galleryPics" datasource="realestate">
INSERT INTO homeImages(img1, img2, img3, img4)
VALUES(<cfqueryparam value="#arguments.info.img1#">, <cfqueryparam value="#arguments.info.img2#">, <cfqueryparam value="#arguments.info.img3#">, <cfqueryparam value="#arguments.info.img4#">)
</cfquery>

<cfquery name="homeimgID" datasource="realestate">
<!---SELECT max(img_id) AS newimg_id--->
SELECT LAST_INSERT_ID() AS newimg_id
FROM homeImages
</cfquery>
<!---set newimg_id = last_insert_id();--->

<cfquery name="list" datasource="realestate">
INSERT INTO homes(ctryid_home, city, location, price, type, square_feet, date_listed, bedrooms, bathrooms, pool, backyard, closets, img, lat, lng, description, tour, plot_number, kitchen, living_room, garage, roof_type, memid_home, category, imgid_home, garages, tile_floor, hardwood_floor, laminate_floor, balcony)
VALUES("#ctry_list.ctry_id#",
<cfqueryparam value="#arguments.info.city#">,
<cfqueryparam value="#arguments.info.location#">,
<cfqueryparam value="#arguments.info.price#">,
<cfqueryparam value="#arguments.info.type#">,
<cfqueryparam value="#arguments.info.square_feet#">,
<cfqueryparam value="#arguments.info.date_listed#">,
<cfqueryparam value="#arguments.info.bedrooms#">,
<cfqueryparam value="#arguments.info.bathrooms#">,
<cfqueryparam value="#arguments.info.pool#">,
<cfqueryparam value="#arguments.info.backyard#">,
<cfqueryparam value="#arguments.info.closets#">,
<cfqueryparam value="#arguments.info.img#">,
<cfqueryparam value="#arguments.info.lat#">,
<cfqueryparam value="#arguments.info.lng#">,
<cfqueryparam value="#arguments.info.description#">,
<cfqueryparam value="#arguments.info.tour#">,
<cfqueryparam value="#arguments.info.plot_number#">,
<cfqueryparam value="#arguments.info.kitchen#">,
<cfqueryparam value="#arguments.info.living_room#">,
<cfqueryparam value="#arguments.info.garage#">,
<cfqueryparam value="#arguments.info.roof_type#">,
<cfqueryparam value="#arguments.info.memid_home#">,
<cfqueryparam value="#arguments.info.category#">,
"#homeimgID.newimg_id#",
<cfqueryparam value="#arguments.info.garages#">,
<cfqueryparam value="#arguments.info.tile_floor#">,
<cfqueryparam value="#arguments.info.hardwood_floor#">,
<cfqueryparam value="#arguments.info.laminate_floor#">,
<cfqueryparam value="#arguments.info.balcony#">
)
</cfquery>
</cftransaction>
</cfif>
</cffunction>
</cfcomponent>

Reply to this Comment

@Will,
Thank you! Saved me a huge headache from beating my head on my desk. Recreating the DSN resolved my issue.

And thanks, Ben, for your great site. It's saved my butt many times.

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.