Take the 2-minute tour ×
Information Security Stack Exchange is a question and answer site for Information security professionals. It's 100% free, no registration required.

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?

share|improve this question
    
So, in the code column, the values are indeed hashed, not in clear text (as shown)? –  Henning Klevjer Oct 26 '12 at 6:55
    
yes, the codes are hashed and salted, in practice. –  maxum Oct 26 '12 at 7:03
    
Are you using a secure PRNG? –  CodesInChaos Oct 26 '12 at 9:33
add comment

2 Answers

up vote 6 down vote accepted

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.
share|improve this answer
    
Great answer! I particularly like your comments about auditing and logging systems. –  D.W. Oct 26 '12 at 6:09
    
@D.W. Cheers. I worked in retail in a past life - I can't count the number of times disputes arose during refunds due to improper logging of receipt information, where we had no way of verifying whether or not a customer actually used a voucher at a different store. Now I'm an avid supporter of logging the crap out of everything! –  Polynomial Oct 26 '12 at 6:13
    
Wow, great comments thx. Initially the pre-pay system will be site specific, codes can't shared across sites. Franchise or multiple sites will require more thought I think. The code is loaded into an app and as a customer purchases product it is deducted from the total. –  maxum Oct 26 '12 at 6:14
    
Added a few extras that I thought of on the way to work. –  Polynomial Oct 26 '12 at 7:36
add comment

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.).

share|improve this answer
    
Yes, the codes are retailer specific. I have another table that tabulates all transactions for each code. Thanks for your comments. –  maxum Oct 26 '12 at 6:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.