C＃使用SHA1在Sql Server 2008上加密密码 [英] C# Encrypt passwords on Sql Server 2008 using SHA1

问题描述

我使用c＃设计了一个登录系统，其中在加载主页面之前在SQL Server 2008中检查用户名和密码。我希望加密数据库上存储的密码。是否可以使用c＃和SHA1来完成算法？



以下是我的存储过程

I have designed a Log In System using c# where the username and password is checked in SQL server 2008 before loading the main page.I wish to encrypt the stored password on the database.Is it possible to do it using c# and SHA1 algorithm?

Following is my stored procedure

ALTER procedure [dbo].[proc_UserLogin]
 @userid varchar(20),
  @password nvarchar(50)
  As 

  declare
  @ReturnVal              varchar(500)


SET NOCOUNT ON      

  if exists(select userid,password from LoginManager where userid=@userid and password=@password)
  set @ReturnVal='0|Logged in Successfully'
  else
  set @ReturnVal='1|Login Failed/Username does not exist'

  select @ReturnVal

C＃Code

C# Code

public void button1_Click(object sender, EventArgs e)
        {
            mainform = new Form1();
            string[] v;

            OleDbConnection conn = new OleDbConnection("File Name=E:\\Vivek\\License Manager\\License Manager\\login.udl");

            try
            {

                conn.Open();
                string query = "EXEC dbo.proc_UserLogin'" + username.Text+ "', '" + password.Text+"'";
                OleDbCommand cmd = new OleDbCommand(query, conn);
                string s = Convert.ToString(cmd.ExecuteScalar());
                v= s.Split('|');
                if (v[0]=="0")
                {
                    mainform.Show();
                    this.Hide();
                }
                else
                {
                    MessageBox.Show("Please enter correct user credentials and try again");
                }


             }

             catch (Exception ex)
             {
                 MessageBox.Show(ex.Message);
             }


              conn.Close();

         }

任何人都可以建议更改代码，以便完成密码加密。？



谢谢

Can anyone suggest changes to the code ,so that password encryption can be accomplished.?

Thanks

推荐答案

您可以使用SQL Server中的标量函数加密用户密码。

让我们看看



在SSMS中执行以下代码（函数返回类型下面是varchar）

You can Encrypt a user password using Scalar function in SQL Server.
lets see

Execute the below code in SSMS( Below function return type is varchar )

USE [DB_NAME]
GO
/****** Object:  UserDefinedFunction [dbo].[PSWD_ENCRYPT]    Script Date: 10/8/2014 10:36:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<MAHESH>
-- Create date: <04-11-2013>
-- Description:	<Password Encryption using SHA Algorithm>
-- =============================================
Create FUNCTION [dbo].[PSWD_ENCRYPT] 
(
	-- Add the parameters for the function here
	@pwd varchar(50)

)
returns varchar(50)
AS
BEGIN
	-- Declare the return variable here
	declare @Result varchar(50);
		declare @hexbin varbinary(max)
	set @hexbin = HASHBYTES('SHA1',HashBytes('SHA1', @pwd));
	set @Result = (select  cast('' as xml).value('xs:hexBinary(sql:variable("@hexbin") )', 'varchar(max)'));
	-- Return the result of the function
	RETURN  @Result

END

用法



插入

Usage

Insertion

declare @pswd varchar(50)
set @pswd='mahesh';
insert into dbo.users(uid,pswd) values ('mahesh',dbo.PSWD_ENCRYPT(@pswd))

选择：

Select:

declare @pswd varchar(50)
set @pswd='mahesh';
select uid from dbo.users where uid=@uid and pswd=dbo.PSWD_ENCRYPT(@pswd)

更新：

Update :

update dbo.users set pswd=dbo.PSWD_ENCRYPT(@pswd) where .....

这篇关于C＃使用SHA1在Sql Server 2008上加密密码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！