April 14, 2015 at 11:07 am
Can anyone tell me what I've done wrong with the following function? I suspect it's something obvious that I just cant see.
CREATE FUNCTION [dbo].[CheckLoginDetails_Username](@SuppliedUsername nvarchar(150), @SuppliedPassword nvarchar(150)) RETURNS bit
AS
BEGIN
DECLARE @Resultbit
DECLARE @MyUsernamenvarchar(150)
DECLARE @MyPasswordnvarchar(150)
SELECT @MyUsername = Username FROM Users WHERE Username = @SuppliedUsername
SELECT @MyPassword = Password FROM Users WHERE Username = @SuppliedUsername
IF ISNULL(@MyUsername,'') = ''
BEGIN
SELECT @Result = 0
END
ELSE
IF @MyPassword = @SuppliedPassword
BEGIN
SELECT @Result = 1
END
ELSE
BEGIN
SELECT @Result = 0
END
RETURN @Result
END
The trouble lies with the 2 lines immediately after the variable declarations. @MyUsername and @MyPassword are never being set despite the fact that the correct values are being passed to the function and there is a matching record in the Users table.
Any ideas?
April 14, 2015 at 12:30 pm
NHS Baz (4/14/2015)
Can anyone tell me what I've done wrong with the following function? I suspect it's something obvious that I just cant see.CREATE FUNCTION [dbo].[CheckLoginDetails_Username](@SuppliedUsername nvarchar(150), @SuppliedPassword nvarchar(150)) RETURNS bit
AS
BEGIN
DECLARE @Resultbit
DECLARE @MyUsernamenvarchar(150)
DECLARE @MyPasswordnvarchar(150)
SELECT @MyUsername = Username FROM Users WHERE Username = @SuppliedUsername
SELECT @MyPassword = Password FROM Users WHERE Username = @SuppliedUsername
IF ISNULL(@MyUsername,'') = ''
BEGIN
SELECT @Result = 0
END
ELSE
IF @MyPassword = @SuppliedPassword
BEGIN
SELECT @Result = 1
END
ELSE
BEGIN
SELECT @Result = 0
END
RETURN @Result
END
The trouble lies with the 2 lines immediately after the variable declarations. @MyUsername and @MyPassword are never being set despite the fact that the correct values are being passed to the function and there is a matching record in the Users table.
Any ideas?
The first issue I see is that it appears that you are storing passwords in plain text. This type of information should encrypted with a one-way hash. The next issue is that this is a scalar function. It would be faster and more flexible if it was an inline table valued function instead. That leads me to the last issue, this entire thing should be a single select statement. There is no need for scalar variables and if then logic.
To simply answer your question and not try to help you make this better, the logic error is here:
SELECT @MyPassword = Password FROM Users WHERE Username = @SuppliedUsername
You should be looking at password, not username.
Here is how I would think about writing this. This is now an inline table valued function which means it will be faster and will scale a lot easier.
CREATE FUNCTION [dbo].[CheckLoginDetails_Username]
(
@SuppliedUsername nvarchar(150)
, @SuppliedPassword nvarchar(150)
) RETURNS table with schemabinding as
return
select cast(count(*) as bit) as IsValid
FROM Users
WHERE Username = @SuppliedUsername
and Password = @SuppliedPassword
and Password > ''
--EDIT--
Added additional predicate to ensure that the stored password is > ''.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 14, 2015 at 12:31 pm
NHS Baz (4/14/2015)
Can anyone tell me what I've done wrong with the following function? I suspect it's something obvious that I just cant see.CREATE FUNCTION [dbo].[CheckLoginDetails_Username](@SuppliedUsername nvarchar(150), @SuppliedPassword nvarchar(150)) RETURNS bit
AS
BEGIN
DECLARE @Resultbit
DECLARE @MyUsernamenvarchar(150)
DECLARE @MyPasswordnvarchar(150)
SELECT @MyUsername = Username FROM Users WHERE Username = @SuppliedUsername
SELECT @MyPassword = Password FROM Users WHERE Username = @SuppliedUsername
IF ISNULL(@MyUsername,'') = ''
BEGIN
SELECT @Result = 0
END
ELSE
IF @MyPassword = @SuppliedPassword
BEGIN
SELECT @Result = 1
END
ELSE
BEGIN
SELECT @Result = 0
END
RETURN @Result
END
The trouble lies with the 2 lines immediately after the variable declarations. @MyUsername and @MyPassword are never being set despite the fact that the correct values are being passed to the function and there is a matching record in the Users table.
Any ideas?
You do have only one record returned? Also the two selects could be done with one.
April 14, 2015 at 1:00 pm
CREATE FUNCTION [dbo].[CheckLoginDetails_Username] (
@SuppliedUsername nvarchar(150),
@SuppliedPassword nvarchar(150)
)
RETURNS bit
AS
BEGIN
RETURN (
SELECT CASE
WHEN @SuppliedPassword = (SELECT Password FROM dbo.Users WHERE Username = @SuppliedUsername)
THEN 1
ELSE 0 END AS Result
)
END
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply