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 fitcmd.Parameters.Add(
"@.UserName","test");cmd.Parameters.Add(
"@.Password","havinggoodday");// ideally, this key should be stored in the web.config filecmd.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
