3

Wouldn't there be a problem with it if for example when a user clicks on a link, a new row is automatically inserted and then the php code requests the last inserted id, and at the same time another row is inserted by another user, so the returned id is actually not the one I'm expecting..?

Am I wrong? Is there a way to do the same without that 'security' hole?
(like maybe from within the prepared statement or something...)

P.S the id is automatically generated.

Thank you.

Asaf
  • 2,005
  • 7
  • 37
  • 59

3 Answers3

5

As mentioned in the manual:

LAST_INSERT_ID() (with no argument) returns a BIGINT (64-bit) value representing the first automatically generated value that was set for an AUTO_INCREMENT column by the most recently executed INSERT statement to affect such a column. For example, after inserting a row that generates an AUTO_INCREMENT value, you can get the value like this:

mysql>SELECT LAST_INSERT_ID();
    ->195

The currently executing statement does not affect the value of LAST_INSERT_ID(). Suppose that you generate an AUTO_INCREMENT value with one statement, and then refer to LAST_INSERT_ID() in a multiple-row INSERT statement that inserts rows into a table with its own AUTO_INCREMENT column. The value of LAST_INSERT_ID() will remain stable in the second statement; its value for the second and later rows is not affected by the earlier row insertions. (However, if you mix references to LAST_INSERT_ID() and LAST_INSERT_ID(expr), the effect is undefined.)

If the previous statement returned an error, the value of LAST_INSERT_ID() is undefined. For transactional tables, if the statement is rolled back due to an error, the value of LAST_INSERT_ID() is left undefined. For manual ROLLBACK, the value of LAST_INSERT_ID() is not restored to that before the transaction; it remains as it was at the point of the ROLLBACK.

So, LAST_INSERT_ID() is always transaction-safe (even though you don't use transaction).

Tiny
  • 27,221
  • 105
  • 339
  • 599
Lion
  • 18,729
  • 22
  • 80
  • 110
  • `lastInsertId()` is provided by the PDO Driver itself, and may or may not be an actual SQL query to `SELECT LAST_INSERT_ID();`. Whilst this probably remains exactly correct, it's nore certain based on just this! – Rudi Visser Jul 10 '12 at 12:00
4

The MySQL Server transfers the insert ID as part of the OK message after a successful INSERT. This ID is stored in PDO, therefore without a round-trip to the server PDO can return you the correct ID for your connection in a safe way.

Reference: http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol#OK_Packet

johannes
  • 15,807
  • 3
  • 44
  • 57
3

To counteract this you would use a transaction.

This would essentially isolate your insert from others, so as long as your Insert/lastInsertId() call is within the same transaction, it will work just fine.

Rudi Visser
  • 21,350
  • 5
  • 71
  • 97
  • So it should be something like this , right? : 1) `$dbh->beginTransaction();` 2) Insertion code + `lastInsertId()` 3) `$dbh->commit();` – Asaf Jul 10 '12 at 13:11
  • 1
    @xTCx Exactly; You also have the possibility to `rollback` if there is something wrong with what you have done in the transaction :) – Rudi Visser Jul 10 '12 at 13:13
  • 3
    This answer is misleading if not outright incorrect. The underlying functions of `lastInsertId()` are connection-aware. So it will return the last inserted ID by the connection that called it. For a better answer, please have a look at [this one](http://stackoverflow.com/questions/5835677/last-insert-id-how-it-works-at-multi-users-environment). – Adi Apr 18 '13 at 15:42
  • @Adnan They're also transaction aware, making my answer perfectly valid. I suggest you read up on transactions if you still disagree. – Rudi Visser Apr 18 '13 at 18:35
  • 3
    @RudiVisser A- I do agree with your comment. B- OP doesn't mention transactions. Advising people to implement transactions to solve a non-existing problem is ridiculous. While you do, indeed, provide a helpful advice in general, your answer is unhelpful in the context of this question. – Adi Apr 18 '13 at 18:40
  • @Adnan I disagree; He asked for a way to do it which wouldn't have the issue - I suggested that a transaction would solve the issue. It would, and so the answer is perfectly valid. – Rudi Visser Apr 19 '13 at 11:15
  • @Adi is right! There is no need to use _transaction_ to get correct `lastInsertId`. See this: https://stackoverflow.com/questions/5835677/last-insert-id-how-it-works-at-multi-users-environment – Rakesh Nov 27 '17 at 19:11
  • @Rakesh and if connections are pooled / shared so as to have 2 operations take place on the same connection? Whilst this was years ago - this is still a valid answer, proposing a valid solution to the problem. – Rudi Visser Nov 27 '17 at 19:17
  • @Rudi Visser I agree. For only shared connections, _transaction_ is a must otherwise not. – Rakesh Nov 27 '17 at 19:22