how to encrypt my password or sensitive data before storing them in a database , using SQL

Hi there ,

1. i have a database and i want to encrypt my passwords before storing my records in a database plus i will later on would require to authenticate my user so again i have to encrypt the string provided by him to compare it with my encrypted password in database

below is my code , i dont know how to do it , plz help

2. one thing more i am storing IP addresses of my users as a "varchar" is there a better method to do it , if yes plz help me

try
{
SqlConnection myConnection = new SqlConnection();
myConnection.ConnectionString = ConfigurationManager.ConnectionStrings["projectConnectionString"].ConnectionString;

SqlDataAdapter myAdapter = new SqlDataAdapter("SELECT *From User_Info", myConnection);
SqlCommandBuilder builder = new SqlCommandBuilder(myAdapter);
DataSet myDataset = new DataSet();
myAdapter.Fill(myDataset, "User_Info");

//Adding New Row in User_Info Table
DataRow myRow = myDataset.Tables["User_Info"].NewRow();
myRow["user_name"] = this.user_name.Text;
myRow["password"] = this.password.Text; // shoule be encrypted
//not known till now how to do it

myRow["name"] = this.name.Text;
myRow["ip_address"] = this.ip_address.Text;

myDataset.Tables["User_Info"].Rows.Add(myRow);
myAdapter.Update(myDataset, "User_Info");

myConnection.Close();
myConnection.Dispose();

}
catch (Exception ex)
{
this.error.Text = "Error ocurred in Creating User : " + ex.Message;
}

Hello my friend, I will give you the answer to this.

Firstly, run the following SQL to create the encrypt function: -

CREATE FUNCTION fn_AlterString
(
@.String AS VARCHAR(800),
@.Key AS VARCHAR(80),
@.Direction AS BIT
)

RETURNS VARCHAR(800)

AS

BEGIN

DECLARE @.NewString AS VARCHAR(800)
SET @.NewString = ''

DECLARE @.Keyi AS INT
SET @.Keyi = 1

DECLARE @.i AS INT
SET @.i = 1

WHILE @.i <= LEN(@.String)
BEGIN
IF (@.Direction = 1)
BEGIN
SET @.NewString = @.NewString + CHAR(ASCII(SUBSTRING(@.String, @.i, 1))
+ CAST(SUBSTRING(@.Key, @.Keyi, 1) AS INT))
END
ELSE BEGIN
SET @.NewString = @.NewString + CHAR(ASCII(SUBSTRING(@.String, @.i, 1))
- CAST(SUBSTRING(@.Key, @.Keyi, 1) AS INT))
END

IF @.Keyi < LEN(@.Key)
BEGIN
SET @.Keyi = @.Keyi + 1
END
ELSE BEGIN
SET @.Keyi = 1
END

SET @.i = @.i + 1
END

RETURN @.NewString

END

Now run the following SQL that will create a stored procedure that your web page will use: -

CREATE PROCEDURE usp_InsertUser
(
@.UserName AS VARCHAR(50),
@.Password AS VARCHAR(50),
@.Key AS VARCHAR(80)
)

AS

INSERT INTO User_Info
( [User_Name],
[Password]
)
VALUES
( @.UserName,
dbo.fn_AlterString(@.Password, @.Key, 1)
)

RETURN

Now alter your web page code as follows: -

SqlConnection

conn =newSqlConnection(ConfigurationManager.ConnectionStrings["projectConnectionString"].ConnectionString);SqlCommand cmd =newSqlCommand("usp_InsertUser", conn);

cmd.CommandType =

CommandType.StoredProcedure;// change these as you see fit

cmd.Parameters.Add(

"@.UserName","test");

cmd.Parameters.Add(

"@.Password","havinggoodday");// ideally, this key should be stored in the web.config file

cmd.Parameters.Add(

"@.Key","564335567754326769012342896");

cmd.ExecuteNonQuery();

conn.Close();

Now for an explanation. The fn_AlterString() function takes a string and transforms it into another based on the second parameter, the key. The third parameter is 1 to encrypt and 0 to decrypt.

Therefore: -

SELECT dbo.fn_AlterString('havinggoodday', '564335567754326769012342896', 1) gives you mgzlqlluvkie|

SELECT dbo.fn_AlterString('mgzlqlluvkie|', '564335567754326769012342896', 0) gives you havinggoodday

You will need to do the latter when checking the password of the user when they log in.

Obviously you will need to use the same key when encrypting and decrypting. They key can only contain numbers. The more digits, the more secure. You can have a larger key than the one used but I put an 80 limit in the function parameter, which you can extend. The key is passed to the stored procedure and in turn passed to the function then used within it, so if somebody stole your database data, your stored procedure code and function code they still would not be able to read your encrypted data because they would need the key, which I suggest you put in the web.config file.

As for your second question, using a varchar to store an ip address is fine. If you need any more help, let me know.

Kind regards

Scotty

|||

hi thanks for your kind help ,

i haven't seen anyone that cool to write an entire code for somebody for free , i appreciate that

one more thing :

can you please tell me how to store and retrieve the key for encryption from web.config file

best regards

gurpreet

|||

Hi Gurpreet,

Within the web.config file add the following within the appSettings section: -

<

appSettings>

<

addkey="VisitationsExpected"value="45545454545445544554"/>

</

appSettings>

Notice the name I have chosen? Call it anything but decryption key. Then within your page code: -

string

strKey =ConfigurationSettings.AppSettings["VisitationsExpected"];

Thanks for the appreciation. I do not appreciate it when users leave one line answers and expect you to know what they mean exactly to fit your situation. I like to take a task to completion. All I ask is that you mark me as the answerer for your question. If you have any more problems, put them in the forum and send me a private message so I am made aware of it.

Kind regards

Scotty

sql