Unique Identifier: Usage and Limitations

Question

Unique Identifier: Usage and Limitations

chisholmd

SSCrazy

Points: 2490
More actions
June 23, 2004 at 11:21 pm

#85771

I just finished reading todays article "Unique Identifier: Usage and Limitations".
I have never used GUID's, I have always used identity integers. These have been causing a problem for me lately. Mostly to do with web security, there are many times that I need to maintain state in my web apps using either hidden form fields or querystrings. I have been using various forms of encryption or obfusication but I am starting to think that GUID's might offer an alternative as I could use them bare.
The biggest limitation I forsee is not being able to use @@identity to get a reference to the recently inserted.
Does anyone use GUID's in a web for these reasons? Any strategy advice, workarounds, pifalls etc?
Thanks
Does anyone use GUID's for
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
June 28, 2004 at 8:00 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

When you say maintaining state, exactly what are you doing. Storing variables for use in connection for retaining when session expires? Or exactly what. I have used several methods myself and experimented with a few using GUID but what is your goal?

chisholmd SSCrazy Points: 2490 More actions · Answer 2

Well have been mostly using session variables (IIS6) to store the id of any currently logged in user. Then lets say I pass the user a list of some objects in teh database that they own. For each of these I would be passing the id of the object as a querystring or a hidden field.

e.g.

showitem.asp?id=1

showitem.asp?id=2

showitem.asp?id=3

Since I am using an identity field with an incrementing integer it is very easy for someone to hack these and enter the ID of an object that might belong to another user.

One strategy is use now is to encrypt the querystring so they show up something like this:

showitem.asp?id=#%vF

showitem.asp?id=3vFq

showitem.asp?id=%6%jjU

There are many encryopting or obfusicating solutions available but they do suck some amount of resources since each id has to be run through these functions everytime they are displayed, then decrypted again when I receive the value back server side.

I was hoping that maybe using GUID's I could simply pass them around bare.

showitem.asp?uid=aaaabbbbbcbbcbcb

showitem.asp?uid=sdssdffsfsfsfsfs

showitem.asp?uid=qswdefegegegegeg

From what I understand no one could "guess" a guid.

One of the nice things about using identity fields is @@identity, is there something similiar for GUID's?

Hey, Maybe I should use both! Use the GUID's for passing around in the web app, but still have my integer ID so I could get easy reference to inserted records

e.g.

insert into...

select @GUID = GUID from...where ID = @@identity

return @GUID

I am trying to tighten the security on all my web apps including checking that form posts actually come from my domain, making hard to guess querystrings, and reduce my dependence on session vars.

So really any advice on advice or known pitfalls of using GUIDs is appriciated.

Thanks,

Dave

Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)

Dave Goerlich SSC Veteran Points: 215 More actions · Answer 3

Dave,

To insert a new record, and 'capture' the new GUID, in a similar manner to using the @@Identity, try this:

DECLARE @newGUID as UniqueIdentifier

SELECT @newGUID = NEWID()

INSERT INTO.... VALUES

(

@newGUID,

...

)

SELECT @newGUID

END

This will insert a row, and return the new GUID.

Hope this helps!

Dave Goerlich

Ross Environmental Services

chisholmd SSCrazy Points: 2490 More actions · Answer 4

So I generate the new GUID first, add it to my insert and return it to the calling proc. Ya that would work.

Thanks loads

Dave

Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)

Dave Goerlich SSC Veteran Points: 215 More actions · Answer 5

Yup, exactly. FWIW, I have a database that I'm currently converting from using Identity columns to GUIDS, as it it going to become a replicated database. In order to keep all of my data correctly linked from parent to child, the methodology I used to convert it was:

1) Edit the parent table, add a Unique Identifier column

2) UPDATE parentTable SET newIDcol = NEWID()

3) Edit child table, add new parentGUID column

4) UPDATE childTable SET parentGUID = newIDcol FROM parentTable INNER JOIN childTable ON parentTable.OldIdentity = childTable.oldParentIdentity

5) Edit Foreign Key relationship, change to link on new GUID columns

6) Edit childTable, drop oldParentIdentity

7) Edit parentTable, drop oldIdentity

Dave Goerlich

Ross Environmental Services