Ensuring Primary Key in MySQL is Random and Unique

classic Classic list List threaded Threaded
19 messages Options
Reply | Threaded
Open this post in threaded view
|

Ensuring Primary Key in MySQL is Random and Unique

stevepiercy
I'm working on improving security by making it almost impossible to guess the value of a primary key field in MySQL, but I am not too confident with my logic.

How does one create a new record in MySQL that has its primary key field:
* as a unique value
* not auto-incremented
* a random alphanumeric string

Would this pseudo-code logic hold up?

    var: 'primary_key' = [http://tagswap.net/lp_string_random]
    var: 'not_unique' = true
    while ($not_unique)
        inline search for $primary_key
        /inline
        if $primary_key already exists
            pick another random primary key
        else
            inline add with unique primary key
            /inline
            $not_unique = false
        /if
    /while

--steve
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Steve Piercy               Web Site Builder               Soquel, CA
<[hidden email]>                   <http://www.StevePiercy.com>

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Ensuring Primary Key in MySQL is Random and Unique

James Harvard
At 1:55 am -0700 1/9/06, Steve Piercy - Web Site Builder wrote:
>I'm working on improving security by making it almost impossible to guess the value of a primary key field in MySQL, but I am not too confident with my logic.
>
>How does one create a new record in MySQL that has its primary key field:
>* as a unique value
>* not auto-incremented
>* a random alphanumeric string

I think your code would work fine, but it would probably be a bit quicker to just try the insert and check for a duplicate key error from MySQL (see below). OTOH maybe this is something that a MySQL stored procedure could deal with entirely in the DB layer? That would be tidier, if possible.

var( 'done' = false);
while( ! $done );
        inline( ... 'p_key_col'=lp_string_random, -add );
                if( ! (duplicate_key_error) );
                        $done = true;
                        // check for any other error
                /if;
        /inline;
/while;

HTH,
James

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Ensuring Primary Key in MySQL is Random and Unique

Jolle Carlestam
I use the built in tag Lasso_UniqueID. There been heard arguments on  
the list that it's not truly random, but it is truly unique and if  
used in a primary key field you can be certain that there will be no  
duplicates.

And even if part of the string would be the same over a number of  
iterations there is still so much of the string that's random to make  
it very hard for anyone to guess what comes next.

Here's code to test:
<?LassoScript //[
        loop: 20;
                Lasso_UniqueID;
                '<br />';
        /loop;
//]
?>

And here's the result from two tries with about 20 seconds apart:

55EB014515ffa23A05NiGx82A7BE
55EB014515ffa23A05RyXW82A7BE
55EB014515ffa23A05RqVp82A7BE
55EB014515ffa23A05Uhik82A7BE
55EB014515ffa23A05VpWj82A7BE
55EB014515ffa23A05nwqW82A7BE
55EB014515ffa23A05USvG82A7BE
55EB014515ffa23A05vxkk82A7BE
55EB014515ffa23A05vwHL82A7BE
55EB014515ffa23A05VlkV82A7BE
55EB014515ffa23A05oIjl82A7BE
55EB014515ffa23A05oxGU82A7BE
55EB014515ffa23A05rixt82A7BE
55EB014515ffa23A05nnro82A7BE
55EB014515ffa23A05iKKg82A7BE
55EB014515ffa23A05RnxP82A7BE
55EB014515ffa23A05XrGt82A7BE
55EB014515ffa23A05URXM82A7BE
55EB014515ffa23A05mOMk82A7BE
55EB014515ffa23A05JiIi82A7BE

55EB014515ffa249F9rPrq82C487
55EB014515ffa249F9oQQQ82C487
55EB014515ffa249F9OuTU82C487
55EB014515ffa249F9mVSu82C487
55EB014515ffa249F9wiNo82C488
55EB014515ffa249F9mWyV82C488
55EB014515ffa249F9NtOm82C488
55EB014515ffa249F9sGvt82C488
55EB014515ffa249F9lVxY82C488
55EB014515ffa249F9wwST82C488
55EB014515ffa249F9gYKy82C488
55EB014515ffa249F9yySi82C488
55EB014515ffa249F9iVqw82C488
55EB014515ffa249F9qppo82C488
55EB014515ffa249F9iOHM82C488
55EB014515ffa249F9LvoU82C488
55EB014515ffa249F9JsMV82C488
55EB014515ffa249F9wuXr82C488
55EB014515ffa249F9Gqxx82C488
55EB014515ffa249F9PrHr82C488

HDB
JC

1 sep 2006 kl. 12.29 skrev James Harvard:

> At 1:55 am -0700 1/9/06, Steve Piercy - Web Site Builder wrote:
>> I'm working on improving security by making it almost impossible  
>> to guess the value of a primary key field in MySQL, but I am not  
>> too confident with my logic.
>>
>> How does one create a new record in MySQL that has its primary key  
>> field:
>> * as a unique value
>> * not auto-incremented
>> * a random alphanumeric string
>
> I think your code would work fine, but it would probably be a bit  
> quicker to just try the insert and check for a duplicate key error  
> from MySQL (see below). OTOH maybe this is something that a MySQL  
> stored procedure could deal with entirely in the DB layer? That  
> would be tidier, if possible.
>
> var( 'done' = false);
> while( ! $done );
> inline( ... 'p_key_col'=lp_string_random, -add );
> if( ! (duplicate_key_error) );
> $done = true;
> // check for any other error
> /if;
> /inline;
> /while;
>
> HTH,
> James
>
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives: http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription:
> http://www.listsearch.com/lassotalk.lasso?manage


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Ensuring Primary Key in MySQL is Random and Unique

Trevor Jacques
>And here's the result from two tries with about 20 seconds apart:
>
>55EB014515ffa23A05NiGx82A7BE
>55EB014515ffa23A05RyXW82A7BE
>55EB014515ffa23A05RqVp82A7BE
>55EB014515ffa23A05Uhik82A7BE
>55EB014515ffa23A05VpWj82A7BE...

I usually take the part that LOOKS random, and then massage that even
more (concatenating several random-looking parts, reversing the order
of some of them, etc.).

T.

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Ensuring Primary Key in MySQL is Random and Unique

Bil Corry-3
In reply to this post by stevepiercy
Steve Piercy - Web Site Builder wrote:
> I'm working on improving security by making it almost impossible to guess the value of a primary key field in MySQL, but I am not too confident with my logic.
>
> How does one create a new record in MySQL that has its primary key field:
> * as a unique value
> * not auto-incremented
> * a random alphanumeric string

One other note, to add to what everyone has said, make sure the column that will store the primary key is a char marked as "binary" - that way it'll be case-sensitive.  Otherwise, xJ92a is the same as Xj92A, which severely limits the number of unique values.

- Bil


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Ensuring Primary Key in MySQL is Random and Unique

Viaduct Productions
In reply to this post by Jolle Carlestam
I've been able to duplicate items on my G5 1.8.  So, if you are  
indeed using these as unique items, you might want to test it first.

On Sep 1, 2006, at 9:01 AM, [hidden email] wrote:

> I use the built in tag Lasso_UniqueID. There been heard arguments  
> on the list that it's not truly random, but it is truly unique and  
> if used in a primary key field you can be certain that there will  
> be no duplicates.


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Ensuring Primary Key in MySQL is Random and Unique

Jolle Carlestam
That calls for a comment from Omnipilot! Is it possible that  
Lasso_UniqueID can produce the same string twice?

HDB
JC
1 sep 2006 kl. 16.13 skrev Rich:

> I've been able to duplicate items on my G5 1.8.  So, if you are  
> indeed using these as unique items, you might want to test it first.
>
> On Sep 1, 2006, at 9:01 AM, [hidden email] wrote:
>
>> I use the built in tag Lasso_UniqueID. There been heard arguments  
>> on the list that it's not truly random, but it is truly unique and  
>> if used in a primary key field you can be certain that there will  
>> be no duplicates.


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Ensuring Primary Key in MySQL is Random and Unique

Göran Törnquist-2
Since it's a pseudo random string it would most
likely be able to produce a duplicate.

/Göran

>That calls for a comment from Omnipilot! Is it
>possible that Lasso_UniqueID can produce the
>same string twice?
>
>HDB
>JC
>1 sep 2006 kl. 16.13 skrev Rich:
>
>>I've been able to duplicate items on my G5 1.8.
>>So, if you are indeed using these as unique
>>items, you might want to test it first.
>>
>>On Sep 1, 2006, at 9:01 AM, [hidden email] wrote:
>>
>>>I use the built in tag Lasso_UniqueID. There
>>>been heard arguments on the list that it's not
>>>truly random, but it is truly unique and if
>>>used in a primary key field you can be certain
>>>that there will be no duplicates.
>
>
>------------------------------
>Lasso Support: http://support.omnipilot.com/
>Search the list archives: http://www.listsearch.com/lassotalk.lasso
>Manage your list subscription:
>http://www.listsearch.com/lassotalk.lasso?manage
>
>--
>This message has been scanned for viruses and
>dangerous content by MailScanner, and is
>believed to be clean.


--
--
Göran Törnquist
--
0733-86 04 70
--
Cortland AB
Sjökarbyvägen 23
184 34 Åkersberga

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Ensuring Primary Key in MySQL is Random and Unique

Jolle Carlestam
I was under the impression that it took timestamped info from the  
local environment and added a random piece to that. And that the  
combination of timestamp and random should ensure that there could  
not be duplicates.

But I would love to hear from Omnipilot.

HDB
JC

1 sep 2006 kl. 18.43 skrev Göran Törnquist:

> Since it's a pseudo random string it would most likely be able to  
> produce a duplicate.
>
> /Göran
>
>> That calls for a comment from Omnipilot! Is it possible that  
>> Lasso_UniqueID can produce the same string twice?
>>
>> HDB
>> JC
>> 1 sep 2006 kl. 16.13 skrev Rich:
>>
>>> I've been able to duplicate items on my G5 1.8. So, if you are  
>>> indeed using these as unique items, you might want to test it first.
>>>
>>> On Sep 1, 2006, at 9:01 AM, [hidden email] wrote:
>>>
>>>> I use the built in tag Lasso_UniqueID. There been heard  
>>>> arguments on the list that it's not truly random, but it is  
>>>> truly unique and if used in a primary key field you can be  
>>>> certain that there will be no duplicates.


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Ensuring Primary Key in MySQL is Random and Unique

Fletcher Sandbeck
In reply to this post by Jolle Carlestam
On 2006-09-01 at 18:28 by [hidden email]:

>That calls for a comment from Omnipilot! Is it possible that  
>Lasso_UniqueID can produce the same string twice?

Lasso_UniqueID has three basic components.  One component is based on the client's IP address.  One component is based on the machine clock.  One component is random.

In normal use it should be impossible to generate to generate a duplicate ID.

It is theoretically possible to get the same ID if you have heavy traffic which is being routed through a single proxy server.  In this case the IP component is defeated since Lasso sess every request from a single IP address.  Lasso is multi-threaded so it is possible for two IDs to be generated at the same time.  And, traffic needs to be heavy enough that two IDs generate the same random components.

It is possible for you to replace [Lasso_UniqueID] with your own custom tag if you require a different implementation.

[fletcher]
--
Fletcher Sandbeck                         [hidden email]
Director of Product Development       http://www.lassostudio.com
OmniPilot Software, Inc.                http://www.omnipilot.com

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Ensuring Primary Key in MySQL is Random and Unique

Mr. Bret Williams
The concern we have had with the Lasso_UniqueID is that for the same  
user, or if the server is generating the key, so many of the  
beginning characters are the same. While it would still be difficult  
to spoof an ID using this value, the appearance is that the key is  
not truly random.

We use our own cTag to generate a unique ID (this was originally  
conceived by Keith Schuster, if I recall correctly):

  If: !(Lasso_TagExists: 'nCtag_UniqueID');

   Define_Tag: 'nCtag_UniqueID',
    -Priority='High';

    Local: 'lvIDLength' = '28';
    Local: 'lvErrorNo' = '';
    Local: 'lvErrorMessage' = '';
    Local: 'lvIDValue' = '';
    Local: 'lvResult' = '';
    Local: 'lvFound' = 1;
    Local: 'lvSourceCharacters' =  
'1234567890aAbBcCdDeEfFgGhHiIjJkKlLmMnNoOpPqQrRsStTuUvVwWxXyYzZ';
    Local: 'lvSourceLength' = (String_Length: (#lvSourceCharacters));

    If: (Local_Defined: 'Database');

     Local: 'lvDatabase' = (#Database);
     Local: 'lvTable' = (#Table);
     Local: 'lvColumn' = (#Column);
     Local: 'lvUsername' = (#Username);
     Local: 'lvPassword' = (#Password);

    /If;

    While: #lvFound > 0 ;

     Loop: #lvIDLength;

      #lvIDValue += (#lvSourceCharacters)->(Substring:(Math_Random: -
Min=1, -Max=(Math_Add: #lvSourceLength, '1')),1);

     /Loop;
                               
     If: (Local_Defined: 'Database');
                               
      Local: 'lvSQL'=
       ('SELECT
        '+#lvColumn+'
         FROM
         '+#lvDatabase+'.'+#lvTable+'
         WHERE
         '+#lvColumn+'="'+#lvIDValue+'"');
                               
       Inline:
        -Database = #lvDatabase,
        -UserName = #lvUsername,
        -Password = #lvPassword,
        -SQL = #lvSQL;
                                               
        #lvFound = (Found_Count);
        #lvErrorNo = (Error_CurrentError: -ErrorCode);
        #lvErrorMessage = (Error_CurrentError);
                                       
       /Inline;
                                       
       If: #lvErrorNo != 0;
                                       
        #lvResult = 'Error:'+#lvErrorNo+'::'+#lvErrorMessage;
        Loop_Abort;
                               
       Else: #lvFound == 0;
                               
        #lvResult = #lvIDValue;
        Loop_Abort;
                               
       Else:(Loop_Count) == 100;
                               
        #lvResult = 'It took more then 100 passes to find a unique id';
        Loop_Abort;
                               
       Else;
                               
        #lvIDValue = '';
                               
       /If;
                                       
      Else;
                               
       #lvResult = #lvIDValue;
       #lvFound = '0';
                               
      /If;
                       
     /While;
                               
     Return: #lvResult;
         
    /Define_Tag;

   /If;

You may want to adjust the Loop Count if you have a rather large  
database, but we have had no trouble quickly generating unique ID of  
any length, that would be very difficult to spoof if used in a URL  
(ex.: products.lasso?ID=eirTUD83Doe45ILx)


On Sep 1, 2006, at 9:48 AM, Fletcher Sandbeck wrote:

> On 2006-09-01 at 18:28 by [hidden email]:
>
>> That calls for a comment from Omnipilot! Is it possible that
>> Lasso_UniqueID can produce the same string twice?
>
> Lasso_UniqueID has three basic components.  One component is based  
> on the client's IP address.  One component is based on the machine  
> clock.  One component is random.
>
> In normal use it should be impossible to generate to generate a  
> duplicate ID.
>
> It is theoretically possible to get the same ID if you have heavy  
> traffic which is being routed through a single proxy server.  In  
> this case the IP component is defeated since Lasso sess every  
> request from a single IP address.  Lasso is multi-threaded so it is  
> possible for two IDs to be generated at the same time.  And,  
> traffic needs to be heavy enough that two IDs generate the same  
> random components.
>
> It is possible for you to replace [Lasso_UniqueID] with your own  
> custom tag if you require a different implementation.
>
> [fletcher]
> --
> Fletcher Sandbeck                         [hidden email]
> Director of Product Development       http://www.lassostudio.com
> OmniPilot Software, Inc.                http://www.omnipilot.com
>
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives: http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription:
> http://www.listsearch.com/lassotalk.lasso?manage



Bret Williams
Novusweb

www.Novusweb.com

760.942.3773 voice
866.668.8793 toll-free

CONFIDENTIALITY NOTICE:  This e-mail document, including all  
attachments, is for the sole use of the intended recipient(s) and may  
contain confidential and privileged information.  If you are not the  
intended recipient, you may NOT use, disclose, copy, or disseminate  
this information.  Please contact the sender by reply immediately and  
destroy all copies of the original message including all  
attachments.  Your cooperation is greatly appreciated.




------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Ensuring Primary Key in MySQL is Random and Unique

Bil Corry-3
In reply to this post by stevepiercy
Steve Piercy - Web Site Builder wrote:
>     var: 'primary_key' = [http://tagswap.net/lp_string_random]

BTW, if you use [lp_string_random] to generate a 20-char id, and you have the column marked as "binary", the odds (assuming [math_random] is indeed random) of hitting a duplicate in a table of 10 million rows is 1 in 70,442,342,554,699,802,296,833,026,462.  The odds get much bigger when the number of rows is much smaller.

So unless you need your solution to be 100% bullet-proof, you can skip checking for duplicates.  Your solution will still be 99.999999999999999999999999858% bullet-proof.

- Bil


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Ensuring Primary Key in MySQL is Random and Unique

Viaduct Productions
In reply to this post by Jolle Carlestam
I got it to do it again today.  In fact, I got it to replicate it  
three times, on more than one occasion.

On Sep 1, 2006, at 12:45 PM, [hidden email] wrote:

> I was under the impression that it took timestamped info from the  
> local environment and added a random piece to that. And that the  
> combination of timestamp and random should ensure that there could  
> not be duplicates.
>
> But I would love to hear from Omnipilot.


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Ensuring Primary Key in MySQL is Random and Unique

Viaduct Productions
In reply to this post by Bil Corry-3
Much less bullet-proof than that, if you are getting unique ID's for  
any application, for the same user:

var('smack' = 1000);
loop(50);
var('hold' = set);

loop($smack);
$hold->insert(lasso_uniqueid);
/loop;

$hold->size != $smack ? $hold->size '<br/>';
/loop;



On Sep 1, 2006, at 2:42 PM, Bil Corry wrote:

> So unless you need your solution to be 100% bullet-proof, you can  
> skip checking for duplicates.  Your solution will still be  
> 99.999999999999999999999999858% bullet-proof.


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Ensuring Primary Key in MySQL is Random and Unique

Fletcher Sandbeck
On 2006-09-01 at 15:08 by [hidden email] (Rich):

>Much less bullet-proof than that, if you are getting unique ID's for  
>any application, for the same user:
>
>var('smack' = 1000);
>loop(50);
>var('hold' = set);
>
>loop($smack);
>$hold->insert(lasso_uniqueid);
>/loop;
>
>$hold->size != $smack ? $hold->size '<br/>';
>/loop;
>
>On Sep 1, 2006, at 2:42 PM, Bil Corry wrote:
>
>> So unless you need your solution to be 100% bullet-proof, you can  
>> skip checking for duplicates.  Your solution will still be  
>> 99.999999999999999999999999858% bullet-proof.

99.98% bulletproof in my testing.  Of course, generating 50,000 unique IDs in a tight loop is not what I would consider normal usage.  If you add a [Sleep: 2] after the [Lasso_UniqueID] then it should be closer to 100% bulletproof.

If you are generating IDs in a loop like this you can append the [Loop_Count] to the end of each ID.  This sequential element will ensure uniqueness at the page level.

I get no duplicates with this code, but it takes 128 seconds to run.

var('smack' = 1000);
loop(50);
var('hold' = set);
loop($smack); $hold->insert(lasso_uniqueid); sleep: 2;
/loop;
$hold->size != $smack ? $hold->size '<br/>';
/loop;

I also get no duplicates with this code, in only about 10 seconds.

var('smack' = 1000);
loop(50);
var('hold' = set);
loop($smack); $hold->insert(lasso_uniqueid + loop_count);
/loop;
$hold->size != $smack ? $hold->size '<br/>';
/loop;

[fletcher]
--
Fletcher Sandbeck                         [hidden email]
Director of Product Development       http://www.lassostudio.com
OmniPilot Software, Inc.                http://www.omnipilot.com

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Ensuring Primary Key in MySQL is Random and Unique

Bil Corry-3
In reply to this post by Viaduct Productions
Rich wrote:
> Much less bullet-proof than that, if you are getting unique ID's for any
> application, for the same user:

Well, [set] isn't case-sensitive, so it could be some values are not actually duplicates.

Anyhow, my blurb about the odds was in reference to [lp_string_random:20], not [lasso_uniqueid].


- Bil



------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Ensuring Primary Key in MySQL is Random and Unique

Viaduct Productions
In reply to this post by Fletcher Sandbeck
Ya the sleep tweaks one of your factors.  But this just demonstrates  
that a leaky boat has the potential to sink, so that's why I  
suggested testing.  For the most part, it's pretty tight.

On Sep 1, 2006, at 3:43 PM, Fletcher Sandbeck wrote:

> 99.98% bulletproof in my testing.  Of course, generating 50,000  
> unique IDs in a tight loop is not what I would consider normal  
> usage.  If you add a [Sleep: 2] after the [Lasso_UniqueID] then it  
> should be closer to 100% bulletproof.
>
> If you are generating IDs in a loop like this you can append the  
> [Loop_Count] to the end of each ID.  This sequential element will  
> ensure uniqueness at the page level.


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Ensuring Primary Key in MySQL is Random and Unique

stevepiercy
In reply to this post by Viaduct Productions
For the record, with Rich's code, I verified that lasso_uniqueid is not unique on a PB G4 1.0 Ghz where all calls to the tag are made on localhost.  It's very close to unique where about < 1/10,000 are duplicates.  Although Fletcher explained the algorithm which decreases the likelihood of duplicates to virtually zero given the client IP factor (which would be good enough in most situations), I need it to be absolutely zero in this situation.  Randomness need only be sufficient to prevent guessing.

So I'll use Bil's tag [lp_string_random] and suggestion to store the value as binary, and use James' pseudo-code to check the db for uniqueness.

Thanks everyone!

--steve


On Friday, September 1, 2006, [hidden email] (Rich) pronounced:

>I've been able to duplicate items on my G5 1.8.  So, if you are  
>indeed using these as unique items, you might want to test it first.
>
>On Sep 1, 2006, at 9:01 AM, [hidden email] wrote:
>
>> I use the built in tag Lasso_UniqueID. There been heard arguments  
>> on the list that it's not truly random, but it is truly unique and  
>> if used in a primary key field you can be certain that there will  
>> be no duplicates.
>
>
>------------------------------
>Lasso Support: http://support.omnipilot.com/
>Search the list archives: http://www.listsearch.com/lassotalk.lasso
>Manage your list subscription:  
>http://www.listsearch.com/lassotalk.lasso?manage
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Steve Piercy               Web Site Builder               Soquel, CA
<[hidden email]>                   <http://www.StevePiercy.com>

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Ensuring Primary Key in MySQL is Random and Unique

Jono-2
In reply to this post by stevepiercy
What I did with a solution last week (PHP so my options were limited  
dammit) was I inserted the row, then MD5'd the id and updated the row  
with that MD5 value in another column.

The 'key' i then always refer to the row as is the MD5 column. It is  
unique because it has a MySQL unique index on the column.

Now the reason i have to do that with PHP is that PHP does not have  
native encryption functions that allow you to decrypt as well. You  
need 3rd party patches/modules/functions which you can not guarantee  
exist, and in fact are not allowed in some environments.

The other was which is more common for me to use via lasso is that  
the id is encrypted whenever it is exposed in a link, and then  
decrypted at the head of the page. You can also do 2-factor checking  
there - encrypt the id using one salt, encrypt the id using another  
salt, pass both params in your link or form, then incoming on the  
target page you decrypt both and if they match, you're in business.

You could even get carried away further with this method but hey,  
your choice :)



On 01/09/2006, at 8:55 PM, Steve Piercy - Web Site Builder wrote:

> I'm working on improving security by making it almost impossible to  
> guess the value of a primary key field in MySQL, but I am not too  
> confident with my logic.
>
> How does one create a new record in MySQL that has its primary key  
> field:
> * as a unique value
> * not auto-incremented
> * a random alphanumeric string
>
> Would this pseudo-code logic hold up?


Jono

// ---------------------------------------------------------
/*
     Jonathan Guthrie
     xServe Ltd, Wellington NZ
     [hidden email]   |   http://xserve.co.nz
     DDI +64 4 978 7388   |   Mob +64 21 678890

*/
// ---------------------------------------------------------



------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage