Syntax error in UPDATE statement while updating password in Access database

Question

I faced syntax error in UPDATE statement, while updating password in Access database in C#:

protected void Button1_Click(object sender, EventArgs e)
{
    OleDbConnection con = new OleDbConnection();
    con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
        Server.MapPath("~/Database/registration.accdb");

    con.Open();

    OleDbCommand cmd = new OleDbCommand();
    cmd.Connection = con;
    cmd.CommandType = CommandType.Text;
    cmd.CommandText =
        "UPDATE into userdata(password)values('"+TextBox1.Text+"') where id=@id";
    cmd.ExecuteNonQuery();
    con.Close();
    Response.Write("alert('Password Reset Successfully done');"); 
}

You need to learn about the syntax of UPDATE SQL statement... `UPDATE INTO` is not the correct syntax — Chetan, Jul 18 '21 at 13:12
Also, you are passing parameters as a mix of string concatenation for the text and command parameter (`@id`) for the id, but you are never setting this parameter. With OLEDB you must specify the parameters as `?`. Use command parameters for both of your inputs. See: [OleDbCommand.Parameters Property](https://learn.microsoft.com/en-us/dotnet/api/system.data.oledb.oledbcommand.parameters?view=dotnet-plat-ext-5.0) for an example. And use either an [INSERT](https://stackoverflow.com/q/10941284/880990) or an [UPDATE](https://stackoverflow.com/q/29534671/880990) command, not a mix of both. — Olivier Jacot-Descombes, Jul 18 '21 at 13:16
And finally _password_ is a reserved word for an Access Sql Engine. You need to put it between square brackets. — Steve, Jul 18 '21 at 13:35
And, storing a password in a database is a complete no-no. NEVER NEVER NEVER store an unhashed password (not even encrypted). — Neil, Jul 18 '21 at 13:39
please use ***parameterised queries*** - building SQL queries by concatenation etc. is a recipe for disaster. not only is it a source for many hard to debug syntax errors, it's also a wide, open gate for ***[SQL Injection attacks](https://bobby-tables.com/)***. — Franz Gleichmann, Jul 18 '21 at 14:04
When using parameterized queries in OLEDB, parameter names aren't honored in the SQL text. Therefore, the order that the parameters are added is important. When using parameters, if a column name occurs more than once in the SQL, then it must be added more than once as a parameter (and in the order it occurs in the SQL). — Tu deschizi eu inchid, Jul 18 '21 at 14:40
You should also have your connection and command objects in `using` blocks — Charlieface, Jul 18 '21 at 15:10
I casted a reopen vote because the marked as duplicates ([1](https://stackoverflow.com/questions/1054022/best-way-to-store-password-in-database), [2](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection)) are about storing passwords in databases, while this question's relevance with passwords is only accidental. The OP could attempt to update any kind of tables/columns, and they would have the same problem. — Theodor Zoulias, Jul 19 '21 at 07:06

Andrei Solero · Answer 1 · 2021-07-18T14:14:17.753

It's because your SQL Query is not correct, that's not how you update data in your database. It should be like this:

query = "Update [tableName] SET [ColumnName] = 'Values', [ColumnName1] = 'Values2',...";

You should learn at least the basics of SQL syntax, more information here

And also, you shouldn't concatenate your query since it will become vulnerable for SQL Injection attack, you should at least use Parameterized Query

OleDbConnection con = new OleDbConnection();
con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
    Server.MapPath("~/Database/registration.accdb");

con.Open();

OleDbCommand cmd = new OleDbCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText =
    "UPDATE yourTableName SET [yourColumnName] = @YourFirstValue, [secondColumnName] = @YourSecondValue WHERE [columnKey] = @ID"

cmd.Parameters.AddWithValue("@YourFirstValues", textbox1.Text);
cmd.Parameters.AddWithValue("@YourSecondValue ", textbox2.Text);
cmd.Parameters.AddWithValue("@ID", textbox3.Text);

cmd.ExecuteNonQuery();
con.Close();
Response.Write("alert('Password Reset Successfully done');");

@user9938 A textbox can't have the value of `null`. And the value of empty will work fine, despite your assertion. — mjwills, Jul 18 '21 at 23:23

score 1 · Answer 2 · answered Jul 18 '21 at 13:39

1

you have to fix your query

   "UPDATE  userdata SET password=@password where id=@id";

and add new lines before cmd.ExecuteNonQuery()

cmd.Parameters.AddWithValue("@id", id);
 cmd.Parameters.AddWithValue("@password", TextBox1.Text);

answered Jul 18 '21 at 13:39

Serge

40,935
4
18
45

Will probaby work, but: **NEVER** store passwords in the database. Use salted hashes instead. – Klaus Gütter Jul 18 '21 at 14:18

score 0 · Answer 3 · answered Jun 18 '22 at 00:28

I had a similar problem, it gave me a syntax error that I solved by checking the whole string and actually there was a comma too many, I don't know if this answer of mine can be useful, among other things after some time.

Syntax error in UPDATE statement while updating password in Access database

3 Answers3