Brain teaser -- query to construct password field

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

General SQL Server Forums

New to SQL Server Programming

Brain teaser -- query to construct password field

Author

Topic

smithm55
Starting Member

9 Posts

Posted - 2012-08-01 : 16:09:35

I'm trying to enhance a query used to build a nightly file of new student accounts in our school district. I have a table of 6-letter words, called password_list. I want my query to build a CSV file each night of new students that contains (A) basic demographic information collected from various tables, and (B) a password. I'd like to build a password for each student as follows:

(1) Pick a random number, and use the password from that row in table password_list
(2) Append to that a random 2-digit number

So, some examples of passwords would be Rabbit24, Orange19, etc.

Could this be done within a select query?

I've tried to incorporate subqueries with some ideas to randomize things that I've found on other sites, such as:

select top 1 password from password_list order by newid()

and

select password from password_list where pwno=ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 1000 + 1

but I can't quite seal the deal.

Any advice would be greatly appreciated!!

Matt Smith
Information Services
DeSoto County (FL) School District

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-08-01 : 16:21:28

What version of SQL are you using? maybe this will help?

SELECT
	<Column List>,
	(select top 1 password from password_list order by newid())
	+ LEFT('0' + CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 100 + 1 AS VARCHAR(2)), 2) AS Password
FROM
	<Student Info>
...

If not:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

smithm55
Starting Member

9 Posts

Posted - 2012-08-02 : 12:03:37

Lamprey, thanks much for the reply! I'm using SQL Server 2008 R2. Your code is really close, but apparently the "order by newid()" only seeds (if that's the right term) once at the beginning of the query. The code I used is as follows:


SELECT
	first_name,
	(select top 1 password from password_list order by newid())
	+ right('0' + CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 99 + 1 AS VARCHAR(2)), 2) AS Password
FROM
	student_info_core

and the results I get are as follows:


ABIGAIL    quacks88
BRANDON	   quacks21
CAMERON	   quacks83
CAPONE	   quacks48
CHEYENNE   quacks20
CHLOE      quacks84
DAMARION   quacks56
DANIELLE   quacks25
DONTAVIAN  quacks49

Every time I re-run the query, I get a different word, but it's always the same word for every record. We're close, but that's where I'm stuck! Anyone know how to make sure we get a new word each time?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-08-02 : 12:46:01

Humm, I'm not seeing the same behavior:

DECLARE @Student TABLE (Name VARCHAR(50))
INSERT @Student (Name)
VALUES
('ABIGAIL'),    
('BRANDON'),	   
('CAMERON'),	   
('CAPONE'),	   
('CHEYENNE'),   
('CHLOE'),      
('DAMARION'),   
('DANIELLE'),   
('DONTAVIAN')

DECLARE @Password_list TABLE (Password VARCHAR(50))
INSERT @Password_list (Password)
VALUES

('Orange'),
('Rabbit'),
('Quacks'),
('Fudges'),
('Happy')

  
SELECT
	Name,
	(select top 1 password from @password_list order by newid())
	+ right('0' + CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 99 + 1 AS VARCHAR(2)), 2) AS Password
FROM
	@student

Results

Name	Password
ABIGAIL		Fudges46
BRANDON		Quacks35
CAMERON		Fudges50
CAPONE		Rabbit67
CHEYENNE	Quacks33
CHLOE		Happy84
DAMARION	Happy65
DANIELLE	Happy34
DONTAVIAN	Orange37

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-08-02 : 12:47:46

May try an OUTER APPLY?

SELECT
	Name,
	A.Password
FROM
	@student AS s
OUTER APPLY
(
	select top 1 
		password + right('0' + CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 99 + 1 AS VARCHAR(2)), 2) AS Password
	from @password_list order by newid()
	
) AS A

smithm55
Starting Member

9 Posts

Posted - 2012-08-02 : 13:51:48

Lamprey, what version of SQL are you using? I'm getting syntax errors when I try to execute your code. Specifically, the table values from your first post, and the OUTER APPLY from the second post.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-08-02 : 13:56:06

