DBI Insert Unique Customer ID

kalchas has asked for the wisdom of the Perl Monks concerning the following question:

Greetings, Monks. I'm trying to create a script to insert new customer information into a mySQL database and at the same time generate a new unique and random customer number. My difficulty is in trying to figure out how to: 1) Check if the customer number really is unique and 2) Try a different number (the original number + 1 is what I've tried) and keep on trying until I hit upon one. Is there a good tutorial out there that anyone's seen on how to do this? I've been all over Google and not come up with a shoe that fits. I'll also post what I've tried so far in case I'm just making a simple mistake that can be fixed. The relevant areas are marked with a lot of ##s. I'm still learning, be gentle :-) Cheers, K.

#!C:\Perl\bin\perl.exe -wT
##
use strict;
use warnings;
use CGI;
use CGI::Carp qw/fatalsToBrowser/;
use File::Basename;
use DBI;

my $driver = 'mysql';
my $database = 'mydatabase';
my $dsn = "DBI:$driver:database=$database";
my $userid = 'foo';
my $password = 'bar';
my $dbh = DBI->connect($dsn, $userid, $password )||die $DBI::errstr;

my $query = CGI->new;


######################################################
######################################################
#########This is where I generate the start number##########
my $basisNumber = int(rand(999));
my $customerNumber = $basisNumber + 2000;


########################################################
########################################################
#########Here I get ready to check if it's in the db########
my $customerCheck = $dbh->prepare("SELECT CustomerNumber FROM Customer
+List");
$customerCheck->execute();

my(@customerMatrix)=();
while(my @customerArray = $customerCheck->fetchrow_array())
  {
  push(@customerMatrix, [@customerArray]);
  }
$customerCheck->finish();


#####################################################
#####################################################
###Here I get ready to do the check in a subroutine#######
my $checkResult = checkValue(@customerMatrix, $customerNumber);
####Here I check it, and increment it if it's not unique, then check a
+gain#########
while($checkResult==1)
{
$customerNumber ++;
return $checkResult;
}

my $companyName = $query->param('cName');
my $billingStreetAddress = $query->param('bStreetAddress');
my $billingCity = $query->param('bCity');
my $billingState = $query->param('bState');
my $billingZip = $query->param('bZip');
my $billingPhone = $query->param('bPhone');
my $billingFax = $query->param('bFax');
my $shippingStreetAddress = $query->param('sStreetAddress');
my $shippingCity = $query->param('sCity');
my $shippingState = $query->param('sState');
my $shippingZip = $query->param('sZip');
my $shippingPhone = $query->param('sPhone');
my $shippingFax = $query->param('sFax');
my $email = $query->param('email');
my $contactPerson = $query->param('cPerson');


my $newCustomer = $dbh->prepare("INSERT INTO CustomerList (CustomerNum
+ber, CompanyName, BillingStreetAddress, BillingCity, BillingState, Bi
+llingZip, BillingPhone, BillingFax, ShippingStreetAddress, ShippingCi
+ty, ShippingState, ShippingZip, ShippingPhone, ShippingFax, Email, Co
+ntactPerson) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
+);
$newCustomer->execute($customerNumber, $companyName, $billingStreetAdd
+ress, $billingCity, $billingState, $billingZip, $billingPhone, $billi
+ngFax, $shippingStreetAddress, $shippingCity, $shippingState, $shippi
+ngZip, $shippingPhone, $shippingFax, $email, $contactPerson);
$newCustomer->finish();
$dbh->commit;

print $query->redirect('http://localhost/mysite.com/thankYou.html');


####################################################
####################################################
########This is the subroutine that does the check######
 sub checkValue
  {
  for(@customerMatrix)
    {
    return 1 if $_ eq $customerNumber;
    }
  return 0;
  }
[download]

Comment on DBI Insert Unique Customer ID Download Code

