MySQL: How to Combine Multiple SQL Statements in One CFQUERY

I've answered this on a couple of mailing lists now so I thought I'd blog the solution so it has a chance in Google. The problem: Combining more than one SQL statement in a a single CFQUERY tag. IE: The Solution: By default, as a security measure, the MySQL Connector/J JDBC driver does not allow multiple queries to be concatenated in a single database request. This behavior is in part an effort to prevent SQL Injection attacks on the database. You can override this setting in the JDBC driver's datasource configuration to allow multiple queries by adding "allowMultiQueries=true" to the JDBC's URL string. For example, a valid JDBC URL may look like this: jdbc:mysql://192.168.1.1:3306/databasename?allowMultiQueries=true But wait, there's more... There are several other nifty configuration options available including failover and performance options. It's really worth taking a look at them.

Comments (6)

Add Comment ]

dave Thanks for this informaction.

What if I use Oracle, any idea?
Charles Arehart Very nice, Cameron. I just pointed this out to someone asking about the topic on a CF mailing list. Thanks for writing it up.
Jason Is this specific to a particular version of MySQL or CF? I'm running Windows CF8 on my dev box and able to connect to a MySQL 5 DB and execute multiple queries. However, when I run the same code on my production box (Linux CF7), I get an error indicating syntax or access problems.

My host is also using a MySQL 3 driver on the Linux CF7 machine. I'm using the MySQL 4/5 driver that comes with CF8.
Jason BTW, I got this error in my email when I submitted my last comment....


Undeliverable:Comment added to Blog: Cameron Childress' Blog / Entry: MySQL: How to Combine Multiple SQL Statements in One CFQUERY

Your message did not reach some or all of the intended recipients.

Subject:   Comment added to Blog: Cameron Childress' Blog / Entry: MySQL: How to Combine Multiple SQL Statements in One CFQUERY
Sent:   2/20/2008 4:20 PM

The following recipient(s) cannot be reached:

no@emails.com on 2/20/2008 4:20 PM
You do not have permission to send to this recipient. For assistance, contact your system administrator.
<nerthus.fusionlink.net #5.7.1 smtp;550 5.7.1 <no@emails.com>... Relaying denied. Proper authentication required.>
Charlie Arehart Hey Jason, that error just means that one of the commenters before you (who marked the option to subscribe to the thread) gave that bogus email address. Since the comment goes out as being "from" you, that's why you get the bounce back. You can safely ignore it (and perhaps Cameron may see these and go in and remote that address and/or the the affected comment.)
Cameron Childress @Jason - I eliminated the comment that was causing the bounces since it didn't comtain anything relevant.

As far as your problem with CF8 vs CF7, this blog post was written before CF8 was available, so it's definitely focused on CF7. In CF7 you had to use a JDBC connectstring to manually connect to newer versions of MySQL because the driver that shipped with CF didn't support them. CF8 DOES support those newer versions of MySQL so you may not actually see the connectstring in the datasource.

In your case, on the CF7 box you (or your host) are doing to need to install a driver to have it support MySQL 4/5. There are several guides online on how to do this. This Adobe Technote appears to explain the process correctly http://tinyurl.com/3aobwb

Once that driver is installed you (or your host) can adjust the JDBC string to support multiple SQL statements.

Add Comment ]

Post a comment





Leave this field empty: