Posted by Jerry Stuckle on 11/26/07 04:15

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

[Back to original message]