I am trying to add customers to a MySQL database and then populate a DataGridView with the data. Without the addCustCmd.Prepare(); method everything works fine. The customer is added to the database and my DGV is populated with new data. But I would like to add security to these inputs and prevent SQL injection, thus the Prepare(); method.

public static void addCust(string cCustomerName, string cAddress, string cAddress2, int cPhone, int cPostalCode, string cCity, string cCountry)
        {
            MySqlConnection addCustConn = new MySqlConnection(connString);
            MySqlCommand addCustCmd = new MySqlCommand("INSERT INTO country VALUES "  
                "(NULL, @countryName, CURRENT_TIMESTAMP,"  
                "@user, CURRENT_TIMESTAMP, @user);"  

                "INSERT INTO city "  
                "VALUES(NULL, @city, LAST_INSERT_ID(), CURRENT_TIMESTAMP, @user, CURRENT_TIMESTAMP, "  
                "@user);"  

                "INSERT INTO address "  
                "VALUES(NULL, @address, @address2, LAST_INSERT_ID(), @postalCode, @phone, CURRENT_TIMESTAMP, @user,"  
                "CURRENT_TIMESTAMP, @user);"  

                "INSERT INTO customer "  
                "VALUES(NULL, @customerName, LAST_INSERT_ID(), active, CURRENT_TIMESTAMP, @user,"  
                "CURRENT_TIMESTAMP, @user);", addCustConn);
            addCustConn.Open();
            addCustCmd.Parameters.AddWithValue("@countryName", cCountry);
            addCustCmd.Parameters.AddWithValue("@city", cCity);
            addCustCmd.Parameters.AddWithValue("@address", cAddress);
            addCustCmd.Parameters.AddWithValue("@address2", cAddress2);
            addCustCmd.Parameters.AddWithValue("@postalCode", cPostalCode);
            addCustCmd.Parameters.AddWithValue("@phone", cPhone);
            addCustCmd.Parameters.AddWithValue("@customerName", cCustomerName);
            addCustCmd.Parameters.AddWithValue("@user", LoginForm.currentUser);

            addCustCmd.Prepare(); //Commenting this out allows the program to run no problems
            addCustCmd.ExecuteNonQuery();

            addCustConn.Close();
        }//end addCust

This is the error that pops up when I try to run it: "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 'INSERT INTO city VALUES(NULL, ?, LAST_INSERT_ID(), CURRENT_TIMESTAMP, ?, CURRENT' at line 1"

CodePudding user response：

Each insert operation has to be a separate command to use prepared statements.

SQL syntax for prepared statements does not support multi-statements (that is, multiple statements within a single string separated by ; characters).

Documentation source