Update a password if user name and old password match

Question

This is the first time I run any type of queries and/or connect to a database through vb. I have looked up my problem on line but have not found exactly what I am looking for.

I have a simple login page on my windows application that runs out of a compact .sdf database. I need to add a procedure that allows the user to change the password.

If the user name in textbox1 and the password in textbox2 match what I have stored in my database, then replace the password with the values of textbox3.

So far I have been able to figure out how to create a new account and verify the log in. I log in using the following:

SELECT        username, userpassword
FROM            UserInfo
WHERE        (username LIKE @username) AND (userpassword LIKE @userpassword)

Then the procedure on my button:

' Check if username or password is empty
If txtPassword.Text = "" Or txtUserName.Text = "" Then
    MessageBox.Show("Please complete the required fields..", "Authentication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

    'Clear all fields
    txtPassword.Text = ""
    txtUserName.Text = ""

    'Focus on Username field
    txtUserName.Focus()

Else

    'If the password and username match, open the main form.
    If Not UserInfoTableAdapter1.Login(txtUserName.Text, txtPassword.Text) = Nothing Then

        Dim frmWelcome As New frmWelcomePage

        frmWelcome.Show()
        Me.Hide()

    Else


        MessageBox.Show("You have entered an invalid user name or password", "Invalid Login", MessageBoxButtons.OK, MessageBoxIcon.Error)

        'Clear all fields
        txtPassword.Text = ""
        txtUserName.Text = ""

        'Focus on Username field
        txtUserName.Focus()

    End If

End If

How can I use something similar to change the password?

You do not want to use `LIKE` with a password. Use an exact match (`=`) instead. — pickypg, Oct 11 '13 at 01:29

score 3 · Accepted Answer · edited May 23 '17 at 11:50

As @pickypg said you should definitely look for an exact match with passwords and usernames. Also you should consider a one way hash for user passwords. This answer does a good job of describing the potential danger of storing plain text passwords. This article has related information and is also amusing.

That aside the sql you're looking for might be something like this:

create procedure updateUserPassword
 @userName varchar(max)
,@oldHashedPassword nvarchar(128)
,@newHashedPassword nvarchar(128)
as
begin
set nocount on;
  if exists ( select 1 from UserInfo where username = @userName and userpassword = @oldHashedPassword )
  begin
    update UserInfo set userPassword = @newHashedPassword where username = @userName;
  end
  else
  begin
   raiserror('No record found for user', 16, 1);
  end
end

Update a password if user name and old password match

1 Answers1

Linked