Executing Multiple Queries using MDB2

I'm trying to execute an action that involves multiple insert
queries. Rather than execute them sequentially, I'd rather build a
sql string containing all of the queries and only have one round trip
to the database. Using MDB2, though, I can't seem to do that. I've
separated each query with a semicolon, but I get a syntax error. When
I display the user info ($result->getUserInfo()) and copy the SQL from
the error message, I can execute the SQL just fine in CocoaMySQL.

Is this a limitation of MDB2? Am I using the wrong query separator?
I'm not seeing anything in the MDB2 documentation about this at all.

My generated SQL looks like this:

INSERT INTO object ( field1, field2, field3 ) VALUES ( value1, value2,
value3 ); INSERT INTO object_property ( field1, field2, field3 )
VALUES ( value1, value2, value3 ); INSERT INTO object_property
( field1, field2, field3 ) VALUES ( value1, value2, value3 ); ...;
INSERT INTO object_property ( field1, field2, field3 ) VALUES
( value1, value2, value3 );

Error:

[Native code: 1064] [Native message: You have an error in your SQL
syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use near '; INSERT INTO object_property
( object_id, object_property, int_value) VALUES ' at line 1]

Thanks.

Rob

Re: Executing Multiple Queries using MDB2

Multiple queries separated by a semicolon won't work. You *have* to
execute the queries sequentially.

Regards,
Kailash Nadh
http://kailashnadh.name


On Nov 25, 7:02 pm, Rob Wilkerson wrote:
> I'm trying to execute an action that involves multiple insert
> queries. Rather than execute them sequentially, I'd rather build a
> sql string containing all of the queries and only have one round trip
> to the database. Using MDB2, though, I can't seem to do that. I've
> separated each query with a semicolon, but I get a syntax error. When
> I display the user info ($result->getUserInfo()) and copy the SQL from
> the error message, I can execute the SQL just fine in CocoaMySQL.
>
> Is this a limitation of MDB2? Am I using the wrong query separator?
> I'm not seeing anything in the MDB2 documentation about this at all.
>
> My generated SQL looks like this:
>
> INSERT INTO object ( field1, field2, field3 ) VALUES ( value1, value2,
> value3 ); INSERT INTO object_property ( field1, field2, field3 )
> VALUES ( value1, value2, value3 ); INSERT INTO object_property
> ( field1, field2, field3 ) VALUES ( value1, value2, value3 ); ...;
> INSERT INTO object_property ( field1, field2, field3 ) VALUES
> ( value1, value2, value3 );
>
> Error:
>
> [Native code: 1064] [Native message: You have an error in your SQL
> syntax; check the manual that corresponds to your MySQL server version
> for the right syntax to use near '; INSERT INTO object_property
> ( object_id, object_property, int_value) VALUES ' at line 1]
>
> Thanks.
>
> Rob

Re: Executing Multiple Queries using MDB2

Rob Wilkerson wrote:
> I'm trying to execute an action that involves multiple insert
> queries. Rather than execute them sequentially, I'd rather build a
> sql string containing all of the queries and only have one round trip
> to the database. Using MDB2, though, I can't seem to do that. I've
> separated each query with a semicolon, but I get a syntax error. When
> I display the user info ($result->getUserInfo()) and copy the SQL from
> the error message, I can execute the SQL just fine in CocoaMySQL.
>
> Is this a limitation of MDB2? Am I using the wrong query separator?
> I'm not seeing anything in the MDB2 documentation about this at all.
>
> My generated SQL looks like this:
>
> INSERT INTO object ( field1, field2, field3 ) VALUES ( value1, value2,
> value3 ); INSERT INTO object_property ( field1, field2, field3 )
> VALUES ( value1, value2, value3 ); INSERT INTO object_property
> ( field1, field2, field3 ) VALUES ( value1, value2, value3 ); ...;
> INSERT INTO object_property ( field1, field2, field3 ) VALUES
> ( value1, value2, value3 );
>
> Error:
>
> [Native code: 1064] [Native message: You have an error in your SQL
> syntax; check the manual that corresponds to your MySQL server version
> for the right syntax to use near '; INSERT INTO object_property
> ( object_id, object_property, int_value) VALUES ' at line 1]
>
> Thanks.
>
> Rob
>

Hi, Rob,

Sorry, mdb2 doesn't allow multiple queries in the same statement.
Although it's annoying in your case, it can also be a safety valve.

For instance, what happens if you have something like:

"UPDATE TABLE1 SET a=3 where b=" . $_POST[invalue];

And some hacker changes $_POST['invalue'] to be something like:

"4; DELETE FROM TABLE1"

The result would be:

UPDATE TABLE1 SET a=3 WHERE b=4;DELETE FROM TABLEA

Now I agree you should validate $_POST['invalue'] before using it, but
you get the idea.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Re: Executing Multiple Queries using MDB2

On Nov 25, 11:15 pm, Jerry Stuckle wrote:
>
> Hi, Rob,
>
> Sorry, mdb2 doesn't allow multiple queries in the same statement.
> Although it's annoying in your case, it can also be a safety valve.
>
> For instance, what happens if you have something like:
>
> "UPDATE TABLE1 SET a=3 where b=" . $_POST[invalue];
>
> And some hacker changes $_POST['invalue'] to be something like:
>
> "4; DELETE FROM TABLE1"
>
> The result would be:
>
> UPDATE TABLE1 SET a=3 WHERE b=4;DELETE FROM TABLEA
>
> Now I agree you should validate $_POST['invalue'] before using it, but
> you get the idea.

Ah, SQL injection. I hope I never have to count on my database
abstraction layer to protect me from such attacks, but I guess it's a
nice safety net. Maybe. :-)

Anyway, thanks for the input guys. It's not a huge deal, I was just
surprised that it wasn't possible so I thought I'd ask. I ended up
building my sql as before, but then exploding it and iterating over
the array of statements. Works just fine.

Thanks again.