Why I get syntax error in this update statement?

Question

I wanted to update a table in my m/s access database where my the user entered a new password in order to replace the old one but i have syntax error in the update statement. Please help!

public partial class resetPassword : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        }
        protected void SubmitButton_Click(object sender, EventArgs e)
        {
            string userName = (string) Session["username"];

        string str = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\inetpub\wwwroot\JetStar\database\JetstarDb.accdb";
        var con = new OleDbConnection(str);
        con.Open();

        string pwd = Request.Form["conPassword"];
        OleDbCommand cmd = new OleDbCommand("UPDATE [users] SET password = '" + pwd + "' WHERE username = '" + userName + "'", con);

        try
        {
            cmd.ExecuteNonQuery();
            MessageBox.Show("Your password has been changed successfully."); 
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
        finally
        {
            con.Close();
        }
    }
}

First thing to fix: use parameterized SQL instead of putting the values directly into the SQL. Second thing to fix: don't put plaintext passwords into your database. Neither of those will probably address your current issue, but given the lack of security here, it's better that the code doesn't work than that it enables a security hole. — Jon Skeet, Dec 11 '14 at 08:29
Also posting the syntax error message might be useful to find the error faster. As Jon Skeet already stated, parameterized SQL is less prone to failure so you might want to consider using that. — Paul Weiland, Dec 11 '14 at 08:31
might be problem in this statement "string str = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\inetpub\wwwroot\JetStar\database\JetstarDb.accdb";" Use double slash instead will fix the issue.. — Amol Bavannavar, Dec 11 '14 at 08:32
@AmolBavannavar - The string is marked as a string literal (the `@` in front of it). The backslashes don't need to be escaped in that situation. — Tim, Dec 11 '14 at 09:31

score 5 · Answer 1 · edited May 23 '17 at 12:12

Probably this happends because password is a reserved keyword on Microsoft Access. You should use it with square brackets as [password]

But more important

You should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.

Don't store your passwords as a plain text. Read: Best way to store password in database

Use using statement to dispose your OleDbConnection and OleDbCommand.

using(OleDbConnection con = new OleDbConnection(str))
using(OleDbCommand cmd = con.CreateCommand())
{
    cmd.CommandText = "UPDATE [users] SET [password] = ? WHERE username = ?";
    cmd.Parameters.Add("pass", OleDbType.VarChar).Value = pwd;
    cmd.Parameters.Add("user", OleDbType.VarChar).Value = userName;
    con.Open();
    try
    {
        cmd.ExecuteNonQuery();
        MessageBox.Show("Your password has been changed successfully."); 
    }
    catch (Exception ex)
    {
        Response.Write(ex.Message);
    }
}

@ArielSeah What is the error message exactly? I assumed your columns are `VarChar`, I added `OleDbType.VarChar` as a second parameter in `.Add()` method. — Soner Gönül, Dec 11 '14 at 09:25
Gonul the error message is: "int does not contain a definition for 'Value' and no extension method 'Value' accepting a first argument of type 'int' could be found." After adding in your modified codes above, the error became "Operation must use an updateable query." And my password field came from the form field as password while username is coming from session when the user log in and stored. — Ariel Seah, Dec 12 '14 at 03:26

score 2 · Answer 2 · answered Dec 11 '14 at 08:35

92.3% ^(a) of all DB problems become obvious if you just print the command before you use it, and read the error message.

So replace:

OleDbCommand cmd = new OleDbCommand("UPDATE [users] SET password = '" + pwd + "' WHERE username = '" + userName + "'", con);

with something like:

String s = "UPDATE [users] SET password = '" + pwd + "' WHERE username = '" + userName + "'";
Console.WriteLine(s);
OleDbCommand cmd = new OleDbCommand(s, con);

Then post the results of:

Response.Write(ex.Message);

for all to see, and examine what it tells you very carefully.

^(a) A statistic I just plucked out of nowhere - actual value may be wildly different.

Why I get syntax error in this update statement?

2 Answers2

But more important