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 the New York ColdFusion User Group (Feb. 2009) with: Aaron Foss

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

Posted by Ben Nadel
Tags: ColdFusion, SQL

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

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

Reply to this Comment

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.

Reply to this Comment

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

Reply to this Comment

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.

Reply to this Comment

@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 =)

Reply to this Comment

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

Reply to this Comment

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.

Reply to this Comment

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?

Reply to this Comment

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.

Reply to this Comment

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.

Reply to this Comment

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.

Reply to this Comment

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.

Reply to this Comment

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.

Reply to this Comment

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?

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.