c# insert into mysql multiple variables

Question

hello all this seems to be my problem I have a table in mysql and am making a c# app when trying to insert i get a You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OIL COMPANY)' at line 1 which is my companyVal. it will insert with one value and a default for all others but i add the second value it errors out

        string ConnectionString2 = ConfigurationSettings.AppSettings["ConnectionString2"];
        MySqlConnection connection2;
        connection2 = new MySqlConnection(ConnectionString2);
        connection2.Open();
        MySqlCommand command = new MySqlCommand("INSERT INTO spt_proposal     (lab_Prop_Id,customer_Name) VALUE (" + a + "," + companyVal + ")", connection2);
        command.ExecuteNonQuery();
        connection2.Close();

Brent

Jon Skeet · Accepted Answer · 2012-03-22 20:55:35Z

5

Don't do this to start with:

// I split the line
MySqlCommand command =
    new MySqlCommand("INSERT INTO spt_proposal lab_Prop_Id,customer_Name) "
        "VALUE (" + a + "," + companyVal + ")", connection2);

That opens you up to SQL injection attacks, as well as making your code harder to understand by mixing code and data, and causing potential problems for conversions (particularly for dates and times).

Instead, use a parameterized SQL statement to give the values to the database.

While you could just add quotes, you absolutely should not do so. Bad data (whether deliberately malicious or not in variables a and b could be a serious security risk. Just don't do it.

You also need to change VALUE to VALUES, but I'd only do that after moving to parameterized SQL. Oh, and use a using statement to automatically dispose of the command and the connection, so you don't leak resources if an exception is thrown.

answered Mar 22, 2012 at 20:55

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bmorrison1982 Over a year ago

Thank you I will look at parameterized queries

Jon Skeet Over a year ago

@bmorrison1982: You should make that a top priority. I would seriously recommend that you don't work on any other code while you've still get queries like this.

Mark Byers · Accepted Answer · 2012-03-22 20:55:16Z

1

You are missing quotes round your strings and VALUE should be VALUES:

string sql = "INSERT ... VALUES ('" + a + "','" + companyVal + "')";

Also I'd advise you to use parameterized queries.

answered Mar 22, 2012 at 20:55

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

1 Comment

bmorrison1982 Over a year ago

Thank you I will look at parameterized queries

Matthew Bonig · Accepted Answer · 2012-03-22 20:58:21Z

0

Guessing here, is the column customer_Name a string/text column? I don't have much experience with mySQL but with SQL Server strings always have to be surrounded by ticks ('). So something like this might work:

INSERT INTO spt_proposal     (lab_Prop_Id,customer_Name) VALUE (" + a + ",'" + companyVal + "')", connection2);

However, this is generally considered "bad form" and a slightly better alternative is using String.Format or even (much much) better is parameterized SQL.

answered Mar 22, 2012 at 20:58

Matthew Bonig

2,1462 gold badges20 silver badges40 bronze badges

1 Comment

bmorrison1982 Over a year ago

Thank you I will look at parameterized queries

code4life · Accepted Answer · 2012-03-22 21:16:38Z

0

Change your command text to be parameterized:

var commandText = "INSERT INTO spt_proposal(lab_Prop_Id,customer_Name)" +
  " VALUES(?lab, ?cust)";

var command = new MySqlCommand(commandText, connection2);
command.Parameters.Add("?lab", MySqlDbType.VarChar, 80).Value = a;
command.Parameters.Add("?cust", MySqlDbType.VarChar, 80).Value = companyVal;

command.ExecuteNonQuery();

I'm making the wild assumption that the field types are varchar(80), lol...

answered Mar 22, 2012 at 21:16

code4life

15.8k8 gold badges56 silver badges82 bronze badges

Collectives™ on Stack Overflow

c# insert into mysql multiple variables

4 Answers 4

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related