Using PHP to execute multiple MYSQL Queries

Question

I am trying to use PHP to run consecutive MYSQL statements as shown in the code snippet below (which just copies one row to another and renames the id via a tmp table).

I am getting a repeated syntax error message. I've tried numerous iterations. And the code looks like code I've researched in the PHP Manual and other myql questions on SO (which do not include the php dimension).

Can anyone shine a light on why my php syntax is incorrect?

 include("databaseconnect.php");// This obviously works. Used a zillion time

$sql ="CREATE TEMPORARY TABLE tmp SELECT * FROM event_categoriesBU WHERE id 
 = 1;";
$sql.="UPDATE tmp SET id=100 WHERE id = 1;";
$sql.="INSERT INTO event_categoriesBU SELECT * FROM tmp WHERE id = 100;";


if ($conn->query($sql) === TRUE) 
 {
  echo "Table row copied successfully. Do something with it";
 } 
 else 
 {
  echo "Error creating table: " . $conn->error;
  //close connection etc
 }

PHP Message back:

Error creating table: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE tmp SET id=100 WHERE id = 1INSERT INTO event_categoriesBU SELECT * FROM t' at line 1

use $conn->multi_query($sql) http://php.net/manual/en/mysqli.multi-query.php — Michael Eugene Yuen, May 05 '18 at 15:14

tadman · Answer 1 · 2018-05-05T15:29:37.090

10

Don't run a bunch of queries at once. Usually the success of one depends on all the other operations having been performed correctly, so you can't just bulldozer along as if nothing's gone wrong when there's a problem.

You can do it like this:

$queries = [
  "CREATE TEMPORARY TABLE tmp SELECT * FROM event_categoriesBU WHERE id = 1",
  "UPDATE tmp SET id=100 WHERE id = 1",
  "INSERT INTO event_categoriesBU SELECT * FROM tmp WHERE id = 100"
];

foreach ($query as $query) {
  $stmt = $conn->prepare($query);
  $stmt->execute();
}

Don't forget to enable exceptions so that any query failures will stop your process instead of the thing running out of control.

The reason you don't use multi_query is because that function does not support placeholder values. Should you need to introduce user data of some kind in this query you need to use bind_param in order to do it safely. Without placeholder values you're exposed to SQL injection bugs, and a single one of those is enough to make your entire application vulnerable.

It's worth noting that PDO is a lot more flexible and adaptable than mysqli so if you're not too heavily invested in mysqli, it's worth considering a switch.

edited May 05 '18 at 15:29

answered May 05 '18 at 15:20

tadman

208,517
23
234
262

PDO doesn't support multiple statements as far as I know, So are you suggesting not using multi statements at all? – jimshot May 05 '18 at 15:32
Yeah its too big a job switching to PDO now. Its a legacy that would cost a fortune to change – jimshot May 05 '18 at 15:33
2

That's a feature, not a bug. The `multi_query` function has been the culprit behind a number of high-profile security breaches in the past in part because not only can you SQL inject things, but you can add in as many statements as you want, making it the perfect playground for attackers. Don't use `multi_query` for that reason alone, but the other is you *must* check the success of each statement you run before moving on to the next. – tadman May 05 '18 at 15:34
`mysqli` can be used safely, but it's just not as friendly to use. `bind_param`, for example, needs its parameters spelled out carefully. PDO's generic `execute` function can take an array, making it much easier to use when dealing with optional arguments. – tadman May 05 '18 at 15:35

Daniele Murer · Answer 2 · 2018-05-05T15:35:02.617

1

You can't execute more than one SQL statement with query($sql), you must use multi_query($sql). Your script will then become something like this:

if ($conn->multi_query($sql) === TRUE) 
{
    echo "Table row copied successfully. Do something with it";
}

See the documentation for a complete example.

However, note that, as other users have well explained in the comments, executing multiple queries at the same time with this method can be potentially dangerous and should be avoided when possible, especially when handling user inputs.

You have better control of the execution flow if you execute one query at a time and check its result, rather than grouping all of them in a single call.

edited May 05 '18 at 15:35

answered May 05 '18 at 15:15

Daniele Murer

247
3
9

5

This funciton is extremely dangerous and should not be used. – tadman May 05 '18 at 15:16
Alternatively, you can also use `mysqli_multi_query($connection, $sql);`. [Documentation @ w3schools](https://www.w3schools.com/php/func_mysqli_multi_query.asp). – Daniele Murer May 05 '18 at 15:19
1

`mysqli_multi_query` is the procedural alter-ego of exactly the same function. You've already linked to the documentation. – tadman May 05 '18 at 15:21
@tadman why dont you tell everyone why it is extremely dangerous. Because its a concatenation? – jimshot May 05 '18 at 15:28
1

You can use `multi_query` if you properly escaped strings you can `mysqli_real_escape_string($conn, $sql)` – Precious Tom May 05 '18 at 15:36
@Precious Tom Would you elaborate with a full answer please Tom, Your solution might work best. I cant switch to PDO. (too big a job) and there is no user interaction at any stage of the mutli statement. Its a background process So it might make sense to add your suggestion to the multi_query method, – jimshot May 05 '18 at 15:56

Using PHP to execute multiple MYSQL Queries

2 Answers2

Linked

Related