Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Using random number for Primary key in Table

Author  Topic 

envida
Starting Member

6 Posts
Posted - 2003-07-18 : 00:21:10
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

15732 Posts
Posted - 2003-07-18 : 00:27:13
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

6 Posts
Posted - 2003-07-18 : 01:04:52
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!

4970 Posts
Posted - 2003-07-18 : 01:39:49
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

701 Posts
Posted - 2003-07-21 : 09:33:41
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 - 2003-07-21 : 10:06:03
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

3467 Posts
Posted - 2003-07-21 : 10:40:18
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

1537 Posts
Posted - 2003-07-21 : 13:56:20
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

3467 Posts
Posted - 2003-07-21 : 14:50:47
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

7423 Posts
Posted - 2003-07-21 : 14:59:04
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 - 2013-08-26 : 05:20:35
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 - 2013-08-26 : 05:35:19
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
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site