need help with my update statement

Question

1.00/5 (1 vote)

See more:

when i run and try to change the password it show error on "da.updatecommand.executenonquery()"
it said "syntax error in update statement"
error code : -2147217900
item:In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user.
message: Syntax error in UPDATE statement.
source: Microsoft Access Database Engine
do i need parameter? and how i add parameter if i need to?

VB

Sub gantiid()
        sql = &quot;update USERLIST set USERNAME = '&quot; &amp; LOGIN.USERNAME.Text &amp; &quot;', PASSWORD= '&quot; &amp; renew.Text &amp; &quot;' where USERNAME ='&quot; &amp; LOGIN.USERNAME.Text &amp; &quot;'&quot;
        kns.Open()
        da.UpdateCommand = New OleDbCommand(sql, kns)
        da.UpdateCommand.ExecuteNonQuery()
        kns.Close()
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        cariid()

        If ds.Tables(&quot;USERLIST&quot;).Rows.Count = 0 Then
            MessageBox.Show(&quot;You Have Enter Wrong Password&quot;, &quot;Warning!&quot;)
            oldpass.Clear()
            newpass.Clear()
            renew.Clear()
            oldpass.Focus()
        ElseIf ds.Tables(&quot;USERLIST&quot;).Rows.Count = 1 And newpass.Text &lt;&gt; renew.Text Then
            MessageBox.Show(&quot;New Password And Confirmation Must Be Same!&quot;, &quot;Warning!&quot;)
            newpass.Clear()
            renew.Clear()
            newpass.Focus()
        ElseIf ds.Tables(&quot;USERLIST&quot;).Rows.Count = 1 And newpass.Text = renew.Text Then
            gantiid()
            MessageBox.Show(&quot;Password Change Is Successful!&quot;, &quot;Notice&quot;)
            Me.Close()
        End If
    End Sub

Posted 12-Jan-15 17:19pm

vendir

Updated 13-Jan-15 3:22am

v5

Add a Solution

Comments

Thanks7872 12-Jan-15 23:25pm

Remove unnecessary code and keep only the part you have problem with. Use Improve question link to do the same.

vendir 13-Jan-15 9:19am

i have improved it

Richard Deeming 13-Jan-15 9:25am

Your code is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

2 solutions

Solution 1

You may have problems with updating the field USERNAME. By your code it looks like the USERNAME field is indexed and unique. You might try:

sql = "update USERLIST set PASSWORD= '" & renew.Text & "' where USERNAME ='" & LOGIN.USERNAME.Text & "'"

Posted 12-Jan-15 18:07pm

bojammis

Comments

vendir 13-Jan-15 9:18am

i have tried it but it have same result and error

Richard Deeming 13-Jan-15 9:26am

You have copied the SQL Injection[^] vulnerability from the question.

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

bojammis 23-Jan-15 15:21pm

Thanks for pointing that out. Taking your advise, I am changing my sqlCommands to use parameters. Thanks

bojammis 13-Jan-15 12:10pm

Good point but I would still consider and use the following: Some cases using string concatenation is acceptable. It is taking the steps to reject any string parts that violate your rules for SQL injection. My apologies - I didn't see that you are working on an Access DB my comments maybe null and void.

vendir 14-Jan-15 9:32am

thank you too

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2015-01-13T03:32:00

Solution 2

Start by fixing the SQL Injection[^] vulnerability in your code.

Also, there's no point updating the USERNAME column to the same value, so you can remove that part of the query.

You'll also need to wrap the PASSWORD column in square brackets (ie: [PASSWORD]), as it's a reserved word in Access.

VB

Sub gantiid()
    ' The OleDb provider doesn't use named parameters:
    sql = "update USERLIST set [PASSWORD] = ? where USERNAME = ?"

    kns.Open()
    da.UpdateCommand = New OleDbCommand(sql, kns)
    da.UpdateCommand.Parameters.AddWithValue("p0", renew.Text)
    da.UpdateCommand.Parameters.AddWithValue("p1", LOGIN.USERNAME.Text)
    da.UpdateCommand.ExecuteNonQuery()
    kns.Close()
End Sub

Once you've fixed that, you then need to review your password storage. You're currently storing passwords in plain-text, which is a really bad idea. You should be storing a salted hash of the password - see: Salted Password Hashing - Doing it Right[^]

Posted 13-Jan-15 3:32am

Richard Deeming

Updated 14-Jan-15 1:46am

v2

Comments

vendir 13-Jan-15 23:39pm

how can i fix the sql injection?
i have copied the fixed code but it still not working?
is salted password hashing can work with access db too?

Richard Deeming 14-Jan-15 7:47am

Using a parameterized query (as per my answer) will fix the SQL injection vulnerability.

You'll also need to wrap PASSWORD in square brackets, as it's a reserved word in Access.

vendir 14-Jan-15 9:32am

yes it is working..
thank you
is there any conditions that we need to use parameter other than this?

Richard Deeming 14-Jan-15 14:49pm

I'm not sure what you mean?

vendir 14-Jan-15 22:31pm

what parameter used for?
in what condition we need to use parameter? on this case we need it to build a sql query. sorry but i'm beginner on this thing

Richard Deeming 15-Jan-15 8:10am

Parameters are used to pass data to a query in such a way that no part of the data can be treated as executable code.

You use parameters whenever you need to pass data to a query.

vendir 15-Jan-15 9:42am

i see
thanks for your help

_Asif_ 14-Jan-15 8:09am

+5