Error with SQL command

Question

I'm trying to get the users details in the text boxes in my form to my database in access, which should save. However i keep getting an error message every time i click to register, the following code is how i am trying to write it out:

public void AddNewUser()
{
    string filePath;
    try
    {
        filePath = (Application.StartupPath + ("\\" + DBFile));
        connection = new System.Data.OleDb.OleDbConnection((ConnectionString + filePath));
        connection.Open();
        System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand();
        command.Connection = connection;
        // ---set the user's particulars in the table---
        string sql = ("UPDATE enroll SET SSN=\'"
                    + (txtSSN.Text + ("\', " + ("Name=\'"
                    + (txtName.Text + ("\', " + ("Company=\'"
                    + (txtCompany.Text +("\', "
                    + (" WHERE ID=" + _UserID))))))))));
        command.CommandText = sql;
        command.ExecuteNonQuery();
        MessageBox.Show("User added successfully!", "Error");

    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString(), "Error");
    }
    finally
    {
        connection.Close();
    }
}

However I think that the problem is actually coming from this section:

// ---set the user's particulars in the table---
string sql = ("UPDATE enroll SET SSN=\'"
            + (txtSSN.Text + ("\', " + ("Name=\'"
            + (txtName.Text + ("\', " + ("Company=\'"
            + (txtCompany.Text +("\', "
            + (" WHERE ID=" + _UserID))))))))));
command.CommandText = sql;
command.ExecuteNonQuery();
MessageBox.Show("User added successfully!", "Error");

Invalid SQL statement. Remove comma `+ (txtCompany.Text +("\' "` — jdl, Dec 29 '13 at 00:21
@Vland a message box pops up saying: "System.DataOleDbException: Syntax error in UPDATE Statement... etc etc" — Zack, Dec 29 '13 at 00:22
as J Lo says... remove the comma. Next time show the error also, not only your code — Vland, Dec 29 '13 at 00:22
See also http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php, and yes, I know this isn't PHP — John Saunders, Dec 29 '13 at 00:23
@JLo Oh wow that actually worked, when i first saw it i thought there was something wrong there but THANK YOU! so much! — Zack, Dec 29 '13 at 00:24
Also could I ask why my reputation has gone down significantly? especially with the post being marked down i mean i tried my best to be specific? — Zack, Dec 29 '13 at 00:40

score 0 · Answer 1 · edited May 23 '17 at 11:49

Really your query is unreadable. Any kind of error could hide in that jungle of string concatenation and single quotes sprawled everywhere. (like a not necessary comma escaped probably from a fixup of a copy/paste operation)

You should use parameterized query and all of this will disappear

command.Connection = connection;
string sql = "UPDATE enroll SET SSN=?, Name=?, Company=? WHERE ID=?";
command.CommandText = sql;
command.Parameters.AddWithValue("@p1", txtSSN.Text);
command.Parameters.AddWithValue("@p2", txtName.Text );
command.Parameters.AddWithValue("@p3", txtCompany.Text);
command.Parameters.AddWithValue("@p4", _UserID);
command.ExecuteNonQuery();

Now I think that this is really more readable, no quotes to add because the framework knows the datatype of every parameter and will use the appropriate quoting required. Last but not least, no problem with Sql Injection

Error with SQL command

1 Answers1