MariaDB error with seemingly correct transaction SQL

Question

I'm having trouble with a specific SQL transaction, which runs fine when I run it in phpMyAdmin, but refuses to run from my own php script.

    <?php

//connect
$link = mysqli_connect('localhost', 'root', '', 'chemDB');
if (!$link) {
    echo "Error: Unable to connect to MySQL." . PHP_EOL;
    echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
    echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
    exit;
}
mysqli_select_db($link, 'chemDB');
echo "Connected successfully\n";

//open import CSV
$file = fopen('cas_list.csv', 'r');

//convert decimal comma to float
function commaToPoint($val)
{
  $val = str_replace(',', '.', $val);
  return floatval($val);
}

//cycle through csv lines
while (! feof($file))
{
  $data = fgetcsv($file, '', ';');

  $un     = strval($data[0]);
  $cas    = strval($data[1]);
  $name   = strval($data[2]);
  $m_mass = commaToPoint($data[3]);
  $m_n    = commaToPoint($data[4]);
  $m_s    = commaToPoint($data[5]);
  $m_cl   = commaToPoint($data[6]);
  $m_f    = commaToPoint($data[7]);
  $m_br   = commaToPoint($data[8]);
  $m_other= commaToPoint($data[9]);

  $query = "BEGIN;
    INSERT INTO chemical (cas, name) VALUES ('$cas', '$name');
    SELECT LAST_INSERT_ID() INTO @chemical_id;
    --followed by more SQL...
  COMMIT;";

  print_r($query);


  $result = mysqli_query($link, $query);
  if (!$result)
  {
    echo("\nError description: " . mysqli_error($link)."\n\n");
  }
}

fclose($file);
mysqli_close($link);

?>

In this case, the table `chemical` in question has 3 fields: id(int 11), cas(varchar24) and name(varchar255).

One of the input lines from the CSV is: 0072;121-82-4;Cyclotrimethyleentrinitramine, bevochtigd;222,3;6;;;;;138,24;

print_r($query) prints out the resulting query string which works fine in phpMyAdmin, but not with mysqli_query():

BEGIN;

INSERT INTO `chemical` (`cas`, `name`) VALUES ('121-82-4', 'Cyclotrimethyleentrinitramine, bevochtigd');

SELECT LAST_INSERT_ID() INTO @chemical_id;

--followed by more SQL...

COMMIT;

However, no matter what format I use I end up with the error:

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 'BEGIN; INSERT INTO `chemical` (`cas`, `name`) VALUES ('121-82-4', 'Cyclotrimet' at line 2`

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! [Don't believe it?](http://stackoverflow.com/q/38297105/1011527) — Jay Blanchard, Jan 25 '17 at 13:17
Possible duplicate of [Working with file pointers on a csv](http://stackoverflow.com/questions/41294380/working-with-file-pointers-on-a-csv) — e4c5, Jan 25 '17 at 13:18
Apart from the sql injection problem, you probably want `mysqli_multi_query` instead. — jeroen, Jan 25 '17 at 13:19

score 0 · Answer 1 · answered Jan 25 '17 at 13:39

0

mysqli_multi_query() fixed the issue, thank you. To the sql injection concerns: This script is run privately to add data to a database, not publically accessible and deleted afterwards.

answered Jan 25 '17 at 13:39

Draecko

25
4

MariaDB error with seemingly correct transaction SQL

1 Answers1