Syntax error in UPDATE for C# using Oledb

Question

I am creating a simple app where users create accounts. I want for the user to be able to change their password after making the account.

I am making this in C# using Oledb.

 string test = "testing";
 con.Open();
 OleDbCommand command = new OleDbCommand();
 command.Connection = con;
 string query = "UPDATE tbl_users SET password = '" + test + "' WHERE username = '" + txtLoginUsername.Text + "'";
 MessageBox.Show(query);
 command.CommandText = query;

 command.ExecuteNonQuery();
 con.Close();

I keep getting the error: " System.Data.OleDbException: 'Syntax error in UPDATE'"

This error is occuring in the line:

command.ExecuteNonQuery();

Even the OleDbCommand understands parameters. Please use them — Hans Keﬆing, Aug 12 '21 at 08:48
@HansKeﬆing Can you please clarify what you mean, I have understand of C# well but I am still learning to use Oledb. — CoinBank, Aug 12 '21 at 08:56
https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection https://stackoverflow.com/questions/1054022/best-way-to-store-password-in-database — mjwills, Aug 12 '21 at 09:24

score 1 · Answer 1 · answered Aug 12 '21 at 11:33

To clarify what Hossein has answered, when you are building your query command by adding strings together, you are wide-open to SQL-injection. Please read up on it some to protect your future development.

With reference to using "parameters". This is basically referring to a place-holder value for the query, like an "insert here" instead of you hard adding parts to the query like you were wrapping single quotes before and after the values for the password and user name.

Looking at your original query

"UPDATE tbl_users SET password = '" + test + "' WHERE username = '" + txtLoginUsername.Text + "'";

What if someone put in values of

Password:  What's Up
Username: O'Conner

Your final query command when concatenated with your approach would create a value

UPDATE tbl_users SET password = 'What's Up' WHERE username = 'O'Conner'

See how the literal values have now screwed-up the string from its intent.

By using the "@" values, this is telling the SQL that hey... there will be another value coming along by the name provided, please use THAT value here. In some databases, they dont use named parameters, but "?" single character instead as a place-holder and you have to add the parameters in the exact order as they appear in the statement you are trying to prepare.

One other thing of personal preference. If your column name is UserName in I would use a parameter name like "@parmUserName" so you know it is EXPLICITLY the parameter and not accidentally just doing UserName = UserName and not get the results. A few more characters, but obvious what its purpose and where coming from works.

Now, how is that applied? Take a look again at Hossein's answer. Notice the two

command.Parameters.AddWithValue("@password", "test");
command.Parameters.AddWithValue("@username", txtLoginUsername.Text);

This is where the parameters are added to the command object. Stating, where you see the @password, use the following value I am putting here, same with @username.

Good luck going forward.

score 0 · Accepted Answer · answered Aug 12 '21 at 10:57

Use this syntax

Use bracket for password in query because password is reserved word

link List of reserved world

        using (var connection = new OleDbConnection("Your Connection String"))
        {
            var query = "UPDATE tbl_users SET [password] = @password WHERE username = @username";

            using (var command = new OleDbCommand(query, connection))
            {
                
                    connection.Open();
                 
                    command.Parameters.AddWithValue("@password", "test");
                    command.Parameters.AddWithValue("@username", txtLoginUsername.Text);

                    command.ExecuteNonQuery();
               
            }
        }

Syntax error in UPDATE for C# using Oledb

2 Answers2