MySQL 3/4 - com.mysql.jdbc.Driver And allowMultiQueries=true
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:
| |
|
|
||
| |
 |
|
||
| |
|
|
I put that in and it worked nicely.
Reader Comments
Make sure your queries are sanitized. Most SQL injection attacks use the ability to do multiple queries per connection.
@Jules,
Most definitely.
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 ...
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.
@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.
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.
@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 =)
@Thilo,
I have never had a problem with it and I find it to be saver than SELECT MAX(id).
@Jules,
Ha ha, I never get tired of a good debate :)
Try using UUID's as order ID's, and you'll have some pissed customers. :)
@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.
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.
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?
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.
Yes, thats why I like it so much. So why bother with an auto incrementing ID?
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.
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.
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.
@Gareth,
I could do that, but then I'd have to write stored procedures :)
Have your DBA do it...his name's Ben too, right? :)
@Gareth,
Ha ha ha, yeah, but he's sooo lazy.
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.
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?