best way to hide identity column

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 2008 Forums

SQL Server Administration (2008)

best way to hide identity column

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-07-16 : 10:20:56

Hey Guys,

I've never worked on an app where an auto incrementing identity column was something we wanted to hide from the user.

I am working on something now where we take "orders" and we don't want competitive websites to be able to count our orders. I don't want to create a GUID ideally as they often break in emails and are just a bit ugly :)

Any suggestions ?

I have a function for creating a random password (provided by Peso)

I could use this and get a neat and tidy looking value, but the problem is that values generated are not unique.

Is there a tidy way to make sure the fnCreatePassword creates a value that doesnt exist in my column before I insert it ? (or if it generates a value that exists, we re-create)

Any help is greatly appreciated!

Thanks!
mike123

CREATE FUNCTION [dbo].[fnCreatePassword]
(
@UpperCaseItems SMALLINT,
@LowerCaseItems SMALLINT,
@NumberItems SMALLINT,
@SpecialItems SMALLINT
)
RETURNS VARCHAR(80)
AS
BEGIN
-- Initialize some variables
DECLARE @UpperCase VARCHAR(26),
@LowerCase VARCHAR(26),
@Numbers VARCHAR(10),
@Special VARCHAR(13),
@Temp VARCHAR(8000),
@Password VARCHAR(8000),
@i SMALLINT,
@c VARCHAR(1),
@v TINYINT

-- Set the default items in each group of characters
SELECT @UpperCase = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
@LowerCase = 'abcdefghijklmnopqrstuvwxyz',
@Numbers = '0123456789',
@Special = '!@#$%&*()_+-=',
@Temp = '',
@Password = ''

-- Enforce some limits on the length of the password
IF @UpperCaseItems > 20
SET @UpperCaseItems = 20

IF @UpperCaseItems < -20
SET @UpperCaseItems = -20

IF @LowerCaseItems > 20
SET @LowerCaseItems = 20

IF @LowerCaseItems < -20
SET @LowerCaseItems = -20

IF @NumberItems > 20
SET @NumberItems = 20

IF @NumberItems < -20
SET @NumberItems = -20

IF @SpecialItems > 20
SET @SpecialItems = 20

IF @SpecialItems < -20
SET @SpecialItems = -20

-- Get the Upper Case Items
SET @i = ABS(@UpperCaseItems)

WHILE @i > 0 AND LEN(@UpperCase) > 0
SELECT @v = nwd % LEN(@UpperCase) + 1,
@c = SUBSTRING(@UpperCase, @v, 1),
@UpperCase = CASE
WHEN @UpperCaseItems < 0 THEN STUFF(@UpperCase, @v, 1, '')
ELSE @UpperCase
END,
@Temp = @Temp + @c,
@i = @i - 1
FROM dbo.vwNEWID

-- Get the Lower Case Items
SET @i = ABS(@LowerCaseItems)

WHILE @i > 0 AND LEN(@LowerCase) > 0
SELECT @v = nwd % LEN(@LowerCase) + 1,
@c = SUBSTRING(@LowerCase, @v, 1),
@LowerCase = CASE
WHEN @LowerCaseItems < 0 THEN STUFF(@LowerCase, @v, 1, '')
ELSE @LowerCase
END,
@Temp = @Temp + @c,
@i = @i - 1
FROM dbo.vwNEWID

-- Get the Number Items
SET @i = ABS(@NumberItems)

WHILE @i > 0 AND LEN(@Numbers) > 0
SELECT @v = nwd % LEN(@Numbers) + 1,
@c = SUBSTRING(@Numbers, @v, 1),
@Numbers = CASE
WHEN @NumberItems < 0 THEN STUFF(@Numbers, @v, 1, '')
ELSE @Numbers
END,
@Temp = @Temp + @c,
@i = @i - 1
FROM dbo.vwNEWID

-- Get the Special Items
SET @i = ABS(@SpecialItems)

WHILE @i > 0 AND LEN(@Special) > 0
SELECT @v = nwd % LEN(@Special) + 1,
@c = SUBSTRING(@Special, @v, 1),
@Special = CASE
WHEN @SpecialItems < 0 THEN STUFF(@Special, @v, 1, '')
ELSE @Special
END,
@Temp = @Temp + @c,
@i = @i - 1
FROM dbo.vwNEWID

-- Scramble the order of the selected items
WHILE LEN(@Temp) > 0
SELECT @v = nwd % LEN(@Temp) + 1,
@Password = @Password + SUBSTRING(@Temp, @v, 1),
@Temp = STUFF(@Temp, @v, 1, '')
FROM dbo.vwNEWID

RETURN @Password
END

GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-18 : 16:25:50

Try this one

DECLARE	@Value DECIMAL(38, 0),
	@Yak DECIMAL(38, 0),
	@Temp DECIMAL(38, 0)

SELECT	@Value = 9223372036854775808 + CAST(CAST(NEWID() AS BINARY(16)) AS BIGINT),
	@Yak = POWER(62.0, 10)

DECLARE	@Characters VARCHAR(75),
	@Output VARCHAR(75)

SELECT	@Characters =	(
				SELECT		SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', Number, 1)
				FROM		master..spt_values
				WHERE		Type = 'P'
						AND Number BETWEEN 1 AND 62
				ORDER BY	NEWID()
				FOR XML		PATH('')
			),
	@Output = ''

