Getting syntax error in update statement. Vb.net

Question

1.00/5 (2 votes)

See more:

When i click on Update Button i am getting the following error syntax error in update statement. vb.net

What I have tried:

VB

If con.State = ConnectionState.Open Then
            con.Close()
        End If
        If txtID.Text = "" Then
            MsgBox("Please Select Element To Update....", MsgBoxStyle.Information)
            Exit Sub
        End If
        ''UPDATE Login SET User = 'Nirmal', Pass= 'n@12345', Rights= 'Admin' WHERE id = 2

        Try
            Dim cmditem1 As New OleDbCommand("UPDATE LOGIN set USER = ('" & txtUser.Text & "'), PASS = ('" & txtPass.Text & "'), RIGHTS = ('" & cmbRights.Text & "') where ID = " & txtID.Text, con)
            con.Open()
            cmditem1.ExecuteNonQuery()
            MsgBox("Updated Successfully", MsgBoxStyle.Information)
            con.Close()

            clean()
            setg1()
        Catch ex As Exception
            MsgBox(ex.Message)

        End Try

XML

<connectionstrings>
    <add name="cn" connectionstring="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\CONFIDENTIAL\YandexDisk\KAPYHA90\PAL\PAL_BillingDB.mdb;Jet OLEDB:Database Password=Pal@30384731;Jet OLEDB:Engine Type=5" providername="System.Data.OleDb">

Posted 18-May-19 16:07pm

NIRMAL90

Updated 24-May-19 8:59am

phil.o

v2

Add a Solution

3 solutions

Solution 1

Because you build the query by concatenation, we have no way to know what is the real query because it depend on input fields.

Quote:

VB

Dim cmditem1 As New OleDbCommand("UPDATE LOGIN set USER = ('" & txtUser.Text & "'), PASS = ('" & txtPass.Text & "'), RIGHTS = ('" & cmbRights.Text & "') where ID = " & txtID.Text, con)

Not necessary a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]

Posted 18-May-19 16:35pm

Patrice T

Comments

NIRMAL90 19-May-19 0:53am

please help me with the update query

[no name] 24-May-19 15:27pm

This time a 5 for not only mention the sql injection stuff :)

Patrice T 24-May-19 15:30pm

Thank you.

Solution 2

VB

"UPDATE LOGIN set USER = ('" & txtUser.Text & "'), PASS = ('" & txtPass.Text & "'), RIGHTS = ('" & cmbRights.Text & "') where ID = " & txtID.Text

Why are you enclosing provided values between brackets ( and )? The SQL syntax is wrong.
The real syntax is

VB

"UPDATE LOGIN set USER = 'username', PASS = 'pass', RIGHTS = 'rights' where ID = id

BUT:
As told you in solution 1, never build SQL strings by concatenating values obtained from user input. This is the issue that you should fix first.
Plus, you should not store passwords in clear text, either.

Posted 19-May-19 0:14am

phil.o

Updated 19-May-19 0:16am

v2

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 · 2019-05-21T04:48:00

Fixing the SQL Injection vulnerability is the easy part:

VB.NET

Dim cmditem1 As New OleDbCommand("UPDATE LOGIN set USER = ?, PASS = ?, RIGHTS = ? WHERE ID = ?", con)

' For OLEDB, parameter names don't matter; 
' they just need to be added in the same order as they appear in the query.

cmditem1.Parameters.AddWithValue("user", txtUser.Text)
cmditem1.Parameters.AddWithValue("pass", txtPass.Text)
cmditem1.Parameters.AddWithValue("rights", cmbRights.Text)
cmditem1.Parameters.AddWithValue("ID", txtID.Text)

con.Open()
cmditem1.ExecuteNonQuery()

Now you'll need to go through the rest of your code and fix any other queries which aren't using parameters correctly.

However, you also need to fix your password storage. You're currently storing passwords in plain text, which is an extremely bad idea, and could leave you open to massive fines. Instead, you need to store a salted hash of the password, using a unique salt for each record, and using multiple rounds of a password derivation function like PBKDF2[^].

Secure Password Authentication Explained Simply[^]
Salted Password Hashing - Doing it Right[^]