SQL Syntax Error

Question

when I try to insert data using form it throws the exception(e1) "Error Occured Please Try Again" 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 " at line1
Please help to correct this error

StringBuilder query = new StringBuilder();
query.Append("Insert Into my_project_data.vehicle(ChassyNumber ,ManufacturedYear, EngineCapacity,Price,Features ,VehicleBrand , VehicleType) Values('" + chassy_txt.Text + "','"+manufac_year_txt.Text+"','"+Engine_Capasity_txt.Text+"','"+Price_txt.Text+"','"+Features_rich_txt.Text+"',");

Classes.DB_Connectivity db = new Classes.DB_Connectivity();

try
{
    db.openConnection();

        if ((radioButton1.Checked || radioButton2.Checked) && (radioButton7.Checked || radioButton11.Checked) && ( manufac_year_txt.Text != "" && Engine_Capasity_txt.Text != "" && Price_txt.Text != "" && Features_rich_txt.Text != ""))
        {


            if (radioButton1.Checked)
            {
                query.Append("BMW ,");

            }
            if (radioButton2.Checked) 
            {
                query.Append("Benz , ");
            }
            if (radioButton7.Checked)
            {
                query.Append("Car ,");
            }
            if (radioButton11.Checked)
            {
                query.Append("SUV ,");
            }

           if ( manufac_year_txt.Text != "" && Engine_Capasity_txt.Text != "" && Price_txt.Text != "" && Features_rich_txt.Text != "")
           { 
               query.Append(" '"+manufac_year_txt.Text+"', '"+Engine_Capasity_txt.Text+"','"+Price_txt.Text+"','"+Features_rich_txt.Text+"'");
            }

            MySqlCommand cmd = new MySqlCommand(query.ToString(), db.conn);
            cmd.ExecuteNonQuery();


            MessageBox.Show(" Vehicle Registration Successfull ", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);


        }
        else
        {
            MessageBox.Show("Fill All Required Information ", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }

}
catch (Exception e1)
{
    MessageBox.Show("Error Occured Please Try Again "  +e1.Message,"Error",MessageBoxButtons.OK,MessageBoxIcon.Error);
}

Do not use string concatenation to build queries. They are prone to SQL Injection. For debug purpose, copy the generated query string from your C# code and try to execute it on mysql directly , see what is the error you get — Habib, Sep 23 '14 at 15:52
Print the SQL command you're about to execute in order to debug. — Jonathan M, Sep 23 '14 at 15:54
I simply do not understand how *anyone* writing SQL in 2014 thinks that concatenating input is *ever* sensible, reasonable, or acceptable... — Marc Gravell, Sep 23 '14 at 16:02
This looks **terrifyingly** insecure. Are you sure you're [properly escaping that data](http://bobby-tables.com/csharp.html)? Using placeholders avoids having to be concerned about this. — tadman, Sep 23 '14 at 16:11
when i execute this in mysql directly it works fine so what is the secured way and how to use it? — Hasiya55, Sep 23 '14 at 16:19
[A classic on Sql Injection](http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work) and an example of [Parameterized queries](http://stackoverflow.com/questions/652978/parameterized-query-for-mysql-with-c-sharp) — Steve, Sep 23 '14 at 16:55
I replaced radio buttons with combo boxes so now it works fine but i want to know how to correct this error. I am just a beginner so it doesn't matter whether it is 2014 or 2020 everyone has to start first so this is my starting point so please anyone can show me what is the secured and correct way to do it — Hasiya55, Sep 23 '14 at 17:10
Everyone was a beginner once. But beginning now one can begin at a better starting point than beginning in say 2000. Please have a look at the accepted answer of the second link which [Steve gave you](http://stackoverflow.com/questions/652978/parameterized-query-for-mysql-with-c-sharp) to see a parameterized query in action. — VMai, Sep 23 '14 at 17:34

score 0 · Answer 1 · answered Sep 23 '14 at 16:00

0

You probably missed an apostrophe here in the end of the line(added it in bold):

query.Append("Insert Into my_project_data.vehicle(ChassyNumber ,ManufacturedYear, EngineCapacity,Price,Features ,VehicleBrand , VehicleType) Values('" + chassy_txt.Text + "','"+manufac_year_txt.Text+"','"+Engine_Capasity_txt.Text+"','"+Price_txt.Text+"','"+Features_rich_txt.Text+"',");

Should be '"+Features_rich_txt.Text+"','"); and you have: '"+Features_rich_txt.Text+"',");

Also you need an apostrophe after the car model: like: query.Append("BMW ',"); and so on.

Please also consider one of the comments above regarding the SQL injection.

answered Sep 23 '14 at 16:00

Vladimir Sadov

41
2

It didn't work query.Append("Insert.......................... '"+Features_rich_txt.Text+"',"); this final " is used to close the insert statement – Hasiya55 Sep 23 '14 at 16:28
Well, your VehicleBrand field value must be between apostrophes as far as understood. It is not, you have to fix it. – Vladimir Sadov Sep 23 '14 at 16:51
And yes, it is pretty easy to ruin whole your database, get your clients private data etc using this code. Consider, for example (please don't try it!) that somebody will put value _','','','','','',''); delete from my_project_data.vehicle;_ to the textbox chassy_txt? – Vladimir Sadov Sep 23 '14 at 16:59

SQL Syntax Error

1 Answers1