SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru
    Join Date
    Oct 2006
    Location
    Queensland, Australia
    Posts
    852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    It Happened To Me...

    Do you know how there's that small possibility between when you insert a row, and when you insert another row using LAST_INSERT_ID(), that another insert statement that's part of another action, will be executed somewhere in between the two aforementioned queries? Well, it finally happened with an eCommerce site I created a year and a half ago.

    During the processing of a order and credit card transaction, another order and credit card transaction had also began processing. What happened was the wrong order_id was given to one of the credit card transactions. Fortunately, as 'noob' as I may have been back then, I made sure that if this were to occur, it wouldn't be disastrous. Luckily the only thing it affected was the display of the credit card transaction information. The actual payment had been applied to both orders correctly.

    By the way, this website only gets about 20 orders a day at the most, and my log confirms that both orders were placed within the same second. There would have only been a milliseconds in it.

    This is just a warning to all those who think it's too rare of an occurrence for this to happen to one of their websites or applications.

    Feel free to share any similar experiences you've had, so we can all learn from others mistakes and poor practices.

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,069
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    That's exacly the reason they invented transactions and auto_increment

    Disclaimer: I'm not saying MySQL invented those techniques, merely that MySQL uses them
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Wardrop View Post
    Luckily the only thing it affected was the display of the credit card transaction information. The actual payment had been applied to both orders correctly.
    Hopefully, you haven't exposed yourself or your client to a breach of data protection law where one person;s credit card data was presented to another purchaser.

    You NEED to fix the scripts so that, as Scallio says, you use transactions and an auto_increment PK.

    USEFUL reminder though.


    bazz

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I assume he's already using AUTO_INCREMENT if he's using LAST_INSERT_ID(), since LAST_INSERT_ID() gives you the last AUTO_INCREMENT value generated on that connection.

    Since each execution of your PHP script by a different user is a different connection, LAST_INSERT_ID() will never, ever give you the ID of a row from a different user.

  5. #5
    SitePoint Guru
    Join Date
    Oct 2006
    Location
    Queensland, Australia
    Posts
    852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are you sure Dan? Like most PHP apps, only one set of credentials are used for database connections. Can you please explain in more detail why you say this can never happen? Also, just to clarify, I'm not using php's built-in mysql_insert_id() function, but rather I'm using the MySQL function as part of a query.

    Quote Originally Posted by IBazz View Post
    Hopefully, you haven't exposed yourself or your client to a breach of data protection law where one person;s credit card data was presented to another purchaser.
    Credit Card information isn't presented to the user anywhere in the front-end. Only administrators have access to the transaction details, which only include a truncated credit card number in addition to other less harmful information.

  6. #6
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yes I'm sure. You can check both the MySQL manual and the PHP manual. PHP's function just uses MySQL's function itself, it's a wrapper.

    LAST_INSERT_ID() gives you the last auto-increment ID generated by that connection. This is exactly what it's made for, so that you don't have to think about contention issues.

    http://dev.mysql.com/doc/refman/5.0/...last-insert-id
    The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.
    A client refers to a MySQL client (in this case, the PHP script), not a user.

    It is also very easy to test if you want some first hand evidence. SSH to your server and run mysql at the command line. Insert a row with an auto_increment column and then SELECT LAST_INSERT_ID(). Now open your website and submit whatever form inserts a row. Go back to the mysql client and SELECT LAST_INSERT_ID() again. It gives you the same value, the last ID generated on your connection, not the ID of the row you generated later on the website.

  7. #7
    SitePoint Guru
    Join Date
    Oct 2006
    Location
    Queensland, Australia
    Posts
    852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes you're right. I did not know that. It certainly makes sense though.

    I've just gone through my code and indeed, the part which caused this issue did not use LAST_INSERT_ID(), in fact, it looks at the current highest order number and adds 1 to it (because of the order of events). This is certainly some of the nastiest code. It's amazing how far I've come since.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •