update issue - SQL Server Forums

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

update issue

Author

Topic

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-05-07 : 11:20:58

i am working on a small password management system, i have table called tbl_userpass.that have
following columns :

userid password oldpass1 oldpass2 oldpass3 oldpass4 oldpass5

i need to do that when i change a paasword the current password is updated in oldpass1
and new password is updated in password column. if user again change the password the new password
is update INFORMATION_SCHEMA password and current is update In oldpass1 and value of oldpass1 is updated in
oldpass2. if again then so on.

please help what should i do.

challenge everything

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-05-07 : 11:58:00

you can always do:


update tbl_userpass
set oldpass5 = oldpass4,
    oldpass4 = oldpass3,
    oldpass3 = oldpass2,
    oldpass2 = oldpass1,
    oldpass1 = password,
    password=<YourNewValue>

No, you're never too old to Yak'n'Roll if you're too young to die.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-05-07 : 14:21:02

or, you could take this opportunity and get your tables a little closer to First Normal Form!

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-07 : 16:59:16

I second Don here. Would be much better to store it as rows like

userid password effdate

which will give details of password and date from which its effective. Using this approach you can get password which was used at any point of time

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-05-08 : 03:16:14

I think if someone is asking about that simple update then he isn't able to change the database design, hence I was not going to point him in that direction.

No, you're never too old to Yak'n'Roll if you're too young to die.

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-05-08 : 23:10:52

thanks for all suggestions. now there is some complications that there is table where i store
the last passowrd not used flag it can be 1 to 8 .if 2 then value in password1 and password2 can not be used for new password. please suggest how can i implement this

challenge everything

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-08 : 23:13:50

quote:
Originally posted by pnpsql

thanks for all suggestions. now there is some complications that there is table where i store
the last passowrd not used flag it can be 1 to 8 .if 2 then value in password1 and password2 can not be used for new password. please suggest how can i implement this

challenge everything

This can be accomplish easily if the table is normalized. Any chance of changing the table design ?

KH
[spoiler]Time is always against us[/spoiler]

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-05-08 : 23:15:32

can not change the table , can create a table variable in code please suggest

challenge everything

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-08 : 23:23:07

assuming the normalized table is like as suggested by visakh


userid password effdate

you can get the "not used" password simply


select TOP ( 2 ) password
from   password_table
where  user_id = <some user>
order by effdate desc

KH
[spoiler]Time is always against us[/spoiler]

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-05-09 : 03:25:51

can it be done by cte , becaue there is no scope of alter table

challenge everything

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-09 : 04:15:32

[code]
; with cte as
(
select userid, pw_no = 1, password from tbl_userpass union all
select userid, pw_no = 2, password = oldpass1 from tbl_userpass union all
select userid, pw_no = 3, password = oldpass2 from tbl_userpass
....
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-09 : 16:19:53

quote:
Originally posted by pnpsql

thanks for all suggestions. now there is some complications that there is table where i store
the last passowrd not used flag it can be 1 to 8 .if 2 then value in password1 and password2 can not be used for new password. please suggest how can i implement this

challenge everything

show some data and explain to us how data is in table currently

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-05-10 : 01:10:42

userid password password1 password2 password3 password4 password5
10001 asasa dsd34sd sdsdsds sdcxc343 sd76hh sdsds4343

the current password is in password feild.

now i need to update password field with new password but
the new password ca not be in any other password field
and the decision that how many old password ca not be used is also at runtime
and we need to get it from some master table.

here is my approach :

declare @ploginname varchar(max)
declare @sqlcmd nvarchar(max)
declare @vid numeric(10)
declare @vpass varchar(max)
DECLARE @retval NVARCHAR(MAX)

BEGIN

SET @PLOGINNAME = 'USER1';

SELECT @VID = PREVPASSNOTUSED
FROM QC_MASTER_FINAL.DBO.QM_POLICY_PARAMETER

set @vpass = 'SASASASAS8'

SET @SQLCMD = ';WITH CTE AS
(
SELECT PW_NO = 1, PASSWORD FROM QM_USER1 WHERE LOGINNAME = ' + '''' + @PLOGINNAME + ''''+' UNION ALL
SELECT PW_NO = 2, PASSWORD = PASSWORD1 FROM QM_USER1 WHERE LOGINNAME = ' + '''' + @PLOGINNAME + '''' + ' UNION ALL
SELECT PW_NO = 3, PASSWORD = PASSWORD2 FROM QM_USER1 WHERE LOGINNAME = ' + '''' + @PLOGINNAME + '''' + ' UNION ALL
SELECT PW_NO = 4, PASSWORD = PASSWORD3 FROM QM_USER1 WHERE LOGINNAME = ' + '''' + @PLOGINNAME + '''' + ' UNION ALL
SELECT PW_NO = 5, PASSWORD = PASSWORD4 FROM QM_USER1 WHERE LOGINNAME = ' + '''' + @PLOGINNAME + '''' + ' UNION ALL
SELECT PW_NO = 6, PASSWORD = PASSWORD5 FROM QM_USER1 WHERE LOGINNAME = ' + '''' + @PLOGINNAME + '''' + ' UNION ALL
SELECT PW_NO = 7, PASSWORD = PASSWORD6 FROM QM_USER1 WHERE LOGINNAME = ' + '''' + @PLOGINNAME + '''' + ' UNION ALL
SELECT PW_NO = 8, PASSWORD = PASSWORD7 FROM QM_USER1 WHERE LOGINNAME = ' + '''' + @PLOGINNAME + '''' + ' UNION ALL
SELECT PW_NO = 9, PASSWORD = PASSWORD8 FROM QM_USER1 WHERE LOGINNAME = ' + '''' + @PLOGINNAME + '''' + '
)
SELECT top ' + CAST(@VID as varchar(max)) + ' PASSWORD FROM CTE order by PW_NO ' ;

IF @VPASS NOT IN (@SQLCMD)

BEGIN

PRINT 'ok'

END

END

it is not working. please suggest

challenge everything

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-10 : 02:18:05

why are you using Dynamic SQL ?

you form the SQL Statement but never execute it

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-10 : 02:34:18

[code]
IF NOT EXISTS
(
SELECT *
FROM (
SELECT LOGINNAME, PW_NO = 1, PASSWORD = PASSWORD FROM QM_USER1 UNION ALL
SELECT LOGINNAME, PW_NO = 2, PASSWORD = PASSWORD1 FROM QM_USER1 UNION ALL
...
) P
WHERE LOGINNAME = @PLOGINNAME
AND PW_NO <= @VID
AND PASSWORD = @VPASS
)
BEGIN
PRINT 'OK'
END
[/code]

KH
[spoiler]Time is always against us[/spoiler]

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-05-10 : 23:11:42

got it what i need , thanks a lot. it works very well .

challenge everything

pnpsql
Posting Yak Master

246 Posts

Posted - 2012-05-10 : 23:20:36

As discussed earlier that i am working on password manage ment project,
there is a login page on my application, where the user enters the user name and password
tthe password get encrypted in sha 256 hash and match the value of hash with already saved hash password for that user.
when both hashes are matched then user is allowed for login. there is a password change page where user can change its password and the new password hash is saved in table.
the same code is called for bot hashing and this is written in java.

now there is some module enhancement where we need that when user does not login for 3 dayus then its account is locked and
need to reset by admin. the problem arises here i write a clr code that converts a string to
sha 256 hash and call it in proc, after reset the passoword when user tries to enter in application
it returns error that invalid password , becasue there are some mismacth in java hashing that is applies on front end of application and the clr hashing .

please suggets the best way that i can use.

challenge everything

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources