Security concerns with pre-pay system

Question

I am looking at setting up a pre-pay system for the food retail industry. Retailers will be able to generate random 10 digit codes and supply these to customers to enter into an app to gain the value assigned to the code. My database is similar to the following

  +----+-----------+-------------+-----------+-------+
  | ID | code      | activated   | user      | value |
  +----+-----------+-------------+-----------+-------+
  |  1 | iijwd923j |     0       |           |  10   |
  +----+-----------+-------------+-----------+-------+
  |  2 | aidwd923j |     0       |           |  10   |
  +----+-----------+-------------+-----------+-------+
  |  3 | jksjwdijk |     0       |           |  10   |
  +----+-----------+-------------+-----------+-------+
  |  4 | jiejdedow |     0       |           |  10   |
  +----+-----------+-------------+-----------+-------+
  |  5 | iwqjdwdqd |     0       |           |  10   |
  +----+-----------+-------------+-----------+-------+

Lots of columns omitted as they aren't required here. When the retailer generates the code it is hashed and salted before going into the database. When the code is entered into the app their id is assigned to the code and it is activated. The retailer can also activate or deactivate the code should codes go 'missing'. This is done by the index.

Do you foresee any security issues here?

Answer 1

The basic principle looks pretty good.

A few thoughts:

• When a token is used, don't delete it. Mark it as used, store a "used" date, and delete old tokens after 6 months. Otherwise the same token serial number might be re-created by accident, because the system has no record of it ever existing.
• Can any single instance of this prepay system run across multiple physical sites? If so, you're going to have to take synchronisation of data into account, otherwise a user might re-use their token on another machine.
• If each vendor system needs to communicate to some kind of base station, they'll need a secure way of doing so. Physical security in the way of an ethernet cable is OK in small stores, but it pays to be secure - make sure you implement SSL for such traffic, with client and server certificates installed on the appropriate machines.
• You'll probably want to tie each code to a specific vendor, whose ID is randomly generated. Otherwise a store assistant could print off a bunch of codes and go shopping at another vendor that uses your system. Make sure the ID generation uses a high quality random number generator!
• Make sure appropriate security provisions are taken for the system that runs the database. Lock the machine down with group policy, enable and properly configure Windows Firewall, install an anti-malware package (Microsoft Security Essentials is good if you don't like the idea of running 3rd party solutions), etc.
• Properly configure your database server software. This includes making sure that it only listens locally (don't bind to 0.0.0.0) or only uses local pipes if possible. You should also use strong credentials for the server, and minimal privileges. If at all feasible, reduce all queries down to stored procedures and only allow the account to access those procedures - give it no privilege on any table.
• Make sure you implement an auditing system that logs the cashier that performed the transaction, the time, the order ID that it belonged to, the total price of the transaction at the time (product prices change!), the store ID, and the plaintext code that was used. You don't need to secure the code at this point - it's useless.
• In the case of a refund where the customer is to be given back their token, generate a new token. Don't ever re-use the old token number.

Answer 2

Looks like a good approach to me.

You might consider whether you foresee the need for dispute resolution. Depending upon your application, maybe it's not necessary, but I think dispute resolution and billing account for a substantial portion of the complexity and challenge of many payment systems.

I think you may want to associate each code with a specific retailer, so it can only be redeemed at that retailer, and so you can run reports on a per-retailer basis (e.g., number of codes created, number redeemed, total value outstanding, etc.).