mysql_insert_id() always returns 0

Question

I searched 4 hours and still cannot find right answer why this function returns 0. It worked yesterday but now I made some changes to script (lot of things have been changed)... I know there are so much topics like mine but I cannot find right answer.

There are 4 text inputs on my index.php. I capture them with my update.php file (POST methods).

Here is piece of update.php

<?php
include "db.php";
connect_to_db();

require_once('recaptchalib.php');
$privatekey = "N/A";
$resp = recaptcha_check_answer ($privatekey,$_SERVER["REMOTE_ADDR"],$_POST["recaptcha_challenge_field"],$_POST["recaptcha_response_field"]);

if (!$resp->is_valid) {
    echo 'Tekst sa slike je pogresno prepisan. Vracamo Vas natrag.';
    echo '<meta http-equiv="refresh" content="5; URL=../">';

} else {
    $pic1 = $_POST["pic1"];
    $p1 = htmlspecialchars($pic1);
    $pic2 = $_POST["pic2"];
    $p2 = htmlspecialchars($pic2);
    $html = $_POST["html"];
    $h = htmlspecialchars($html);
    $link = $_POST["link"];
    $l = htmlspecialchars($link);

    $id = mysql_insert_id();

    $query = "INSERT INTO `koraci` (`id`, `p1`, `p2`, `h`, `l`) VALUES ({$id}, '{$p1}', '{$p2}','{$h}','{$l}')";
    $result = mysql_query($query) or die(mysql_error());
}

include_once("config.php");
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=Windows-1250" />
<link rel="stylesheet" type="text/css" href="style.css" />
<title><?php echo $title; ?></title>
<script type="text/javascript" src="cufon-yui.js"></script>
<script type="text/javascript"  src="font.js"></script>
<script type="text/javascript">
    Cufon.set('fontSize', '20px').replace('body');
</script>
</head>

<body>
<?php include_once("analytics.php"); ?>
<h1><?php echo $title; ?></h1>
<br />
<textarea name="one"><center><img src="<?php echo $p1; ?>" id="Like"></center></textarea>
<br />
<textarea name="two"><iframe width=800 height=500 frameborder=0 src="<?php echo $id; ?>"></iframe></textarea>
<br />
</body>
</html>

And in my db.php is:

<?php
define("DB_SERVER", "N/A");
define("DB_USER", "N/A");
define("DB_PASS", "N/A");
define("DB_NAME", "N/A");

function connect_to_db() {
    $connection = mysql_connect(DB_SERVER, DB_USER, DB_PASS) or die("There was an error connecting to the database: " . mysql_error());
    mysql_select_db(DB_NAME);
    mysql_query("SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'");
    return $connection;
}
?>

N/A means I wont share that details.

My db.sql:

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+01:00";

CREATE TABLE IF NOT EXISTS `koraci` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `p1` varchar(255) NOT NULL,
  `p2` varchar(255) NOT NULL,
  `h` varchar(255) NOT NULL,
  `l` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

INSERT INTO `koraci` (`id`, `p1`, `p2`, `h`, `l`) VALUES
(1, 'SOMETHING', 'SOMETHING', 'SOMETHING', 'SOMETHING');

So basiclly, it sends everything to db (I can see with my phpMyAdmin).

The problem is, IT ALWAYS RETURNS 0 as id that im echoing!

It doesn't show any error (PHP or MySQL).

[15:41] I have updated update.php to:

    if (!$resp->is_valid) {
    echo 'Tekst sa slike je pogresno prepisan. Vracamo Vas natrag.';
    echo '<meta http-equiv="refresh" content="5; URL=../">';

} else {
    $pic1 = $_POST["pic1"];
    $p1 = htmlspecialchars($pic1);
    $pic2 = $_POST["pic2"];
    $p2 = htmlspecialchars($pic2);
    $html = $_POST["html"];
    $h = htmlspecialchars($html);
    $link = $_POST["link"];
    $l = htmlspecialchars($link);

    $query = "INSERT INTO `koraci` (`id`, `p1`, `p2`, `h`, `l`) VALUES ({$id}, '{$p1}', '{$p2}','{$h}','{$l}')";
    $result = mysql_query($query) or die(mysql_error());

    $id = mysql_insert_id();
}
?>

And now I have this 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 "

[16:04] I can see that id isn't included in query if I set it after query. I'm talking about this:

INSERT INTO `koraci` (`id`, `p1`, `p2`, `h`, `l`) VALUES (, 'SOMETHING', 'SOMETHING','SOMETHING','SOMETHING')

Your code is vulnerable to SQL injection. Use parameterized queries instead of `htmlspecialchars`. — Marcel Korpel, Apr 20 '13 at 13:00
Well, you'll get hacked if you don't escape the data. Don't use the MySQL_* functions, either. — Connor Gurney, Apr 20 '13 at 13:02
Like I said: use parameterized queries (search for MySQLi or PDO). — Marcel Korpel, Apr 20 '13 at 13:02
The "useful anti SQL function" is called `mysql_real_escape_string()` in your case. — Sven, Apr 20 '13 at 13:05
Possible duplicate of [mysql\_insert\_id() returns 0](http://stackoverflow.com/questions/8243503/mysql-insert-id-returns-0) — Machavity, Aug 07 '16 at 04:08

score 7 · Accepted Answer · edited Jun 20 '20 at 09:12

7

Do mysql_insert_id after mysql_query.

Look the documentation here, and try to not use the mysql* functions anymore, as it is explained in the red box. There are securities issues with these function.

mysql_insert_id : Retrieves the ID generated for an AUTO_INCREMENT column by the previous query (usually INSERT).

In your code, when you do mysql_insert_id, no insert was made.

edited Jun 20 '20 at 09:12

Community

1
1

answered Apr 20 '13 at 13:01

Pierrickouw

4,644
1
30
29

2

I haven't heard about any _security_ issues of mysql* functions, they are simply not up to date anymore, lacking functionality, and the performance is also lower than with mysqli. – Sven Apr 20 '13 at 13:07
Yes, of course, but you need to be much more careful. And you can easily forget an escape or something, and break the security of your app – Pierrickouw Apr 20 '13 at 13:11
1

@GreenLeaf: It's very easy to forget escaping when using MySQLi or PDO, too. That's why I recommend using parameterized queries. – Marcel Korpel Apr 20 '13 at 13:16
@GreenLeaf: Now it says "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 '" – Anonymous Apr 20 '13 at 13:35
@AnteDražić 1: echo $query before executing it and you should understand why you have that error; 2: isn't id auto incremented? – Chris Ghenea Apr 20 '13 at 13:57
@ChrisGhenea: ID is auto incremented. – Anonymous Apr 20 '13 at 14:00
@ChrisGhenea: I echoed it and now I see... id haven't got a value. I got this output: INSERT INTO `koraci` (`id`, `p1`, `p2`, `h`, `l`) VALUES (, 'SOMETHING', 'SOMETHING','SOMETHING','SOMETHING') – Anonymous Apr 20 '13 at 14:03
`id` doesn't need a value (as it's an `AUTO_INCREMENT` column). Change the INSERT to `INSERT INTO koraci (p1, p2, h, l) VALUES ('$p1', '$p2', '$h', '$l')` (while still acknowledging the horrible SQL injection problems.) – searlea Apr 20 '13 at 14:06

score 1 · Answer 2 · edited Nov 04 '21 at 03:07

In my case, I had AUTO_INCREMENT as well but still I was getting always 0.

The core reason I found is the code used for DB connection. Previously I was using this code which was causing returning 0

// THIS CODE RETURNS ALWAYS "0"
$con = @mysql_connect($mysql_db_hostname, $mysql_db_user, $mysql_db_password, $mysql_db_database);    
if (!$con) {
   echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

Here is the CORRECT code I used and it started working well.

// THIS CODE RETURNS CORRECT VALUE OF LAST INSERTED ROW
$link = mysql_connect('localhost', 'root', '');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
mysql_select_db('openfire');

mysql_insert_id() always returns 0

2 Answers2

Linked