SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Using random number for Primary key in Table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

envida
Starting Member

Australia
6 Posts

Posted - 07/18/2003 :  00:21:10  Show Profile  Reply with Quote
I want to create an random number for the primary key on a table, but I don't want to use the increment value that Sql Server offers. I want it to be random.
I know I can create a uniqueidentifier but thats to big.
What I have been trying to use is this(which I found on this forum):

(convert(int,(rand(convert(binary(4),newid())) * 100000000)))

I make this the default value on the column that holds the primary key.

Is this a valid thing to do? Will it always create a unique key?
How many unique keys can I get from this value?

In Microsoft Access you can create a random number on the primary key, but this doesn't seem to exsist on Sql Server as far as know( but I'm a newbie of course).

Hope someone has some input on this.

cheers

robvolk
Most Valuable Yak

USA
15671 Posts

Posted - 07/18/2003 :  00:27:13  Show Profile  Visit robvolk's Homepage  Reply with Quote
A. Why do you want a random number?

B. Are there no other columns that offer a natural primary key candidate?

It makes no sense to me to choose a random number over a sequential number, if you insist on generating artificial key values. And even if there was a reason, then just use GUID, it's much easier and will guarantee uniqueness. The rand() function, no matter how you tweak it, will sooner or later generate the same value. Combining it with NEWID() probably won't help that too much.

Go to Top of Page

envida
Starting Member

Australia
6 Posts

Posted - 07/18/2003 :  01:04:52  Show Profile  Reply with Quote
Reason I want to use a random number is that it's for a webapplication and I will be passing the ID (primary key) in the URL, so as a security precaution I would like it to be a random number so it won't be easy to guess the previous or next record.

I don't have other columns that could act as a primary key.

I guess I could use GUID, but I have never used them before.
Are there any problems, speed issues, etc. I should be conserned about when using them in a webapplication?

I guess it will be slower to search a GUID compared to smaller numbers, but is there a big difference if you have like 4000 or so records?

Thanks


Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 07/18/2003 :  01:39:49  Show Profile  Visit Merkin's Homepage  Reply with Quote
Hi

Random is never guaranteed to be unique. A guid will perform a little worse than an int, but it will be unique. If you use some decent indexes you should get it up to speed.

However, if your application security is just that it isn't easy to guess an ID then that is no security at all. Can you secure in other ways (i.e. your current logged in user has permission to the current ID).



Damian
Go to Top of Page

Amethystium
Aged Yak Warrior

Garden of Sakuntala
701 Posts

Posted - 07/21/2003 :  09:33:41  Show Profile  Visit Amethystium's Homepage  Reply with Quote
Perhaps you could generate your random number based on the system clock.

You could even generate a random number by summing 2 or 3 random numbers together. The chance of the sum of three random numbers occurring twice or more is very slim indeed.

---------------
Shadow to Light
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/21/2003 :  10:06:03  Show Profile  Reply with Quote
quote:

Reason I want to use a random number is that it's for a webapplication and I will be passing the ID (primary key) in the URL, so as a security precaution I would like it to be a random number so it won't be easy to guess the previous or next record.



And to what end?

Won't you have to "code" for that?

If it's just an identifier, why do you care?


If it's sensitive data, why not have the user who's data that's sensitve protect it with a pwd?

Can you tell us what this is for?



Brett

8-)
Go to Top of Page

SamC
White Water Yakist

USA
3461 Posts

Posted - 07/21/2003 :  10:40:18  Show Profile  Reply with Quote
It might be simpler to do a simple encrypt / decrypt operation on your SQL IDENTITY (before passing the ID in your ASP querystring) instead of trying to cook up a random SQL ID.

For not so secure purposes, you could encrypt your id like:

strIDEncrypt = CSTR((MyID + 1111111) * 17)

then to retrieve the ID

intID = (CINT(Request.Querystring("ID")) / 17) - 1111111

You can try a larger prime number than 17 if you want.

Next you might want to validate the result somehow to make trial and error hacking fail. For example, append the numbers 2222 to the end of what you've encrypted, then check that the 2222 is there when you decrypt, etc.


strIDEncrypt = CSTR((((MyID * 10000) + 2222) + 1111111) * 17)

intID = (CINT(Request.Querystring("ID")) / 17) - 1111111

IF intID - (intID / 10000 * 1000) = 2222 Then
' Valid ID
intID = intID / 10000 ' Remove the 2222 suffix

Else
' Invalid ID
End If


Sam



Edited by - SamC on 07/21/2003 10:52:08
Go to Top of Page

JimL
SQL Slinging Yak Ranger

USA
1535 Posts

Posted - 07/21/2003 :  13:56:20  Show Profile  Visit JimL's Homepage  Reply with Quote
Why display the Primary key at all? If I dont want someone to know about my sequencing I hide it. Once I even added a bogus collum that performed a trace If someone tryed to access the next seq number.

Jim
Users <> Logic
Go to Top of Page

SamC
White Water Yakist

USA
3461 Posts

Posted - 07/21/2003 :  14:50:47  Show Profile  Reply with Quote
Envida has an ASP page that invokes a result on another page by passing a record parameter (ID) in a querystring.

There may be other ways to pass a parameter (session variables)...

Sam

Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 07/21/2003 :  14:59:04  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
I'd go with Sam's encrypt/decrypt method. i think it's a great idea, very simple.

I like the validation as well, to detect hacking...... nice one, Sam.

- Jeff
Go to Top of Page

Masterhame
Starting Member

2 Posts

Posted - 08/26/2013 :  05:20:35  Show Profile  Reply with Quote
quote:
Originally posted by SamC

strIDEncrypt = CSTR((((MyID * 10000) + 2222) + 1111111) * 17)

intID = (CINT(Request.Querystring("ID")) / 17) - 1111111

IF intID - (intID / 10000 * 1000) = 2222 Then
' Valid ID
intID = intID / 10000 ' Remove the 2222 suffix

Else
' Invalid ID
End If


Sam




I'm not a hacker but your code is easy to break!

I made a code form your advice in C# like this:





        public static long EncryptValidationID(long ValidationID)
        {
            return (((ValidationID * 10000) + 7589) + 1123211) * 29;
        }

        public static long DecryptValicationID(long EncryptedID)
        {
            long ID = (EncryptedID / 29) - 1123211;

            if (ID.ToString().Substring(ID.ToString().Length-4,4) == "7589")
            {
                return ID / 10000;
            }
            return -1;
        }



but it is easy to break!
look at this results:

ID EncryptedID
1 33083200
2 33373200
3 33663200
4 33953200
5 ?

remove 3200 trailing numbers, then you have:


ID EncryptedID
1 3308
2 3337
3 3366
4 3395
5 ?

every step add 29 to get the next step encrypted code!
for step 5: 3395+29=3424

then add 3200 trailer th 3424. you have 34243200 now. after running decrypt function to 34243200 you will receive 5!
Go to Top of Page

Masterhame
Starting Member

2 Posts

Posted - 08/26/2013 :  05:35:19  Show Profile  Reply with Quote
The best way is to change your data type to (uniqueidentifier) and write newid() in default value or binding property.
when you make a new row you have a random ID like these:

4ebd2661-612e-4544-93c5-c23e57051954
24298722-a082-4dcd-8e5b-9cc72d278984
d6c3fc3b-9533-4508-9f4d-138bd8fd75fe
5485985a-b8e6-4b0c-bb1f-0b497adccfdb
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.59 seconds. Powered By: Snitz Forums 2000