Tech Blog

Multiple queries with CFMX-MySQL JDBC connector

Posted At : February 10, 2008 10:32 AM 3 Comments

I found a little snippet the other day and included it in the tail end of a previous post - however, on reflection I thought it deserved a post of it's own.

If you want to utilise multiple queries per sql statement and you are using the MYSQL JDBC connector you need to add the following to your jdbc connector querystring.

allowMultiQueries=true
So you querystring would look like:
jdbc:mysql://127.0.0.1:3306/mydbname?allowMultiQueries=true

This will allow you to do queries like the following to return autoincrement values:

<cfquery name="qInsert" datasource="mydsn">
INSERT INTO tbl_demo (name)
VALUES ( <cfqueryparam value="#myName#">);
SELECT last_insert_id() as newID
</cfquery>
<cfoutput>ID of value is: #qInsert.newID#</cfoutput>

Please note however that this functionality is disabled as it can leave the door open for SQL Injection attacks. However, as long as you always use cfqueryparam for all the dynamic parts of your query you will be fine.

Posted in: Database, ColdFusion, mysql

Leave a Comment

3 Comments

tsammons 2/11/08 11:59 AM

If you allow multiple queries, are you setting yourself up for SQL injection?

Mark Lynch 2/11/08 12:34 PM

Hi tsammons,
Yes and No - you are opening a potential way that sql injection queries can happen, by appending a semi-colon and writing a new query. But if you are correctly using cfqueryparam's in all your queries then it won't happen.

And conversely by not allowing multiple queries, you are not protecting yourself from all potential SQL injection's attacks.
Cheers,
Mark

Martin Parry 2/15/08 9:54 AM

Brill -This looks like this can reduce the need for a CFLOCK or CFTRANSACTION around an insert while we write the record and then retrieve the new ID.

Additionally, bulk insertion of data should be quicker too.

Thanks

Martin