Replies are listed 'Best First'.
Re: DBI Insert Unique Customer ID by pc88mxer (Vicar) on Apr 08, 2008 at 23:14 UTC
`mysql` has an `AUTO_INCREMENT` attribute that can be used for this very purpose. Just declare your customer id field to be of type `INT PRIMARY KEY AUTO_INCREMENT`. If you insert `NULL` into that field, mysql will assign a unique id for that row, and you can retrieve the id using the `LAST_INSERT_ID()` function. For more info, see: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html	[reply] [d/l] [select]
Re^2: DBI Insert Unique Customer ID by kalchas (Acolyte) on Apr 09, 2008 at 00:49 UTC
I read somewhere in my searching that it was bad to use MySQL generated autoincrements as customer numbers. I believe the reason was that they're too easy to guess, which I can see. Cheers, K.	[reply]
Re^3: DBI Insert Unique Customer ID by ikegami (Patriarch) on Apr 09, 2008 at 06:02 UTC
Sounds like you have a major problem if knowing someone else's customer id is a problem! It's only dangerous to know someone's id when knowing someone's id is sufficient to impersonate the person. That's usually the case for session ids, and is inexcusable* anywhere else. And if you think 5 digits would be sufficient in that case, you're quite mistaken! It would take but seconds to find a valid id! (50 attempts on average if you have 1000 customers.) Session ids are usually 8 times longer (128 bit rather than 16). * — And even then, the risk is mitigated by binding the session id to the user's IP address and expiring the session id on short order.	[reply]
Re^3: DBI Insert Unique Customer ID by Your Mother (Archbishop) on Apr 09, 2008 at 05:37 UTC
I think you probably do want the AUTO_INCREMENT though. The thing about the "easy to guess" is that it only matters if your design or security is fundamentally flawed. Any number of ecommerce sites use numeric Ids for customers. What does knowing that 321299 is a customer Id buy you? Nothing unless the site in question is inherently hackable. You can also create your own sequences with most DBs too so that the "numbers" can look like anything you like. The nice thing about GUIDs is that they can be shared between applications whereas DB Ids are purely local. I have considered using them so that user accounts or content (or even versioned code, CSS, etc) could be packaged as data-based plugins; nothing to install, just insert the SQL. I haven't got around to it ... but one of these days.	[reply]
Re: DBI Insert Unique Customer ID by graff (Chancellor) on Apr 09, 2008 at 01:14 UTC
If I understand your question, the advice from Your Mother might be just what you need -- unless you were actually looking for a numeric-string type of value, such as might be used as a "PIN" (personal identifying number) by the person to whom the number is being assigned, in which case as 128-bit (~~16-character~~ 16-byte, 22-character if base64-encoded) UUID/GUID kind of value might be inappropriate. Since you specifically mention wanting a "unique but random" number, I assume the other replies about using an "autoincrement" field would be inappropriate as well, since these are not at all random. A solution I've used in the past (admittedly a kluge with a minor limitation) is to generate a randomized list of numbers first, store that list in a separate table, then select from it sequentially as needed. Include a "status" field along with the randomized numeric value field, and hand out random but unique numbers like this: `my ($pin) = $dbh->selectrow_array( "select pin_value from pins where s +tatus is null limit 1 for update" ); $dbh->do( "update pins set status='used' where pin_value=$pin" );` [download] The "for update" in the select is supposed to keep some other query from fetching the same row until the update has been done. The limitation, of course, is that you need to make sure the pins table doesn't run out of available rows. It's easy enough to solve with a cron job or something that checks the number of unused rows at regular intervals and adds more whenever it gets too close to zero. In this case, the "add_pins" process just needs to make sure that only adds numbers that haven't been used yet, and that's pretty easy using a hash. For that matter, it's not hard to make sure your initial sequence of random numbers is big enough. This approach is really nice if you want to put any sort of constraint on the range of possible numeric values being handed out (e.g. they should all be 6-digit, not begin with 0, not contain "666", or whatever).	[reply] [d/l]
Re^2: DBI Insert Unique Customer ID by Phatfingers (Initiate) on Apr 09, 2008 at 06:25 UTC
I've got to concur with using the auto_increment feature of the database to determine the unique ID rather than trying to solve this in Perl from the most recent query. The database app is able to avoid the race condition of two users creating accounts at the same time with both instances coming up with the same number. Listen to Your Mother. Security by obscurity is mostly self-delusion. If user 24000 is able to access someone else's information by typing 24001 in the URL where they see their 24000, then your security problem isn't that 24001 is easy to guess, it's that your guard is down.	[reply]
Re: DBI Insert Unique Customer ID by Your Mother (Archbishop) on Apr 08, 2008 at 23:10 UTC
I think you didn't try `perl "unique id"` in your Google searches. :) Try Data::UUID or Data::GUID; (don't know anything about Data::Uniqid, but it's there too). Update: MySQL and others have built-in support for generating them.	[reply] [d/l]
Re^2: DBI Insert Unique Customer ID by kalchas (Acolyte) on Apr 09, 2008 at 01:02 UTC
Thanks for the response... UUID and GUID don't seem to do exactly what I want, though. I'm looking for a 5 digit number that's unique to the database. It looks like UUID and GUID do something a bit different, creating numbers unique to a URL. I'd think there'd be a simple way to generate the customer numbers, then check if they're already in the DB. Cheers, K.	[reply]
Re: DBI Insert Unique Customer ID by mhearse (Chaplain) on Apr 08, 2008 at 23:49 UTC
As has already ben stated, the autoincrement key is the best approach. If you are forced to increment it yourself: `select max(CustomerNumber + 1) from CustomerList` [download] Here is an easy way to get the newly created/incremented key using DBI, if you are working with an autoincremented field. Ater your insert, do this: `my $customer_number = $dbh->{'mysql_insertid'};` [download]	[reply] [d/l] [select]