Skip to main content
Ben Nadel at cf.Objective() 2013 (Bloomington, MN) with: Jake Scott
Ben Nadel at cf.Objective() 2013 (Bloomington, MN) with: Jake Scott

MySQL 3/4 - com.mysql.jdbc.Driver And allowMultiQueries=true

By on
Tags: ,

Just a quick note on configuring a MySQL JDBC Driver to allow multiple queries per CFQuery tag when using MySQL 3/4 (pre MySQL 5) on a ColdFusion 8 server. A few months ago, I leaned that you could add "allowMultiQueries=true" to the MySQL 5 connection string. However, today, I had to do some work on a ColdFusion MX 7 box that only had MySQL JDBC driver capabilities. I tried adding the allowMultiQueries to the connection string but this did not work.

After some Googling, I found that you have to add the allowMultiQueries=true to the actual JDBC Url, not the Connection String field:


 
 
 

 
Turning On Multiple Queries Using com.mysql.jdbc.Driver.  
 
 
 

I put that in and it worked nicely.

Reader Comments

8 Comments

This is a setting which would be good to define always.

Depending on some of our projects we had to have on MySQL 5 definition too.

Thanks for sharing ...

39 Comments

Maybe this is a dumb question, but I personally liked the thought that it only allowed one query at a time, and because of that much less worry about SQL injection.

Other then the convenience of writing multiple queries in a single cfquery tag, what are the benefits of doing that? A speed boost cause it pack multiple calls to the db into one?

I think it's a neat tip, but I guess I'd need to be convinced on why I'd actually do it.

15,663 Comments

@Tim,

My biggest reason for using it is to create a new record and then immediately select the new auto-increment ID created using LAST_INSERT_ID(), or, in newer versions of MySQL, @@Identity.

3 Comments

I try to use allowMultiQueries=true as less as possible, hence my default datasource for an application allows only one query at a time. If I need multiple queries for an app I define a second datasource and use it only where needed.

Question: What do folks think about LAST_INSERT_ID() and using it inside 1 cfquery. Is it safe? Or even inside 2 cfquery tags one after another? Would I need <cftransaction> or <cflock type="exclusive"> for that? I am not sure since I heard different opinions on that in the past.

12 Comments

@Thilo Hermann,

My opinion on that is, to not use auto incrementing IDs - use UUIDs instead. But I'm sure Mr. Nadel is tired of that discussion =)

12 Comments

@Will Tomlinson,
Use incremental IDs for order IDs and folks know how many orders were placed, and try to snoop on other orders.

Sorta like project/invoice and check numbers. My good client can see month to month how many other invoices I've generated besides theirs. And I can see how many checks they've written in the same amount of time.

There would be no security issues in using incremental IDs so long as your app is written correctly. But still, it's kinda like dropping your drawers.

For orders/tracking I use links in emails with the UUID.

25 Comments

That's why I use a UUID in another field in the orders table - not as the PK ID.

Then I use the UUID in combination with the PK ID, for any viewing, editing, deletions, etc.

Now, my customer has a nice, short order ID, and UUID that prevents tampering.

12 Comments

I'm confused by your method. Doesn't the client still have the PKID, and use it to gain access to order information? They could then still tamper with it - no?

25 Comments

The UUID is used much like an unlock key. You need the UUID in order to do anything with the record.

No one can tamper with it because it's nearly impossible to guess someone's UUID.

25 Comments

The incrementing ID is just for the customer to use. Sometimes they call to inquire about an order. They can give my client the four digit ID instead of some long UUID.

7 Comments

Backend processing and the CLIENT can use the Auto PKID to simplify look-ups and processing... Such as a means of using @@identity or last insert id on the backend (as already mentioned) or a quick order lookup for the CLIENT... not to be confused with the consumer.

Unless it is in an authenticated section of your application, or you have taken other measures to thwart enumeration, you would never display any order information based on an auto incrementing PKID without combining it with something that ensures the user should be able to see it. Such as a UUID

Securing order information is of course only one scenario. I typically use a secondary UUID field in tables that hold file information for which I don't want the download links easily enumerated. The UUID is passed through the URL as a download key. So a file download link might look like...

http://thedomain.com/files/#fileID#/#downloadKey#

-or-

http://thedomain.com/files/?fileid=#fileID#&downloadKey=#downloadKey#

The where clause on the file lookup would simply be...

where fileID = <cfqueryparam etc... value="#url.fileID#" />
AND downloadKey = <cfqueryparam etc... value="#url.downloadKey#" />

Without the combination of both unique columns, the file record cannot be recovered.

111 Comments

Could just use stored procedures and pass parameters off to them. Set the result returned to be the id you're looking for. You wouldn't have to run multiple queries in your cf pages then.

1 Comments

Thank you very much for this post. Adding allowMultiQueries="true" in context.xml didn't help until I added it to url as allowMultiQueries=true

Good idea is to use prepared statements and it will help you to avoid possible SQL injection.

1 Comments

what if you simple remove any semicolons that are passed in a variable before they reach the query. I can't think of a situation where a semi colon would be legitimately passed from a public form.

#REPLACE(variablename,";","","all")#',

would that be sufficient protection?

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel