Multiple Commands

Brendan_Nolan

This may be a stupid question, but can you run multiple SQL queries in the one mysql_query() ?

$sql = "INSERT INTO thing(thing1, thing2) VALUES('$thing1','$thing2'); INSERT INTO thing(thing1, thing2) VALUES('$thing1','$thing2'); INSERT INTO thing(thing1, thing2) VALUES('$thing1','$thing2');"
mysql_query($sql);

I get the following error which indicates that I cant...
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 '; DELETE * FROM unsharp_system_user_access_table WHERE system_u

Is there any way to do it?

Sxooter

Doesn't mysql have a syntax like:

insert into table (row1, row2, row3) values ('1','2','3'), ('4','5','6');

or something like that?

Yep, I just looked it up and it seems they do, at least in 4.0.xx.

I really hate they don't leave the docs up for 3.x if you need them. What flavor mysql are you running?

Brendan_Nolan

I actually want to run a couple of differant types of queries (sorry, I should have made that clear), and I don't require a result for any of them (because I am not doing a SELECT or anything)

I am doing an INSERT, and a couple of DELETEs, all on differant tables.

Sxooter

OK, the ; being a terminator is not generally supported by later versions on PHP / database libs, because it can aid in sql inject attacks, so you'll have to run each one seperately. Which also makes sense. If you run an insert, and two selects, which data set gets attached to the result handle? The insert, the first select, or the second?

If you have a lot of the same generic queries to run, and don't want to have to type in a bunch of seperate mysql_query() statements, you can do what I do:

$vars = "something";
$sql[0] = "insert into...";
$sql[1] = "select * from...";
$sql[1].= "above long statement con't";
$sql[2]="next sql statement with $vars";

foreach($sql as $s){
  $r[] = mysql_query($s);
}

Brendan_Nolan

I only wanted to be able to do this because I thought that if MySQL was able to handle all of the queries at once it might be more efficient than doing a mysql_query() for each one.

I wouldn't normally need a result handle anyway, as I would not be doing anything with the result of a DELETE or INSERT.

Doesn't matter 🙂

Sxooter

Yeah, seperating queries with ; and piling a bunch into one mysql_query is considered a possible security problem. I.e. a user might be able to put something like ';delete from table;

and if you haven't escaped the ' marks, and don't have magic quotes on, they could delete everything in a table.

I'm pretty sure that PHP5 will have all the different db layers working where they just chop off anything after the first semi-colon that isn't in a quoted field. Postgresql had it's behaviour changed to do this recently.

Brendan_Nolan

Yeah, after this thread started I went and did a fair amount of research on injection attacts. And as far as I can tell, it is pretty much impossible to pull it off in PHP because of magic quotes, and because mysql_query only allows one query.

I guess that this is smart. But anyone that has magic quotes off is insane, and that alone is enough to prevent an injection isn't it?

ahundiak

Can you provide a link between not using majic quotes and injection attacks? I don't really see the connection. I personally think that majic quotes (which are not part of standard sql) are a bad idea and I don't use them.I don't think I'm insane but who really knows?

Brendan_Nolan

Can you provide a link between not using majic quotes and injection attacks?

A classic example is when magic_quotes are off, and the variable has not had addslasshes() performed on it somebody can gain access to a password protected system by entering something like

anything' OR '1'='1

into a username field.

Sxooter

Originally posted by ahundiak
Can you provide a link between not using majic quotes and injection attacks? I don't really see the connection. I personally think that majic quotes (which are not part of standard sql) are a bad idea and I don't use them.I don't think I'm insane but who really knows?

Ummmm. magic quotes have NOTHING to do with the SQL standard, they're a PHP feature.

Quoting, and quote escaping however, ARE part of the SQL standard, another part MySQL only gets about half right but that's another discussion for another time.

Anyway, in order to insert the string:
My cat's claws need trimming

you'd HAVE to escape the ' in the string, as the ' character in SQL spec is the quote that goes around string literals, and the way you escape a ' is with a leading \ (backslash) so you'd get:
My cat\'s claws need trimming

to insert. If you actually tried to insert the first string without escaping you'd get a sql query that would look like this:

insert into table (field1) values ('My cat's claws need trimming');

which should generate a parse error at the s right after the second ' mark.

Sxooter

Originally posted by Brendan Nolan
I guess that this is smart. But anyone that has magic quotes off is insane, and that alone is enough to prevent an injection isn't it?

Probably. My guess is that is is also considered sloppy to allow more than one query per mysql_query since it's impossible to return a handle that explains that the first query worked, the second one failed, and the third one returned some data.

Brendan_Nolan

Originally posted by Sxooter
Probably. My guess is that is is also considered sloppy to allow more than one query per mysql_query since it's impossible to return a handle that explains that the first query worked, the second one failed, and the third one returned some data.

They could have returned them in an array...

I guess that a lot of scripts would be dangerous on servers where magic_quotes are off if you could execute multiple queries.

I guess they could even have a security check on mysql_query, where the number of queries to be executed is an argument...

But I guess that it isn't really worth it.

I am happy to accept that it is just the way it is :-)

I just wanted to make sure that I couldn't write my scripts a little better to help the server.

ahundiak

Actually, the standard SQL way to handle 's is to double them up.

Art's Theory becomes
Art''s Theory

The two ''s then get turned back into a single quote by the database.

Using an escape character i.e. Art\'s Theory is NOT standard SQL. Look it up if you don't believe me. It's something that both mysql and postgres support but many other databases do not. But they all support the doubling up process.

This is just a pet peeve of mine. PHP's popularity has resulted in thousands of developers learning SQL (which is a good thing). But many of them have learned some of the basics incorrectly (which is a bad thing).