I tried it on several versions of SQL: 2008, 2008 R2 and 2012. Is your Server perhaps running in a backwards compatable mode for an older version of SQL? I don't have a running version of 2005, but my first query should work on that too.

smithm55
Starting Member

9 Posts

Posted - 2012-08-02 : 14:46:17

Strange. No weird back-compatible modes that I'm aware of. I'll go back to the drawing board for just a bit. Thanks for staying with this!

smithm55
Starting Member

9 Posts

Posted - 2012-08-02 : 15:06:40

Very puzzling. I've tried on my 2005 and 2008 R2 servers.

This statement:

DECLARE @Password_list TABLE (Password VARCHAR(50))
INSERT @Password_list (Password)
VALUES
('Orange')

returns this:

(1 row(s) affected)

But as soon as I add a second record:

DECLARE @Password_list TABLE (Password VARCHAR(50))
INSERT @Password_list (Password)
VALUES
('Orange'),
('Puppet')

I get this:

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near ','.

smithm55
Starting Member

9 Posts

Posted - 2012-08-02 : 15:48:16

The version is indeed the culprit. I was working on different SQL Servers, but still connecting back to my database that's (gulp) SQL 2000 SP4. I guess I'd have to upgrade this server in order to get this code to work.

smithm55
Starting Member

9 Posts

Posted - 2013-06-17 : 11:07:26

Lamprey, it looks like you're still around, so I wanted to resurrect this topic, if I may. I've upgraded my old server above to 2008. Let me ask a new question. If I copy and paste your code above, beginning with the DECLARE @Student TABLE..., everything works just as you described. I get a new word and number for every account, as I'd hoped.

However, as soon as I convert these into physical tables, things break down for me again, and I get the same word on every line.

For example, I'm curious if you would be willing to execute this code, and see if you get similar results:

==========

CREATE TABLE Student (Name VARCHAR(50))
INSERT Student
VALUES
('ABIGAIL'),
('BRANDON'),
('CAMERON'),
('CAPONE'),
('CHEYENNE'),
('CHLOE'),
('DAMARION'),
('DANIELLE'),
('DONTAVIAN')

CREATE TABLE PasswordList (Password VARCHAR(50))
INSERT PasswordList
VALUES
('Orange'),
('Rabbit'),
('Quacks'),
('Fudges'),
('Happy')

SELECT
Name,
(select top 1 password from passwordlist order by newid())
+ right('0' + CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 99 + 1 AS VARCHAR(2)), 2) AS Password
FROM
student

===========

Results:

Name Password
ABIGAIL Happy33
BRANDON Happy36
CAMERON Happy20
CAPONE Happy91
CHEYENNE Happy72
CHLOE Happy43
DAMARION Happy76
DANIELLE Happy16
DONTAVIAN Happy41

Thank you for any advice!!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2013-06-17 : 11:23:25

You might want to take a look at this for ideas.

Generate Password Procedure
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78859

CODO ERGO SUM

smithm55
Starting Member

9 Posts

Posted - 2013-06-17 : 12:05:51

quote:
You might want to take a look at this for ideas.

Generate Password Procedure
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78859 height="1" noshade id="quote">

Thanks Michael -- some good tips here, not nothing I could find to help me accomplish what I'm trying to do.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-06-17 : 13:28:13

You might try something like an OUTTER APPLY. Although you need to corrilate it in some way, even falsely, in order to get the resutls you want. Here is one way to do it:

SELECT
	Name,
	A.Password
FROM
	student AS s
OUTER APPLY
(
	select top 1 
		password + right('0' + CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 99 + 1 AS VARCHAR(2)), 2) AS Password
	from 
		passwordlist 
	WHERE
		s.Name <> '' -- Something that wil never be true
	order by newid()
	
) AS A

smithm55
Starting Member

9 Posts

Posted - 2013-06-17 : 15:58:58

I just did a little preliminary testing, but I *think* this is going to work. THANKS Lamprey!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources