secure primary key's

robertaccettura

I've got a database that will scale to many rows. Say the structure is as follows:

| row_id | row_name | row_data |

for security reasons, row_id shouldn't be a sequential int, since there is proprietary information being leaked (how many records). That's a big bad problem.

So my question is:

what do you use as a primary key? How do you keep things fast, scalable, and clean? If you just rand() you have the potential for a collision. What is your technique for avoiding this?

I'm sure I'm not the only one who finds autonumbering to be evil and unacceptable.

mrhappiness

Originally posted by robertaccettura
for security reasons, row_id shouldn't be a sequential int, since there is proprietary information being leaked (how many records). That's a big bad problem.

why?

I'm sure I'm not the only one who finds autonumbering to be evil and unacceptable.

who else does?

i use auto_increment and i don't feel unsecure.
where do you populate this value?

robertaccettura

I'll give a generic example:

if you table is your user table, and your user has 2 accounts (one 6 months ago, and signed up another recently). They can tell how many clients you have.

A competitor does that, and they get decent information on your internal business.

Transaction #'s, etc. etc.

That's insecure from a business perspective. May be safe technologically, but it's a business disaster.

AstroTeg

Some thoughts:

Maybe think about seeding the initial auto-index number and have it start out somewhere randomly (like 11557 or something like that). It wouldn't be perfect, but at the same time, if no one knows what number is the starting number, then it'll be a bit more difficult to determine a row count.

In the Microsoft world (ASP for example), there was a way to generate GUIDs (I think that's what they were called). They're a unique ID randomly generated based on time (I think) and some other factors. I think they are 128 bit, but don't quote me on that. They are alpha-numeric. Because they're a string, they'd actually slow down your queries a bit because strings are harder to search on then integers. I don't know if PHP can directly generate guids. PHP definitely should be able to if you use a COM object that generates the IDs. Of course, this doesn't port to *nix very well. It may be possible to generate your own guids.

Personally, I'd try going with the seeding before spending a lot of energy on other approaches.

mrhappiness

Originally posted by robertaccettura
That's insecure from a business perspective. May be safe technologically, but it's a business disaster.

That's possible only if you tell them the highes value of your user table or if yu at least tell them the value of their own entriy's primary key

Why should you do so?
Why not use auto_increment internally and generate a customer-number or anything similar using the cureent year for example?

id     |  cust_no
  1784 | 20041246
  1785 | 20041247
  8794 | 20051587

which woulkd (technically) mean that customer 20051587 is the 1587 you aquired in 2005 and he has the id 8794 internally

or you might try

md5(microtime());

as kind of GUID (global unique identifier)

Where do you populate the value of the primary key itself and why?

robertaccettura

Originally posted by mrhappiness
That's possible only if you tell them the highes value of your user table or if yu at least tell them the value of their own entriy's primary key

If they just signup again, or complete a transaction, they get the next incriment in theory.

It's easy recon. People do this all the time to their competitor. Get good knowledge on people's business.

Do it a few times in a day to see the usage rates, etc.

Dishonest? Yea, technically. But it's part of life. It's an insecure design.

Why should you do so?
Why not use auto_increment internally and generate a customer-number or anything similar using the cureent year for example?
id     |  cust_no
  1784 | 20041246
  1785 | 20041247
  8794 | 20051587
which woulkd (technically) mean that customer 20051587 is the 1587 you aquired in 2005 and he has the id 8794 internally
[/B]

Why keep both? cust_no has to be unique anyway (otherwise it's completely useless. what purpose does id serve?

The question is how to generate a good unique cust_no. So that there's no data being leaked (signup date/time, # of current customers, etc.)

Where do you populate the value of the primary key itself and why?

Not doing it yet, this is called research. 😃

Better than writing an insecure system by using auto num.

robertaccettura

I thought about using an md5 of the time or something... but the following became an issue:

Is there a performance issue by using a varchar(32) and an md5 as a primary key to search by, rather than an int?

robertaccettura

Originally posted by AstroTeg
Some thoughts:

Maybe think about seeding the initial auto-index number and have it start out somewhere randomly (like 11557 or something like that). It wouldn't be perfect, but at the same time, if no one knows what number is the starting number, then it'll be a bit more difficult to determine a row count.

It's a good idea, but runs into 1 issue:

say I'm selling something. And I'm recording sales numbers.

Buy a widget today.

Buy a widget next week.

Buy one a week after.

Diff between the 3 and get my average # of widgets sold/week.

From a security perspective, I now know people's sales numbers (can guess them). That will freak out many tin-foil-hat people.

Buy from a major retailer (amazon, bn.com, apple, dell), you get some unique token.

Package from FedEx or UPS? You get a unique tracking number. It's not sequential. You can't just +1 and get the next package to ship.

mrhappiness

Originally posted by robertaccettura
If they just signup again, or complete a transaction, they get the next incriment

if you tell them...

Why keep both? cust_no has to be unique anyway (otherwise it's completely useless. what purpose does id serve?

beside of SAP almost all cust_nos include letters as well
comparing letters is slower than comparing int => read primary key (int) from unique id (cust_no)

The question is how to generate a good unique cust_no. So that there's no data being leaked (signup date/time, # of current customers, etc.

function get_custno($length = 8) {
  $length = max(0, (int)abs($length));
  $md5 = md5(microtime());
  $md5 = array($md5{rand(0, 31)}, $md5{rand(0, 31)}, $md5{rand(0, 31)});
  $cust_no = '%s%s-%s%s%s';
  return sprintf($cust_no, $md5[0], date('Y'), $md5[2], substr(md5(microtime()),  rand(0, 23), $length), $md5[1]);
}

unpredictable enough?
just a little attempt

mrhappiness

oh, if you're in GUIDs...

function guid() {
    $address = array(
        'name' => isset($_ENV['COMPUTERNAME']) ? $_ENV['COMPUERNAME'] : 'localhost',
        'ip' => $_SERVER["SERVER_ADDR"]);
    $guid = implode('/', $address);
    list($usec, $sec) = explode(" ",microtime());
    $guid .= $sec.substr($usec, 2, 3);
    $guid .= rand(0,1) ? '-':''.rand(1000, 9999).rand(1000, 9999).rand(1000, 9999).rand(100, 999).rand(100, 999);
    $guid = strtoupper(md5($guid));
    return substr($guid, 0, 8).'-'.substr($guid, 8, 4).'-'.substr($guid, 12, 4).'-'.substr($guid, 16, 4).'-'.substr($guid, 20);
}

i'm sure you like it g

AstroTeg

Having been in the shipping industry, I can say the tracking numbers do increment by one (or at least back in the late 90's they had). Its been a while, but if I remember correctly, the last 5-10 digits increment by one. What makes the tracking numbers look goofy is the first 6 or so numbers are static depending on shipping method/level and shipping location/number. In short, the tracking number is a handful of number concatenated to make a unique number.

If you're doing anything with a session ID, then tossing that in as part of the number might help. Or if you have some info that is unique to the customer/transaction, then that info might be interesting to use with generating a unique (numeric) ID.