encrypying and decrypting the password variable

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

Transact-SQL (2000)

encrypying and decrypting the password variable

Author

Topic

stats
Starting Member

10 Posts

Posted - 2006-02-04 : 10:22:52

After searching on the internet, I've not had any luck. Can anyone suggest anything.

This is what I currently hav (without any encryption processes):

ALTER PROCEDURE registerUser
@userName char(16),
@userPass char(16),
@fullName char(10),
@addr1 char(15),
@addr2 char(15),
@city char(10),
@postcode char(8),
@tel char(4),
@email char(30)
AS
-- Make sure username does'nt already exist
IF EXISTS (SELECT * FROM userTable WHERE userName = @userName)
RETURN -1
ELSE
BEGIN
--Insert new row into user table
INSERT INTO userTable
(userName, userPass, fullName, addr1, addr2, city, postcode, tel, email)
VALUES (@userName, @userPass, @fullName, @addr1, @addr2, @city, @postcode, @tel, @email)
RETURN 1
END

ALTER PROCEDURE userLogin
@userName char(16),
@userPass char(16)
AS
-- Make sure username and password is correct
IF EXISTS (SELECT userID, userName, userPass
FROM userTable
WHERE userName = @userName AND userPass = @userPass)
BEGIN
SELECT userID, userName, userPass
FROM userTable
WHERE userName = @userName AND userPass = @userPass
RETURN 1
END
GO

STaTs

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-04 : 10:28:08

How secure do you want it to be?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

stats
Starting Member

10 Posts

Posted - 2006-02-04 : 10:54:12

a simple encryption is all I need here and now.

STaTs

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-04 : 11:15:21

Then

ALTER PROCEDURE registerUser
@userName char(16),
@userPass char(16),
@fullName char(10),
@addr1 char(15),
@addr2 char(15),
@city char(10),
@postcode char(8),
@tel char(4),
@email char(30)
AS
declare @encryptpwd varchar(1000)

select @encryptpwd = reverse(@userPass)

--Insert new row into user table
INSERT INTO userTable
(userName, userPass, fullName, addr1, addr2, city, postcode, tel, email)
select @userName, @encryptpwd, @fullName, @addr1, @addr2, @city, @postcode, @tel, @email
where not exists (select * from userTable where userName = @userName

if @@rowcount <> 1
RETURN -1
else
RETURN 1
END

ALTER PROCEDURE userLogin
@userName char(16),
@userPass char(16)
AS
declare @encryptpwd varchar(1000)
declare @userid int
select @encryptpwd = reverse(@userPass)
-- Make sure username and password is correct

SELECT @userID = userID
FROM userTable
WHERE userName = @userName AND userPass = @encryptpwd

if @userID is not null
begin
select userid = @userid
RETURN 1
end
GO

I've just used reverse here.
It's important to keep the encryption separate from the rest of the code as it may change.
Also never decrypt what is in the table to check the password. You may go to an encryption where that's not possible.

Another easy encryption is xor'ing the bits which will give you something that is at least unreadable.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-04 : 11:36:22

Play around with something like this

declare @encryptpwd varchar(1000)
declare @i int

select @encryptpwd = @userPass
select @i = 0
while @i < len(@userpwd)
begin
select @i = @i + 1
select @encryptpwd = stuff(@encryptpwd, @i, 1, char(ascii(substring(@userpwd, @i, 1))^100))
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

stats
Starting Member

10 Posts

Posted - 2006-02-04 : 12:16:34

thanks. what does the "stuff" method do?

STaTs

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-04 : 12:18:31

stuff deletes and inserts characters in a string. In this case it just deletes one char ind inserts another at the same place - in effect replacing it.
Have a look in bol.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources