Why is INSERT INTO followed by SELECT LAST_INSERT_ID() not outputting anything?

Question

The PHP code I have inserts the HTML form data from the previous page into the database and in the same SQL statement return the PostID back from the inserted data. The PostID column is AUTO_INCREMENTING. I have been researching this problem for a week or two now and have found no significant solutions.

<?php
include("dbconnect.php");
mysql_select_db("astral_database", $con);
session_start();

$username = $_SESSION['username'];
$forumtext = $_POST["forumtext"];
$forumsubject = $_POST["forumsubject"];

$postquery = 'INSERT INTO Forums (Creator, Subject, Content) VALUES ("$username", "$forumsubject", "$forumtext"); SELECT LAST_INSERT_ID()';
$result = mysql_query($postquery, $con);

if (!$con) {
    echo "<b>If you are seeing this, please send the information below to astraldevgroup@gmail.com</b><br>Error (331: dbconnect experienced fatal errors while attempting to connect)";
    die();
}

if ($username == null) {
    echo "<b>If you are seeing this, please send the information below to astraldevgroup@gmail.com</b><br>Error (332: Username was not specified while attempting to send request)";
    die();
}

if ($result != null) {
    echo "last id: " . $result;

    $fhandle = fopen("recentposts.txt", "r+");
    $contents = file_get_contents("recentposts.txt");
    fwrite($fhandle, json_encode(array("postid" => $result, "creator" => $username, "subject" => $forumsubject, "activity" => time())) . "\n" . $contents);
    fclose($fhandle);
    mysql_close($con);
    header("location: http://astraldevgroup.com/forums");
    die();
} else {
    die("<b>If you are seeing this, please send the information below to astraldevgroup@gmail.com</b><br>Error (330: Unhandled exception occured while posting forum to website.)<br>");
    echo mysql_error();
}

mysql_close($con);
?>

First off, the mysql_query doesn't return anything from the SELECT statement. I haven't found anything that will properly run both the SELECT statement and the INSERT statement in the same query. If I try running them in two different statements, it still doesn't return anything. I tried running the following statement in the SQL console and it ran perfectly fine without errors.

INSERT INTO Forums (Creator, Subject, Content) VALUES ("Admin", "Test forum 15", "This is a forum that should give me the post id."); SELECT LAST_INSERT_ID();

first of all, you must avoid using mysql_ in your queries, as it is declared unsafe. — ameenulla0007, Jan 19 '16 at 20:41
[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Jay Blanchard, Jan 19 '16 at 20:43
Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Jan 19 '16 at 20:43
and mysql_ doesn't support multiple queries in one statement. you need to utilize, mysql_insert_id(); after the query. to get the result. — ameenulla0007, Jan 19 '16 at 20:43

score 3 · Accepted Answer · edited May 23 '17 at 12:15

The mysql_query function does not run multiple statements

Reference: http://php.net/manual/en/function.mysql-query.php

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server ...

That's one reason your call to mysql_query isn't returning a resultset.

The most obvious workaround is to not try to run the SELECT in the same query. You could use a call to the mysql_insert_id instead.

Reference: PHP: mysql_insert_id http://php.net/manual/en/function.mysql-insert-id.php

Answers to some of questions you didn't ask:

Yes, your example code is vulnerable to SQL Injection.
Yes, the mysql_ interface has been deprecated for a long time.
Yes, you should being using either PDO or mysqli interfaces instead of the deprecated mysql_ functions.

FOLLOWUP

Re-visiting my answer, looking again at the question, and the example code.

I previously indicated that the code was vulnerable to SQL Injection, because potentially unsafe values are included in the SQL text. And that's what it looked like on a quick review.

But looking at it again, that isn't strictly true, because variable substitution isn't really happening, because the string literal is enclosed in single quotes. Consider what the output from:

  $foo = "bar";
  echo '$foo';
  echo '"$foo"';

Then consider what is assigned to $postquery by this line of code:

$postquery = 'INSERT ... VALUES ("$username", "$forumsubject", "$forumtext")';

Fixing that so that $username is considered to be a reference to a variable, rather than literal characters (to get the value assigned to $username variable incorporated into the SQL text) that would introduce the SQL Injection vulnerability.

Prepared statements with bind placeholders are really not that hard.

score 0 · Answer 2 · answered Jan 21 '16 at 20:38

$result will never be null. It's either a result handle, or a boolean false. Since you're testing for the wrong value, you'll never see the false that mysql_query() returned to tell you that the query failed.

As others have pointed out, you can NOT issue multiple queries in a single query() call - it's a cheap basic defense against one form of SQL injection attacks in the PHP mysql driver. However, the rest of your code IS vulnerable other forms of injection attacks, so... better start reading: http://bobby-tables.com

Plus, on the logic side, why are you testing for a null username AFTER you try to insert that very same username into the DB? You should be testing/validating those values BEFORE you run the query.

+1. for the reference to the classic xkcd/327. and for answering some additional questions that OP didn't ask. — spencer7593, Jan 21 '16 at 22:49

Why is INSERT INTO followed by SELECT LAST_INSERT_ID() not outputting anything?

2 Answers2