December 4, 2014 at 7:39 am
Table to Update is CustomerCredit
- Fields are CustomerNumber, CreditLimit
Validation Table - UserAuthorization
-Field UserID, Password
I am writing an asp application where Fields on the asp form is CustomerNumber, CreditLimit,UserID & Password.
I would like to use a Update Statement like
UPDATE CustomerCredit
Set CreditLimit = @CreditLimit
with the validation from UserAuthorizationTable , matching UserID and Password must exist.
December 4, 2014 at 7:48 am
Validation meaning that only the user/pwd combination is allowed to update?
I'd use a stored proc. Pass in the values. Then use an IF statement to see if a row is returned from the validation table.
IF EXISTS(select name from user where user = user and pwd = pwd)
update
HOWEVER, please tell me you're not storing passwords in plain text. That's a huge security hole.
December 4, 2014 at 8:12 am
skb 44459 (12/4/2014)
Table to Update is CustomerCredit- Fields are CustomerNumber, CreditLimit
Validation Table - UserAuthorization
-Field UserID, Password
I am writing an asp application where Fields on the asp form is CustomerNumber, CreditLimit,UserID & Password.
I would like to use a Update Statement like
UPDATE CustomerCredit
Set CreditLimit = @CreditLimit
with the validation from UserAuthorizationTable , matching UserID and Password must exist.
Steve's question about passwords is very important but, notwithstanding that, you might be able to use a join:
update cc
Set CreditLimit = @CreditLimit
from [schemaname].CustomerCredit cc
join [schemaname].UserAuthorizationTable uat on ....
Note that you should always explicitly qualify your table names with their schema.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 5, 2014 at 1:08 pm
Thanks for th replies. The password is stored using AesCryptoServiceProvider (that's what I was told). Is there a way to decode is on the sql side?
December 9, 2014 at 10:05 am
skb 44459 (12/5/2014)
Thanks for th replies. The password is stored using AesCryptoServiceProvider (that's what I was told). Is there a way to decode is on the sql side?
Depends. If it's stored as a symmetric encoding, then yes. The AES algorithm is well known. Depends on where the key is and can you access it in the database.
If this is decrypt-able, it's a poor practice. Never, ever, ever, allow password decryption. Only resets.
December 9, 2014 at 12:04 pm
It hurts to love someone and not to be loved in return, but it hurts even more to have an UPDATE without a WHERE.
Try this example:
CREATE TABLE CustomerCredit(
CustomerNumber int,
CreditLimit decimal(10,2)
);
INSERT INTO CustomerCredit
VALUES
(1, 1235.46),
(2, 35.46),
(3, 199.99),
(4, 222.22),
(5, 100.00);
SELECT * FROM CustomerCredit;
DECLARE @CreditLimit decimal(10,2) = 999.99;
BEGIN TRAN; --Make it safe
UPDATE CustomerCredit
Set CreditLimit = @CreditLimit;
SELECT * FROM CustomerCredit;
ROLLBACK TRAN; --Undo the changes
--DROP TABLE CustomerCredit;
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply