Sanity check - unique online "coupon code"?

[swiers]

I was recently tasked with coming up with a system that would control user access to a variety of online products (e-books). The desired method was to have user accounts and "book codes". Each book code would be unique, and be associated with one of the publishers book and (once redeemed) a single user. Security wise, they needed to be assuredly unique but also non-sequential (and otherwise very hard to guess). As a usability requirement, I decided book codes should also be easily entered without resorting to copy-paste, contain only url-safe characters, and be as short as security allowed for.

Here's what I came up with (code is from a PHP class, using Codeigniter framework). It may do some extra work that's not strictly needed from a crypto standpoint, and implement some "security by obscurity" that's also not strictly needed but the basic question stands. Basic idea is that each code contains a base 64 encoding of its id separated by a non-base64 character from base 64 version of a sha1 hash that combines (among other things) that same ID. My presumption is the first part is assuredly unique (and remains so because of the separator) and the second part is (very) hard to guess, but (potentially) not unique. The code table has a 20 bit id field, so I figure at most I'd need 4 characters (plus the separator) to make each code unique, with a minimum (given my algorythms) of 2 plus the separator; the remaining characters are devoted just to making the code harder to guess. In this case, I'm using codes with 16 characters, providing 7-9 characters of "cryptography" (IE, a minumum of 42 bits) in each code.

Does this seem a reasonable and reliable solution? The codes in the db look good, but that's a rather small sample base...

    /**
     * creates unique new book codes and inserts them into user_book_code table
     * code is based off book table id and current max value of user-book-codes id field
     * returns true (inserts worked) or false (at least one failed insert or code creation attempt)
     *
     * @param int $id - book's id in books table
     * @param in $num - number of codes to write
     */
    public function make_book_codes($id,$num){
        if ( (int)$id != $id) return false;
        if ( (int)$num != $num) return false;

        $ok = true;
        $this->load->library('encrypt');
        $query = $this->db->query('SELECT MAX(id) AS id_max FROM user_book_code');
        $u= $query->row()->id_max;
        for ( $i=0; $num >= $i ; $i++ ) {  // changed to logical equivalent for posting reasons to be HTML safe, had less than symbol
            if (!$ok) continue;
            $codeA = $this->acid64hex( dechex($u++) );
            $hash = $this->encrypt->sha1($u.$this->salt.$id);
            $codeB = $this->acid64hex( $hash );
            $code = strrev(substr( "$codeA+$codeB" , 0, $this->codeSize ));
            $ok = $ok && $codeA && $codeB && $this->db->query(" INSERT INTO user_book_code (book_id,code) VALUES ($id, '$code') ") ;
        }
        if ($ok) {
            return $this->get_book($id);
        }
        return false;
    }

    /**
     * Returns a url-safe shortened version of a hex hash
     * which "dissolves" the base 16 hex number into a base 64 encoding
     * returned string will have 2 chars for every 3 (or fraction of) hex digits in hex hash
     * returns false if hash is not hex value convertable string
     *
     * @param str $hash
     */
    public function acid64hex($hash){
        $return = '';
        $hash = strtolower($hash);
        $acid64 = '-qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM1234567890_';
        $hashSmoke = array
            ('1','2','3','4','5','6','7','8','9','0','a','b','c','d','e','f');
        $badHash = str_replace($hashSmoke,'',$hash);
        if ($badHash){
            return false;
        }
        $hashTrip = str_split($hash,3) ;
        foreach ($hashTrip as $trip){
            $pair10 = hexdec($trip);
            $rA = $pair10%64;
            $rB = floor($pair10/64);
            $return .= $acid64[$rA] . $acid64[$rB];
        }
        return $return;
    }

And yes, I did post this partly just because I had fun with function / variable names.

[apapadimoulis]

No, this is overly complex and not very user friendly. 16 case-sensitive alpha-numeric characters is stupid: is it a 0 or a O, a 1 or a l, etc.

Credit card numbers are 16 digits and encode both a 4-digit bank code and a check digit. That's enough for the entire world's credit card needs, and that's even considering that many people have multiple cards. A few years back, I remember seeing somewhere (Wits & Wagers game) that 3b+ cards were issued in the US alone... so even this is overkill for e-books.

This is a simple problem to solve. Step away from the code and think about it.

How many books could be sold and how hard should it be to guess? Let's be really generous and say a million books and one-in-a-million. So multiply the two numbers together and you get a trillion; subtract one and you get a nice 12-digit number that can accommodate an impossible number of sales with an impossible number of guesses, and there ya go. Slap in two dashes to make it easier to type in (3 blocks of 4), and now you have something that's simple and user friendly.

[ekolis]

Seems like GUIDs would fit your requirements - dunno if PHP can generate them (they're more of a .NET thing, though Java IIRC has something similar)...

Or perhaps you could use QR codes, if you want to be trendy, but that would require the users to have smartphones...

[Medezark]

I don't think the solution requested is the most practical and adds a level of complexity without really addressing the main reason for the request;

Have a table in your source database for "user purchases";  The "book" table should have a unique identifier for each book; the user purchases table would have the users ID and a row for each book they have purchased; on a user library page you could then present a list of "My Books" by iterating through the user purchases table based on the authenticated user's ID; the user's only/primary means of reaching their purchases would be through this menu.

Put as much of this in server side code as necessary so that the user can't just enter any random query string into the address bar to get to another user's or a book they have not purchase.

This lends itself also to "trial periods" or "rentals"  (Add a Trial End Date field and a Read Online page for any "expiration" enabled items.)

[swiers]

That's effectively what's done. There's just three tables; users, books, and codes. Initially the codes table just lists book ID and code, but it has a null field for user ID; when a code is used, the user ID for the redeeming user goes in there. Can't really get much simpler and have a many-many relationship without violating normal form.

I like the suggestion about expiration dates- trial access is a great idea, probably something the publisher never considered. That'l make a great upsell and let my company bill some more hours. :)

I'll probably go with something along the lines of what Alex Papadimoulis suggested. I agree, the original security specs were overkill, and a simple 12 (maybe 16) digit number fits the bill just fine, and can be generated with very minor code adjustment.

Thanks all!