Login using Entity with password encrypted as varbinary

Question

Login using Entity with password encrypted as varbinary

Navigation

#1 by (4 votes)
#2 by (1 votes)

4

I have encrypted the user's password and in the Password field of my table I went from Varchar to Varbinary, in the web part of my system everything is fine only in the windows part I am using Entity and I do not know how to do it validate the user's password, do not know if the EF has something of the type or I have to encrypt the password entered and pass by parameter to perform this query ...

    public static User ValidateUser(string UserName, string Password, ObjEntities cx)
    {
        var o = from c in cx.User
                where c.UserName == UserName && c.Password == Password
                select c;

        if (o.ToList().Count > 0)
            return o.Single();
        else
            return null;
    }

c# entity-framework varbinary

asked by anonymous 01.07.2014 / 21:18

2 answers

1

Translated / adapted response this answer in SOen

What you need to do is to encapsulate PWDCOMPARE into a UDF (user-defined function) in your database, and then call that UDF through your DataContext .

link

That is, run this in your database:

CREATE FUNCTION fn_PWDCOMPARE (@pwd NVARCHAR(MAX),@pwdhash NVARCHAR(MAX))
RETURNS BIT
BEGIN
  RETURN PWDCOMPARE(@pwd, @pwdhash)
END

Then add this to your DataContext in Visual Studio and call it this way:

var o = from c in cx.User
            where c.UserName == UserName && 
                  db.fn_PWDCOMPARE(Password, c.Password)
            select c;

P.S. The PWDENCRYPT function is an old function, and its use is discouraged in new projects. If the change is possible, consider using HASHBYTES instead of it (although reading the documentation, it seems that hash functions supported are not safe to protect passwords ... Unfortunately, I have no alternatives to suggest).

01.07.2014 / 21:44

Reading in Delphi generated XML in Excel Storyboard Follows

score 4 · Accepted Answer

Simple example:

I would create 2 Stored Procedure for the User Table, and would load inside the Entity Framework . Create Table User

CREATE TABLE [dbo].[User](
    [UserId] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [varchar](50) NULL,
    [Password] [varbinary](128) NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
    [UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Stored Procedure

CREATE PROCEDURE SP_Insert_User 
(
    @UserName varchar(50),
    @Password varchar(30)
)
AS
BEGIN
    INSERT INTO [User]([UserName], [Password]) VALUES(@UserName, PWDENCRYPT(@Password));
    SELECT [UserId], [UserName], [Password] FROM [User] WHERE [UserId] = @@IDENTITY;
END

CREATE PROCEDURE SP_User_Verify
(   
    @UserName varchar(50),
    @Password varchar(30)
)
AS
BEGIN
    SELECT * FROM [User] WHERE [User].[UserName] = @UserName AND 
                               PWDCOMPARE(@Password,[User].[Password]) = 1
END

The SP_Insert_User to enter new users and SP_User_Verify to verify the existence of the user with the return of their data.

In your Model, I import both Stored Procedure this way

Choosethetwowiththeirnamesthatinthecontextwillbecreatedtworeturnfunctionsinthisway:

public partial class ObjEntities : DbContext
{
    public ObjEntities()
        : base("name=ObjEntities") { }

    public DbSet<User> User { get; set; }   
    public virtual ObjectResult<SP_Insert_User_Result> SP_Insert_User(string userName, string password)
    {
        var userNameParameter = userName != null ?
            new ObjectParameter("UserName", userName) :
            new ObjectParameter("UserName", typeof(string));

        var passwordParameter = password != null ?
            new ObjectParameter("Password", password) :
            new ObjectParameter("Password", typeof(string));

        return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<SP_Insert_User_Result>("SP_Insert_User", userNameParameter, passwordParameter);
    }
    public virtual ObjectResult<SP_User_Verify_Result> SP_User_Verify(string userName, string password)
    {
        var userNameParameter = userName != null ?
            new ObjectParameter("UserName", userName) :
            new ObjectParameter("UserName", typeof(string));

        var passwordParameter = password != null ?
            new ObjectParameter("Password", password) :
            new ObjectParameter("Password", typeof(string));

        return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<SP_User_Verify_Result>("SP_User_Verify", userNameParameter, passwordParameter);
    }
}

How to use:

class Program
{
    static void Main(string[] args)
    {
        using (ObjEntities cx = new ObjEntities())
        {
            SP_User_Verify_Result user = ValidateUser("USUARIO2", "SENHA2", cx);
        }
    }
    public static SP_User_Verify_Result ValidateUser(string UserName, string Password, ObjEntities cx)
    {
        return cx.SP_User_Verify(UserName, Password).FirstOrDefault<SP_User_Verify_Result>();
    }
}

Debug:

IfinthislineSP_User_Verify_Resultuser=ValidateUser("USUARIO2", "SENHA2", cx); the variable user is null , then the user was not found, consequently, unauthorized user.