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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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