How to fix syntax error in PHP when statement works fine in MySQL?

Question

I'm using PHP/MySQL to store data collected from a web page. I'm getting

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 narrative_photos VALUES (`filename`, `narrative_id`) VALUES ('ash_02

When I take the statement produced by PHP and paste it into the MySQL console, the statement works fine.

Here's the PHP code:

foreach ($files['pictures']['final_name'] as $key => $final_name) {
    $sql .= "INSERT INTO narrative_photos ";
    $sql .= "(`filename`, `narrative_id`) ";
    $sql .= "VALUES (";
    $sql .= "'" . db_escape($db, $final_name) . "', ";
    $sql .= "'LAST_INSERT_ID()'); ";
}

It produces something that looks like this:

INSERT INTO narrative_photos VALUES (`filename`, `narrative_id`) VALUES ('ash_020819-140257.png', 3);

If I paste that into MySQL it works. But if I comment out the PHP code and substitute:

 $sql .= "INSERT INTO narrative_photos VALUES (`filename`, `narrative_id`) VALUES ('ash_020819-140257.png', 3);";

it continues to throw the MySQL error.

I've been playing with this for a couple of hours and I can't figure out where my mistake is. I would appreciate a second set of eyes. Thanks!

EDIT: Here's the entire function for context.

function insert_narrative($narrative, $files) {
    global $db;

    $sql = "INSERT INTO narratives ";
    $sql .= "(date, positive_thing, what_you_did, goals, plan, entered_by, library_id) ";
    $sql .= "VALUES (";
    $sql .= "'" . db_escape($db, $narrative['sqldate']) . "', ";
    $sql .= "'" . db_escape($db, $narrative['positive_thing']) . "', ";
    $sql .= "'" . db_escape($db, $narrative['what_you_did']) . "', ";
    $sql .= "'" . db_escape($db, $narrative['goals']) . "', ";
    $sql .= "'" . db_escape($db, $narrative['plan']) . "', ";
    $sql .= "'" . db_escape($db, $narrative['entered_by']) . "', ";
    $sql .= "'" . db_escape($db, $_SESSION['library_id']) . "'";
    $sql .= "); ";

    if (!empty($files['pictures']['final_name'])) {
        foreach ($files['pictures']['final_name'] as $key => $final_name) {
            $sql .= "INSERT INTO narrative_photos ";
            $sql .= "(`filename`, `narrative_id`) ";
            $sql .= "VALUES (";
            $sql .= "'" . db_escape($db, $final_name) . "', ";
            $sql .= "LAST_INSERT_ID()); ";

        }
    }
    $result = mysqli_query($db, $sql);
    if ($result) {
        return true;
    } else {
        echo mysqli_error($db);
        db_disconnect($db);
        exit;
    }
}

EDIT #2: I just realized that independent of the syntax error my approach isn't going to work because that LAST_INSERT_ID is probably going to pick up the ids for each of those inserts instead of just using the id from the main table. I've modified the function but I'm still getting a syntax error at SET @narrative_id. Here's the code.

$sql = "INSERT INTO narratives ";
$sql .= "(date, positive_thing, what_you_did, goals, plan, entered_by, library_id) ";
$sql .= "VALUES (";
$sql .= "'" . db_escape($db, $narrative['sqldate']) . "', ";
$sql .= "'" . db_escape($db, $narrative['positive_thing']) . "', ";
$sql .= "'" . db_escape($db, $narrative['what_you_did']) . "', ";
$sql .= "'" . db_escape($db, $narrative['goals']) . "', ";
$sql .= "'" . db_escape($db, $narrative['plan']) . "', ";
$sql .= "'" . db_escape($db, $narrative['entered_by']) . "', ";
$sql .= "'" . db_escape($db, $_SESSION['library_id']) . "'";
$sql .= "); ";
$sql .= "SET @narrative_id = LAST_INSERT_ID()";

if (!empty($files['pictures']['final_name'])) {
    foreach ($files['pictures']['final_name'] as $key => $final_name) {
        $sql .= "INSERT INTO narrative_photos ";
        $sql .= "(`filename`, `narrative_id`) ";
        $sql .= "VALUES (";
        $sql .= "'" . db_escape($db, $final_name) . "', ";
        $sql .= "@narrative_id); ";

    }
}

I think you don't want these quotes 'LAST_INSERT_ID()' in fact if you can remove that completely. As the key is automatically done on inserts. Basically your passing a string to the Pkey of the table. Which is why you get an error. — ArtisticPhoenix
– ArtisticPhoenix, Commented Feb 8, 2019 at 22:14
Removing the quotes doesn't get rid of the error. The reason for the LAST_INSERT_ID() is that I have a SQL statement preceding this that writes to a related table. — rancidamoeba
– rancidamoeba, Commented Feb 8, 2019 at 22:28
Well LAST_INSERT_ID is a MySql function, it was like putting quotes around DATE(datetime) or WHERE. — ArtisticPhoenix
– ArtisticPhoenix, Commented Feb 8, 2019 at 22:33
How are you trying to run the query - as it's potentially a multi-query? — Nigel Ren
– Nigel Ren, Commented Feb 8, 2019 at 22:36
@rancidamoeba Can you just echo $sql; just before the closing for loop and see what you are getting as sql. — G_real
– G_real, Commented Feb 8, 2019 at 22:37

mickmackusa · Accepted Answer · 2019-02-09 09:33:47Z

2

If you're trying to make one query to insert all the values, you need to not include the INSERT part of the query each time, just adding a new set of values instead. You could use something like this. Note that LAST_INSERT_ID() should not be enclosed in quotes, as that will insert the literal string "LAST_INSERT_ID()" instead of the value.

$sql  = "INSERT INTO narrative_photos (`filename`, `narrative_id`) VALUES ";
$values = array();
foreach ($files['pictures']['final_name'] as $final_name) {
    $values[] = "('" . db_escape($db, $final_name) . "', LAST_INSERT_ID())";
}
$sql .= implode(', ', $values);

Notes

I'm assuming that you actually want all of these filenames to end up with the same value in narrative_id, which is going to link back to another table.

Although from the look of it these values have been filtered already (I presume they are actual system filenames), the code is still potentially vulnerable to SQL injection. This question and this question offer some good advice as to how you can use prepared statements with arrays of parameters.

edited Feb 9, 2019 at 9:33

mickmackusa♦

49.2k13 gold badges98 silver badges165 bronze badges

answered Feb 9, 2019 at 0:06

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Strawberry Over a year ago

The final_name bit seems prone to injection

mickmackusa Over a year ago

$key is not used. And this is a good opportunity to promote prepared statements.

mickmackusa Over a year ago

Wait a sec. How is this expected to work? (The OP's coding attempt, I mean.) Is this expected to keep catching the new LAST_INSERT_ID from the previous inserted row? I am concerned that this is going to store skewed/unintended rows.

Nick Over a year ago

@mickmackusa my assumption was that all the files are supposed to be associated with an id from an insert in another table (given the name of the field being narrative_id). That's why I went for this approach as they will all get the same value.

mickmackusa Over a year ago

I'm not blaming you on this. I just don't know if this is going to work as planned/intended. Think about what LAST_INSERT_ID() does. Am I crazy here? I haven't run any local tests to confirm my suspicions. I know it is a common habit to declare $values = array(); but if there are no rows, there shouldn't be a query executed.

EternalHour · Accepted Answer · 2019-02-08 23:58:24Z

0

I don't understand why you concatenating each line to a variable instead of just doing an entire statement. Also, in your examples you have used VALUES twice which is incorrect. I can't understand why you are creating the SQL statement inside a loop but you never execute it, which you need to. I don't know what API you are using but here's an example.

if (isset($files['pictures']['final_name'])) {
  foreach ($files['pictures']['final_name'] as $key => $final_name) {
    $sql = "INSERT INTO narrative_photos (`filename`, `narrative_id`) 
            VALUES ('".db_escape($db, $final_name)."', LAST_INSERT_ID())";
    if (!$mysqli->query($sql)) {
      echo "SQL failed: (".$mysqli->errno.") ".$mysqli->error;
    }
  }
} else {
  echo "final name does not exist";
}

edited Feb 8, 2019 at 23:58

answered Feb 8, 2019 at 23:46

EternalHour

8,6776 gold badges40 silver badges58 bronze badges

Collectives™ on Stack Overflow

How to fix syntax error in PHP when statement works fine in MySQL?

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related