|
One way encryption
Let me begin by saying that the examples in this article do not use the
Microsoft Cryptography API (System.Security.Cryptography).
Instead, I have used “pwdencrypt” and
“pwdcompare”, which are internal (and undocumented!!) functions
of SQL Server [version] used to manage passwords.
Pwdencrypt uses a one-way hash that takes a clear string and returns an
encrypted version of that string.
Pwdcompare
compares an unencrypted string with its encrypted representation to
check whether they match.
Let’s go through an example to see how to use these functions from .NET:
Sample table structure for storing the login information of
an user:
|
create table testlogin
(
uid varchar(10),
pwd
varbinary(255)
)
|
The subroutine below is used to store the user information after encrypting
the password:
private void cmdAdd_Click(object sender, System.EventArgs e)
{
SqlConnection cn = new SqlConnection(ConnectionString);
cn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "insert into testlogin values ("
+ "'" + txtUserName.Text + "',"
+ "convert(varbinary(255), pwdencrypt (" + "'"
+ txtPassword.Text + "'" + ")))";
cmd.ExecuteNonQuery();
}
|
For readability purposes, I have shown the “insert” statements in multiple lines.
It has to be in a single line for proper execution.
Moreover,
it is not advisable to write queries directly in the front end, so in the real world we would be using stored procedures.
Now let’s have a look at the procedure used to authenticate user data. The
procedure checkLogin accepts a username and a
password as input parameters and returns 0 or 1 as
its output value.
Procedure used to authenticate an user
create procedure checkLogin
(
@uid varchar(255),
@pwd varchar(255),
@error int = 0 output
)
As
if exists (select * from testlogin where uid=@uid and 1 = pwdcompare(@pwd,pwd,0))
select @error = 1
else
select @error = 0
|
The procedure is self-explanatory. The third
parameter of pwdCompare
is provided for backward compatibility (with earlier versions of SQL Server). While
comparing passwords encrypted in SQL Server 6.5, we need to pass 1 for this value.
The complete code-behind source code follows:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
namespace testing
{
public class WebForm2 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.RequiredFieldValidator RFVPassword;
protected System.Web.UI.WebControls.RequiredFieldValidator RFVUserName;
protected System.Web.UI.WebControls.Button cmdAdd;
protected System.Web.UI.WebControls.TextBox txtUserName;
protected System.Web.UI.WebControls.Label Label2;
protected System.Web.UI.WebControls.Label Label1;
protected System.Web.UI.WebControls.Button cmdLogin;
protected System.Web.UI.WebControls.Label message;
protected System.Web.UI.HtmlControls.HtmlForm Form1;
protected System.Web.UI.WebControls.TextBox txtPassword;
string ConnectionString;
public WebForm2()
{
ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["constr"];
}
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
}
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.cmdAdd.Click += new System.EventHandler(this.cmdAdd_Click);
this.cmdLogin.Click += new System.EventHandler(this.cmdLogin_Click);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
private void cmdAdd_Click(object sender, System.EventArgs e)
{
SqlConnection cn = new SqlConnection(ConnectionString);
cn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "insert into testlogin values (" + "'" + txtUserName.Text + "'," + " convert(varbinary(255),pwdencrypt(" + "'" + txtPassword.Text + "'" + ")))";
cmd.ExecuteNonQuery();
}
private void cmdLogin_Click(object sender, System.EventArgs e)
{
SqlConnection cn = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand("checkLogin",cn);
cmd.CommandType = CommandType.StoredProcedure;
// Adding the first Input parameter
SqlParameter workParam = cmd.Parameters.Add(new SqlParameter("@uid",SqlDbType.VarChar,255));
workParam.Value = txtUserName.Text;
// Adding the second Input parameter
SqlParameter workParam1 = cmd.Parameters.Add(new SqlParameter("@pwd",SqlDbType.VarChar,255));
workParam1.Value = txtPassword.Text;
// Adding the output parameter
SqlParameter workParam2 = cmd.Parameters.Add(new SqlParameter("@error",SqlDbType.Int, 4));
workParam2.Direction = ParameterDirection.Output;
try
{
// Opening a connection.
cn.Open();
cmd.ExecuteScalar();
object LoginResult = workParam2.Value;
LoginResult = LoginResult.ToString();
// Assigning the value 0 (fail) or 1 (success) to the label control
if (LoginResult.ToString() == "1")
message.Text = "You are Authorized !";
else
message.Text = "You aren't authorized :-(";
}
catch (Exception ex)
{
message.Text = ex.Message;
}
finally
{
// close the connection
cn.Close();
}
}
}
}
|
Note:
If a user forgets his password,
we could reset it to some random value. Intriguingly, if you use one
way encryption there’s no way to determine what exactly the user’s password
was.
|
Install, Configure, Forget!