SQL sevrer Lock table and Hollywood business

Could there be a security risk by locking a database table? Even if there is a risk, could it impact Hollywood?

Consider this.

Suppose you are running a website which provides movie tickets (Or DVD rental) booking service. As new movies are released, the demand is high, and things move quite fast. You may be having just 10 seats vacant and there are 100s of users trying to book seats. Normally you will confirm booking only when payment has been done AND seats are available. What if you charge the customer and latter on find out seats are not available. In situations where the commodity you are providing changes very frequently you have two options.

1.       Lock the database table for lets say 5 minutes and let user complete the transaction. If time has elapsed and user has not completed the request (payment), you can release the lock and make the resource (like movie tickets) available for other users.

2.       Deny the request and tell user that when she initiated the request and till the time payment was processed, the available quantity/price has changed. Based on the business, you can take decision. If movie tickets have a constant price, you can confirm the seats based on new availability.  It is being assumed that time taken to process payment is negligible.

While the first option provides more usability and less security, second option provides more security and less usability. Security folks, stop yawning:)

Now let discuss what the security vulnerability in first option is. An attacker could easily automate ticket booking request and lock the resources forever. In case you are thinking of blocking automated requests based on IP address, attacker could use freely accessible proxies to make request.

Note that in the second option I said that It is being assumed that time taken to process payment is negligible. Actually, one could still attack this with more frequent requests. Even if you are locking the data base table for few seconds, one could send large number of requests from different proxies.

Finally, lets discuss what is the best way to handle this vulnerability proactively. Other techniques like validating the card are reactive and more complex to implement.

HIP. Yea, Human Interaction Proof like Captchas. I agree that these images are quite annoying sometimes but they are definitely less annoying than getting to know you could have seen the latest hollywood movie if you had a better luck.

This brings us to the conclusion that Captchas needs to be used whenever automated requests from end users can impact your business.

Note that we have taken the example of movie tickets booking web application. The vulnerability discussed above can be used against most of the ecommerce web applications.

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS