Convert binary to varchar on button click

Question

I'm trying to create a registration page for an SQL server, and I have some problems when it comes to the login section. The column Passphrase is set to dataType Binary(20). When a user registers an account from my form it enters their password in the Passphrase column as .

insertUser.Parameters.AddWithValue("@Passphrase", System.Text.Encoding.Default.GetBytes(TextBoxPass.Text));

When I create a Password using the default stored procedure for creating accounts it sets the password in the Passphrase column as "NULL".

ALTER FUNCTION [dbo].[FN_HashPassphrase2](
@vchPassphrase varchar(12),
@intAccountID int
)
RETURNS binary(20)
AS
BEGIN
RETURN (
    HASHBYTES('md5',@vchPassphrase)
);
END

When I try to log into an account that was created with the registration page I get password mismatch.

My question is this...

When someone enters their account info into the login fields, how can I convert the back into varchar(12) when the login button is clicked?

Here is my button click

protected void Button1_Click(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["RegDNMembershipConnectionString"].ConnectionString);
    con.Open();
    string cmdStr = "select count(*) from Accounts where AccountName='" + TextBox1.Text + "'";
    SqlCommand Checkuser = new SqlCommand(cmdStr, con);
    int temp = Convert.ToInt32(Checkuser.ExecuteScalar().ToString());
    if (temp == 1)
    {
        string cmdStr2="Select Passphrase from Accounts where AccountName='" + TextBox1.Text + "'";
        SqlCommand pass=new SqlCommand(cmdStr2, con);
        string password=pass.ExecuteScalar().ToString();
        con.Close();

Answer 1

To answer the question, as you haven't shown the actual code for comparing the passwords to the user entered text. I am assuming that you are trying to compare invalid data against each other. It is also worth noting that comparing binary will be Case Sensitive. (Yes I know binary doesnt have any concept of case sensitivity for the binary content).

The process I would take (if I had to use this example) is using a SP to create the user account and setting the binary passphrase using your existing function something like. (note this is quite basic and doesnt check if an account already exists).

CREATE PROCEDURE [dbo].[sp_CreateUser]
    @AccountName nvarchar(50),
    @Passphrase nvarchar(50)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Passbinary binary(20) = (SELECT [dbo].[FN_HashPassphrase2](@Passphrase))
    INSERT INTO [dbo].[Accounts] (AccountName, Passphrase) VALUES (@AccountName, @Passbinary)
END

Then create the user on your form such as...

using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["CON_String"].ConnectionString))
{
    var cmd = new SqlCommand("EXEC sp_CreateUser @p0, @p1", con);
    cmd.Parameters.AddWithValue("@p0", Username.Text);
    cmd.Parameters.AddWithValue("@p1", Password.Text);
    con.Open();
    cmd.ExecuteNonQuery();
    con.Close();
}

Now that gives us a result such as (yes basic).

So we now have the correct data in the database. Now for checking the user account I would use another stored procedure passing in the plain text of the AccountName and Passpharse using your existing hashing function to compare the password binary strings such as.

ALTER PROCEDURE sp_VerifyUser
    @AccountName nvarchar(50),
    @Passphrase nvarchar(50)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Passbinary binary(20) = (SELECT [dbo].[FN_HashPassphrase2](@Passphrase))
    DECLARE @AccountID int = (SELECT TOP 1 AccountID FROM [dbo].[Accounts] WHERE AccountName = @AccountName AND Passphrase = @Passbinary)
    SELECT TOP 1 CAST(CASE WHEN @AccountID IS NULL THEN -1 ELSE @AccountID END as INT)
END
GO

This simple SP will return a int (the account number) if the user exists by matching the account name and binary converted passphrase. If there isnt a match then -1 will be returned otherwise the accountID will be. Then from your code this is simple to call such as.

errorLbl.Text = "";
using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["CON_String"].ConnectionString))
{
    var cmd = new SqlCommand("EXEC sp_VerifyUser @p0, @p1", con);
    cmd.Parameters.AddWithValue("@p0", Username.Text);
    cmd.Parameters.AddWithValue("@p1", Password.Text);
    con.Open();
    var accountID = (int)cmd.ExecuteScalar();
    con.Close();
    if (accountID == -1)
        errorLbl.Text = "Username and password mismatch";
    else
    {
        errorLbl.Text = "YOUR GOOD TO GO! Have a nice day.. Your account ID is:" + accountID;
    }
}

As you can see this is quite a simple example.

What this will do

1. Binary store the password using a MD5 hash function
2. Compare user plain text username and password against a stored record calling the same hash function above

What this is missing

1. All your other account fields were not taken into consideration
2. Does not represent your actual database architecture.