[SOLVED] mysql_query() and union - unsupported?

[GingerRobot]

Hopefully just a quick question. Would i be right in thinking that the UNION command does not work in a query made using the mysql_query() function?

 

I see that multiple queries are unsupported, and i guess using UNION is like having a multiple query.

 

If someone could just confirm, i'd be grateful.

 

Ben

[per1os]

UNION is supported, subquerys are not in MySQL versions <= 3.x (maybe even 4)

 

But UNION should be supported.

[GingerRobot]

Are you sure? If i try this query in phpMyAdmin:

 

SELECT `score` FROM `ranking` WHERE `id`='3' UNION SELECT `score` FROM `ranking` WHERE `id`='5'

 

Then i get an output showing two scores:

score 

96

83

 

However, if i use the following php script:

 

<?php
include('test_db_connection.php');
$result = mysql_query("SELECT `score` FROM `ranking` WHERE `id`='3' UNION SELECT `score` FROM `ranking` WHERE `id`='5'")or die(mysql_error());
print_r(mysql_fetch_assoc($result));

?>

 

Then i only get the first score as an output:

Array ( [score] => 96 )

 

I appreciate its a highly pointless query - this is just for testing purposes.

 

[Barand]

mysqli supports multiple queries.

 

Subqueries came in in MySQL 4.1.

 

UNION has been around even longer.

[GingerRobot]

Oh dear. Obviously not thinking clearly. Of course, the way i was outputting the results would only show the first row. Whoops!

 

Thanks for your help.

[per1os]

You do know that phpMyAdmin uses mysql_query right?

 

Anyhow the issue is you are not looping through the rows.

 

<?php
include('test_db_connection.php');
$result = mysql_query("SELECT `score` FROM `ranking` WHERE `id`='3' UNION SELECT `score` FROM `ranking` WHERE `id`='5'")or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
      $rows[] = $row;
}

print_r($rows);
?>

[GingerRobot]

Yeah, i figured that out. Still cant believe i did that

 

You do know that phpMyAdmin uses mysql_query right?

 

I did wonder that. I guess phpMyAdmin breaks up any multiple queries then? It appears to execute those ok.

[per1os]

How are you using multiple queries in phpMyAdmin?

 

If they are seperated by a comma, that would work in the script too. If you are using php5 and mysqli that might also explain it.

[GingerRobot]

Separated with a semi-colon. And i just tried it without the mysqli extension. Still manages to do multiple queries.

[per1os]

You can run multiple queries via mysql_query with the semi-colon operated.  Thats not a subquery.

 

SELECT * FROM table_name WHERE table_2_id_col IN(SELECT id FROM table2 WHERE x=1);

 

That is what mysql <= 3.x cannot handle.

 

SELECT * FROM table_name WHERE table_2_id_col = 1; SELECT * FROM table_name2;

 

"Should" work. That is why SQL Injection is such a worry because if someone did this:

 

$_GET['id'] = "' OR 1; DELETE FROM table_name; SELECT * from table_name WHERE id = '1";
SELECT * FROM table_name WHERE table_2_id_col = '$_GET['id']'

 

Could potentially trash your table.

 

But as far as I know the ; separator should work for ya, again I am unsure but it should.

[GingerRobot]

Well, if i run the following code:

 

<?php
include('test_db_connection.php');
$sql = "INSERT INTO `ranking` VALUES ('','200');INSERT INTO `ranking` VALUES ('','200')";
mysql_query($sql) or die(mysql_error());
?>

 

Then i get the error: 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 `ranking` VALUES ('','200')' at line 1

 

And the php manual does say that multiple queries are not supported with mysql_query()

[per1os]

Interesting. I could have sworn I have done that before, but maybe I am wrong.

 

I will have to look more into it. I know something similar is possible, cause if it wasn't SQL Injection wouldn't be a huge issue as it is.