我正在尝试创建一个登录表单，该表单使用存储过程来检查用户是否存在，并使用VS2015&SQL Server 2012。
我已经创建了下表
CREATE TABLE dbo.[User]
(
UserID INT IDENTITY(1,1) NOT NULL,
LoginName NVARCHAR(40) NOT NULL,
PasswordHash BINARY(64) NOT NULL,
FirstName NVARCHAR(40) NULL,
LastName NVARCHAR(40) NULL,
CONSTRAINT [PK_User_UserID] PRIMARY KEY CLUSTERED (UserID ASC)
)
并运行以下程序更新表
ALTER TABLE dbo.[User] ADD Salt UNIQUEIDENTIFIER
GO
ALTER PROCEDURE dbo.uspAddUser
@pLogin NVARCHAR(50),
@pPassword NVARCHAR(50),
@pFirstName NVARCHAR(40) = NULL,
@pLastName NVARCHAR(40) = NULL,
@responseMessage NVARCHAR(250) OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @salt UNIQUEIDENTIFIER=NEWID()
BEGIN TRY
INSERT INTO dbo.[User] (LoginName, PasswordHash, Salt, FirstName, LastName)
VALUES(@pLogin, HASHBYTES('SHA2_512', @pPassword+CAST(@salt AS NVARCHAR(36))), @salt, @pFirstName, @pLastName)
SET @responseMessage='Success'
END TRY
BEGIN CATCH
SET @responseMessage=ERROR_MESSAGE()
END CATCH
END
全部基于此来源https://www.mssqltips.com/sqlservertip/4037/storing-passwords-in-a-secure-way-in-a-sql-server-database/
我已经创建了一个简单的表单与用户和密码框提交按钮我正在使用以下代码提交按钮
Private Sub RadButtonSubmit_Click(sender As Object, e As EventArgs) Handles RadButtonSubmit.Click
Dim dt As New DataTable()
Dim adp As New SqlDataAdapter()
Try
Dim con As New SqlConnection(My.Settings.Connection)
Dim cmd As New SqlCommand("uspLogin", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@pLoginName", RadTextBoxUser.Text.Trim())
cmd.Parameters.AddWithValue("@pPassword", RadTextBoxPass.Text.Trim())
adp.SelectCommand = cmd
adp.Fill(dt)
cmd.Dispose()
If dt.Rows.Count > 0 Then
RadLabelMessage.Text = "Login Successfull"
'Or in show messagebox using ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Login Successfull');", true);
'Or write using Response.Write("Login Successfull");
'Or redirect using Response.Redirect("Mypanel.aspx");
Else
RadLabelMessage.Text = "Wrong Username/Password"
'Or show in messagebox usingScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Wrong Username/Password');", true);
'Or write using Response.Write("Wrong Username/Password");
End If
Catch ex As Exception
'RegisterStartupScript(Me, Me.[GetType](), "Message", "alert('Oops!! following error occured : " & ex.Message.ToString() & "');", True)
' Response.Write("Oops!! following error occured: " +ex.Message.ToString());
Finally
dt.Clear()
dt.Dispose()
adp.Dispose()
End Try
End Sub
当我输入用户名和密码时，它总是显示"错误的用户名/密码"
我添加了使用的用户
DECLARE @responseMessage NVARCHAR(250)
EXEC dbo.uspAddUser
@pLogin = N'Admin',
@pPassword = N'123',
@pFirstName = N'Admin',
@pLastName = N'Administrator',
@responseMessage=@responseMessage OUTPUT
SELECT *
FROM [dbo].[User]
这是登录的存储过程
CREATE PROCEDURE dbo.uspLogin
@pLoginName NVARCHAR(254),
@pPassword NVARCHAR(50),
@responseMessage NVARCHAR(250)='' OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @userID INT
IF EXISTS (SELECT TOP 1 UserID FROM [dbo].[User] WHERE LoginName=@pLoginName)
BEGIN
SET @userID=(SELECT UserID FROM [dbo].[User] WHERE LoginName=@pLoginName AND PasswordHash=HASHBYTES('SHA2_512', @pPassword+CAST(Salt AS NVARCHAR(36))))
IF(@userID IS NULL)
SET @responseMessage='Incorrect password'
ELSE
SET @responseMessage='User successfully logged in'
END
ELSE
SET @responseMessage='Invalid login'
END
我可以看到用户存在于我的数据库中，任何关于如何修复的建议
谢谢M
我使用以下代码重新创建了登录表单
Imports System.Net.Sockets
Imports System
Imports System.IO
Imports System.Security.Cryptography
Imports System.Text.RegularExpressions
Imports System.Data.SqlClient
Public Class Login
Private FirstNameValid As Boolean 'Is Name Valid?
Private LastNameValid As Boolean 'Is Surname Valid?
Private XUserNameValid As Boolean
Private UserPassValid As Boolean
Private EmailValid As Boolean 'Is Email Valid?
Private Sub FirstName_Leave(sender As Object, e As System.EventArgs) Handles FirstName.Leave
If FirstName.Text = "" Then ErrorLabel.Text = "Please Enter Your First Name!"
'If Not A Matching Format Entered
If Not Regex.Match(FirstName.Text, "^[a-z]*$", RegexOptions.IgnoreCase).Success Then 'Only Letters
ErrorLabel.Text = "Please Enter Alphabetic Characters Only!" 'Inform User
FirstName.Focus() 'Return Focus
FirstName.Clear() 'Clear TextBox
FirstNameValid = False 'Boolean = False
Else
FirstNameValid = True 'Everything Fine
ErrorLabel.Text = ""
End If
End Sub
Private Sub LastName_Leave(sender As Object, e As System.EventArgs) Handles LastName.Leave
'Create A Pattern For Surname
Dim strSurname As String = "^[a-zA-Zs]+$"
Dim reSurname As New Regex(strSurname) 'Attach Pattern To Surname Textbox
'Not A Match
If Not reSurname.IsMatch(LastName.Text) Then
ErrorLabel.Text = "Please Enter Alphabetic Characters Only!"
LastName.Focus()
LastName.Clear()
LastNameValid = False
Else
LastNameValid = True
ErrorLabel.Text = ""
End If
End Sub
Private Sub UserPass_Leave(sender As Object, e As System.EventArgs) Handles UserPass.Leave
If UserPass.Text = "" Then ErrorLabel.Text = "Please Enter Your Password!"
If UserPass.Text.Length < "8" Then
ErrorLabel.Text = "Password must be 8 Charecters!"
UserPass.Focus()
UserPass.Clear()
UserPassValid = False
Else
UserPassValid = True
ErrorLabel.Text = ""
End If
End Sub
Private Sub ValidateEmail()
'Set Up Reg Exp Pattern To Allow Most Characters, And No Special Characters
Dim reEmail As Regex = New Regex("([a-zA-Z0-9_-.]+)@(([[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}." +
")|(([a-zA-Z0-9-]+.)+))([a-zA-Z]{2,4}|[0-9]{1,3})",
RegexOptions.IgnoreCase _
Or RegexOptions.CultureInvariant _
Or RegexOptions.IgnorePatternWhitespace _
Or RegexOptions.Compiled
)
Dim blnPossibleMatch As Boolean = reEmail.IsMatch(UserEmail.Text)
If blnPossibleMatch Then
'Check If Entered Email Is In Correct Format
If Not UserEmail.Text.Equals(reEmail.Match(UserEmail.Text).ToString) Then
ErrorLabel.Text = "Invalid Email Address!"
Else
EmailValid = True 'Email is Perfect
End If
Else 'Not A Match To Pattern
EmailValid = False 'Set Boolean Variable To False
ErrorLabel.Text = "Invalid Email Address!" 'Inform User
UserEmail.Clear() 'Clear Textbox
UserEmail.Focus() 'Set Focus To TextBox
End If
End Sub
Private Sub txtEmail_LostFocus(sender As Object, e As System.EventArgs) Handles UserEmail.LostFocus
If UserEmail.Text = "" Then ErrorLabel.Text = "Invalid Email Address!"
ValidateEmail() 'Check Email Validity
End Sub
'The maximum number of times the user can try to login.
Private Const MAX_ATTEMPT_COUNT As Integer = 3
'The number of times the user has tried to login.
Private attemptCount As Integer = 0
Private Sub Login_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
RadPanel1.Show()
RadPanel2.Hide()
RadLabel7.Text = "Register"
TextBoxUserName.Focus()
Dim splash As SplashScreen1 = CType(My.Application.SplashScreen, SplashScreen1)
Dim MadeUpSteps() As String = {"Initializing...", "Loading ports...", "Checking Connection...", "Please wait...", "Connecting..."}
For i As Integer = 0 To MadeUpSteps.Length - 1
splash.UpdateProgress(MadeUpSteps(i), CInt((i + 1) / MadeUpSteps.Length * 100))
System.Threading.Thread.Sleep(1500)
Next
Dim tcpc As New TcpClient()
Try
tcpc.Connect("cloud01.smarthosting.co.uk", 80)
Return
Catch
If MessageBox.Show("Sorry No connection is available try again later.",
"Connection Error",
MessageBoxButtons.OK,
MessageBoxIcon.Error) = Windows.Forms.DialogResult.OK Then
Me.DialogResult = Windows.Forms.DialogResult.Cancel
Me.Close()
End If
End Try
End Sub
Dim adminLogin As Boolean
Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click
Dim x As New System.Security.Cryptography.SHA512CryptoServiceProvider()
Dim bs As Byte() = System.Text.Encoding.UTF8.GetBytes(TextBoxPassword.Text)
bs = x.ComputeHash(bs)
Dim s As New System.Text.StringBuilder()
For Each b As Byte In bs
s.Append(b.ToString("x2").ToLower())
Next
TextBoxPassword.Text = s.ToString()
Dim pass = TextBoxPassword.Text
If Me.ValidateCredentials Then
Dim Obj As New Main
adminLogin = False
My.Forms.Main.RadTextBox1.Text = adminLogin
Me.DialogResult = Windows.Forms.DialogResult.OK
ElseIf Me.ValidateCredentials1 Then
Dim Obj As New Main
adminLogin = True
My.Forms.Main.RadTextBox1.Text = adminLogin
Me.DialogResult = Windows.Forms.DialogResult.OK
Else
Me.attemptCount += 1
Dim message As String
If Me.attemptCount = MAX_ATTEMPT_COUNT Then
message = "The maximum number of failed logins has been reached." &
Environment.NewLine &
"The application will now exit."
Me.DialogResult = Windows.Forms.DialogResult.Abort
Else
message = "The provided credentials are incorrect." &
Environment.NewLine &
"Please try again."
Me.TextBoxPassword.Clear()
Me.TextBoxUserName.SelectAll()
Me.TextBoxUserName.Select()
End If
MessageBox.Show(message,
"Login Failed",
MessageBoxButtons.OK,
MessageBoxIcon.Error)
End If
End Sub
Private Sub Cancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Cancel.Click
If MessageBox.Show("Are you sure you want to exit without logging in?",
"Confirm Exit",
MessageBoxButtons.YesNo,
MessageBoxIcon.None) = Windows.Forms.DialogResult.Yes Then
Me.DialogResult = Windows.Forms.DialogResult.Cancel
End If
End Sub
Private Function ValidateCredentials() As Boolean
Dim result As Boolean
result = Me.ValidateCredentialsByTableAdapter()
Return result
End Function
Private Function ValidateCredentialsByTableAdapter() As Boolean
Using adapter As New System3DataSetTableAdapters.UserTableAdapter
Return adapter.ScalarQuery(Me.TextBoxUserName.Text.Trim(),
Me.TextBoxPassword.Text).Value > 0
End Using
End Function
Private Function ValidateCredentials1() As Boolean
Dim result As Boolean
result = Me.ValidateCredentialsByTableAdapter1()
Return result
End Function
Private Function ValidateCredentialsByTableAdapter1() As Boolean
Using adapter As New System3DataSetTableAdapters.Admin8TableAdapter
Return adapter.ScalarQuery(Me.TextBoxUserName.Text.Trim(),
Me.TextBoxPassword.Text).Value > 0
End Using
End Function
Private Sub RadLabel7_Click(sender As Object, e As EventArgs) Handles RadLabel7.Click
If RadPanel1.Visible = True Then
RadLabel7.Text = "Login"
RadPanel2.Show()
RadPanel1.Hide()
ElseIf RadPanel2.Visible = True Then
RadLabel7.Text = "Register"
RadPanel1.Show()
RadPanel2.Hide()
End If
End Sub
Private Sub RadButton1_Click(sender As Object, e As EventArgs) Handles RadButton1.Click
Dim x As New System.Security.Cryptography.SHA512CryptoServiceProvider()
Dim bs As Byte() = System.Text.Encoding.UTF8.GetBytes(UserPass.Text)
bs = x.ComputeHash(bs)
Dim s As New System.Text.StringBuilder()
For Each b As Byte In bs
s.Append(b.ToString("x2").ToLower())
Next
UserPass.Text = s.ToString()
Dim pass = UserPass.Text
Dim myConnection As New System.Data.ConnectionState
Dim mySqlDataAdapter As New SqlDataAdapter("Select * from Users", myConnection)
Dim myDataSet As New DataSet()
Dim myDataRow As DataRow
' Create command builder. This line automatically generates the update commands for you, so you don't
' have to provide or create your own.
Dim mySqlCommandBuilder As New SqlCommandBuilder(mySqlDataAdapter)
' Set the MissingSchemaAction property to AddWithKey because Fill will not cause primary
' key & unique key information to be retrieved unless AddWithKey is specified.
mySqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
mySqlDataAdapter.Fill(myDataSet, "Users")
myDataRow = myDataSet.Tables("Users").NewRow()
myDataRow("ID") = "NewID"
myDataRow("xFirstName") = FirstName.Text
myDataRow("xLastName") = LastName.Text
myDataRow("xEmail") = UserEmail.Text
myDataRow("UserPass") = pass
myDataSet.Tables("Users").Rows.Add(myDataRow)
End Sub
End Class
Public Class PasswordHash
Public Const SALT_BYTE_SIZE As Integer = 24
Public Const HASH_BYTE_SIZE As Integer = 24
Public Const PBKDF2_ITERATIONS As Integer = 1000
Public Const ITERATION_INDEX As Integer = 0
Public Const SALT_INDEX As Integer = 1
Public Const PBKDF2_INDEX As Integer = 2
Public Shared Function CreateHash(password As String) As String
' Generate a random salt
Dim csprng As New RNGCryptoServiceProvider()
Dim salt As Byte() = New Byte(SALT_BYTE_SIZE - 1) {}
csprng.GetBytes(salt)
' Hash the password and encode the parameters
Dim hash As Byte() = PBKDF2(password, salt, PBKDF2_ITERATIONS, HASH_BYTE_SIZE)
Return PBKDF2_ITERATIONS + ":" + Convert.ToBase64String(salt) + ":" + Convert.ToBase64String(hash)
End Function
''' <summary>
''' Validates a password given a hash of the correct one.
''' </summary>
''' <param name="password">The password to check.</param>
''' <param name="correctHash">A hash of the correct password.</param>
''' <returns>True if the password is correct. False otherwise.</returns>
Public Shared Function ValidatePassword(password As String, correctHash As String) As Boolean
' Extract the parameters from the hash
Dim delimiter As Char() = {":"c}
Dim split As String() = correctHash.Split(delimiter)
Dim iterations As Integer = Int32.Parse(split(ITERATION_INDEX))
Dim salt As Byte() = Convert.FromBase64String(split(SALT_INDEX))
Dim hash As Byte() = Convert.FromBase64String(split(PBKDF2_INDEX))
Dim testHash As Byte() = PBKDF2(password, salt, iterations, hash.Length)
Return SlowEquals(hash, testHash)
End Function
''' <summary>
''' Compares two byte arrays in length-constant time. This comparison
''' method is used so that password hashes cannot be extracted from
''' on-line systems using a timing attack and then attacked off-line.
''' </summary>
''' <param name="a">The first byte array.</param>
''' <param name="b">The second byte array.</param>
''' <returns>True if both byte arrays are equal. False otherwise.</returns>
Private Shared Function SlowEquals(a As Byte(), b As Byte()) As Boolean
Dim diff As UInteger = CUInt(a.Length) Xor CUInt(b.Length)
Dim i As Integer = 0
While i < a.Length AndAlso i < b.Length
diff = diff Or CUInt(a(i) Xor b(i))
i += 1
End While
Return diff = 0
End Function
''' <summary>
''' Computes the PBKDF2-SHA1 hash of a password.
''' </summary>
''' <param name="password">The password to hash.</param>
''' <param name="salt">The salt.</param>
''' <param name="iterations">The PBKDF2 iteration count.</param>
''' <param name="outputBytes">The length of the hash to generate, in bytes.</param>
''' <returns>A hash of the password.</returns>
Private Shared Function PBKDF2(password As String, salt As Byte(), iterations As Integer, outputBytes As Integer) As Byte()
Dim pbkdf2__1 As New Rfc2898DeriveBytes(password, salt)
pbkdf2__1.IterationCount = iterations
Return pbkdf2__1.GetBytes(outputBytes)
End Function
End Class
uspLogin存储过程将其结果作为@responseMessage输出参数返回，而不是作为结果集返回。所以你的。NET登录代码不应使用DataTable来获取结果；相反，它应该定义一个@responseMessage输出参数。
使用这样的代码：
Dim resultParam = cmd.Parameters.Add("@responseMessage", SqlDbType.NVarChar, 250)
resultParam.Direction = ParameterDirection.Output
cmd.ExecuteNonQuery()
Dim res = resultParam.Value.ToString()