WHILE @Yak >= 1
	BEGIN
		SET	@Temp = FLOOR(@Value / @Yak)

		SET	@Output = @Output + SUBSTRING(@Characters, @Temp + 1, 1)

		SELECT	@Value = @Value - @Temp * @Yak,
			@Yak = @Yak / 62
	END

SELECT	@Output

N 56°04'39.26"
E 12°55'05.63"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-07-20 : 06:18:29

Hey Peso,

Looks very interesting, I get this error tho when trying to run it.

Msg 8116, Level 16, State 1, Line 25
Argument data type decimal is invalid for argument 2 of substring function.

Any ideas ?

Thanks again!
mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-20 : 06:20:52

To be honest, I am on SQL 2008 writing this.
DECLARE @Temp as TINYINT and it should work.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-20 : 06:41:11

The probability you get same "base characters" are 1 in 2^62 (1 in 4 611 686 018 427 387 904).
However, since the random value can be represented in any "base character sequence", the probability you get an collision value is near zero, since the random value is only created once, due the nature of NEWID().

If you want to be absolute sure there cannot be a collision, always use same base character sequence.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-20 : 07:04:51

Using a fixed based character sequence also have the ability to reverse engineer the code to the original id value.

N 56°04'39.26"
E 12°55'05.63"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-07-20 : 08:27:03

Hey Peso,

Changing to tinyint worked perfectly :)

I have a couple futher questions if you don't mind.

Using a value as generated by your function (example: YpgSxdvaDe7)
as a new ID column for my tables, would not be as fast as using a simple incrementing INT value, but does get me the security I have need.

While this solution works perfectly, your previous solution for the password was also good, and the 6 char string was more visually appealing for URL's and User Interface.

1.) What do you think about using the password function and somehow just checking to see if it already exists in the table ? If it does just create a new one ?

I would have to adjust my site so that all the SP's get passed this unique value for the WHERE clause instead of the INT IDENTITY

2.) I'm not sure if this is a decent technique or a technique just asking for a headache.

What if we had some sort of algorithm that transformed a simple INT into this value, I could keep the sql backend written and designed with integer lookups etc ...

Thanks once again!
mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-20 : 09:22:46

It's very simple. Write a base 62 reverse coding algorithm!

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-20 : 09:23:20

I'll do that in a few minutes.
have to take care of daughter now. She woke up.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-20 : 09:42:02

A thought. You can still have BIGINT as identity (clustered index), and have a column CODE with default to NEWID().

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-20 : 09:53:34

[code]DECLARE @Code CHAR(11),
@Base CHAR(62),
@Value DECIMAL(38, 0)

SELECT @Code = 'YpgSxdvaDe7',
@Base = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789',
@Value = 0

SELECT @Value = SUM(Multiplier * Exponent) - 9223372036854775808
FROM (
SELECT POWER(62.0, Number) AS Exponent,
CHARINDEX(SUBSTRING(@Code, LEN(@Code) - Number, 1), @Base) AS Multiplier
FROM master..spt_values
WHERE Type = 'P'
AND Number < LEN(@Code)
) AS d

SELECT @Value[/code]

N 56°04'39.26"
E 12°55'05.63"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-07-20 : 10:08:37

How about using BIGINT IDENTITY column seeded to a fairly large number, and setting the increment to a fairly large number, like 462.

That would make it very hard for someone outside to determine how many orders your are processing.


create table #ORDER (
ORDER_ID bigint	not null
	identity(4729845244390348,462)
	primary key clustered,
COL_1 int not null
)

insert into #Order (COL_1)
select top 10
	id
from
	sysobjects

select * from  #ORDER

drop table #order

Results:


ORDER_ID             COL_1       
-------------------- ----------- 
4729845244390348     1
4729845244390810     2
4729845244391272     3
4729845244391734     4
4729845244392196     6
4729845244392658     8
4729845244393120     9
4729845244393582     10
4729845244394044     11
4729845244394506     12

CODO ERGO SUM

tripodal
Constraint Violating Yak Guru

259 Posts

Posted - 2009-07-20 : 11:24:47

Use datetime as your decrement.

Then only the person with all of the orders can actually determine the ordernumbers.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-20 : 11:44:55

Will have some problems if two orders are placed simultaneously.

N 56°04'39.26"
E 12°55'05.63"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2009-07-21 : 05:33:27

Hey Guys,

Some excellent suggestions here, am going to play with the suggested ideas and see what works best.

Thanks again!
Mike123

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2009-07-21 : 09:38:04

I like MVJ's suggestion. your competitors will be blown away that you have 4729845244390348 orders already so soon, and that you process them 462 times faster than they do!

elsasoft.org

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-07-21 : 09:58:36

quote:
Originally posted by jezemine

I like MVJ's suggestion. your competitors will be blown away that you have 4729845244390348 orders already so soon, and that you process them 462 times faster than they do!

elsasoft.org

It would also give you a fixed length Order number, at least until you you have more then 11,407,261,375,778 orders.

CODO ERGO SUM

tripodal
Constraint Violating Yak Guru

259 Posts

Posted - 2009-08-11 : 19:35:03

I still prefer counting backwards.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources