October 19, 2018 at 3:03 pm
Hello, I am working on a Stored Procedure to validate user registration for username, email, and password. For each, I am checking/validating multiple scenarios. For the interface, if validation criteria is not met, I would like to "bubble up" all errors for each validation that are applicable. Currently, my current code will only "bubble up" the first validate error it finds. Is there a way to go through the entire PROC, then bubble up all applicable error messages? Then, if no errors are found, do an INSERT statement? Should I be using a TRY CATCH to do all of this?
Here is my code:
DECLARE
@Username varchar(100),
@FirstName varchar(100),
@LastName varchar(100),
@Email varchar(100),
@Pwd varchar(128),
@Msg varchar(100) OUTPUT
SET @Username = 'SQLRookie'
SET @Email = 'jdoe@SQLRookie.com'
SET @FirstName = 'John'
SET @LastName = 'Doe'
SET @Pwd = 'ABC123xyz!'
-- ===================================================================================================================================
-- -- Create and Validate Username
-- ===================================================================================================================================
IF (SELECT 1 FROM Usertbl WHERE Username = @username) = 1
BEGIN
PRINT 'This username is already regsitered, please try again'
SET @Msg = 'This username is already regsitered, please try again'
END
IF (LEN(@username) < 5) BEGIN
PRINT 'Username must contain at least 5 characters'
SET @Msg = 'Username must contain at least 5 characters'
END
-- ===================================================================================================================================
-- Create and Validate Email
-- ===================================================================================================================================
IF (SELECT 1 FROM Usertbl WHERE Email = @Email) = 1
BEGIN
PRINT 'This email is already registered. Forgot your password?'
SET @Msg = 'This email is already registered. Forgot your password?'
END
IF (@EMAIL <> '' AND @EMAIL NOT LIKE '_%@__%.__%')
BEGIN
PRINT '- Please enter a valid email address'
SET @Msg = '- Please enter a valid email address'
END
-- ===================================================================================================================================
-- -- Create and Validate Password
-- ===================================================================================================================================
IF @Pwd = LOWER(@Pwd) COLLATE Latin1_General_CS_AI BEGIN
PRINT '- The string must contain at least one uppercase character.'
SET @Msg = '- The string must contain at least one uppercase character.'
END
IF @Pwd = UPPER(@Pwd) COLLATE Latin1_General_CS_AI
BEGIN
PRINT '- The string must contain at least one lowercase character.'
SET @Msg = '- The string must contain at least one lowercase character.'
END
-- IF no error than INSERT INTO Table
October 20, 2018 at 8:59 am
You shouldn't be validating password in the SQL in the first place. The fact that you are means you are passing the password as Rae text to the SQL Server (a bad idea) and you might be storing it as plan text. Validation should be done on the client side and passwords should be hashed and salted in the DBMS.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 22, 2018 at 7:00 am
Thank you for your reply, this makes a lot more sense! Yes, later on I am doing Hash and Salt but did not include in the sample. Also, would you recommend doing the Hash/Salt as a function? Thanks again.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply