C#•9mo ago

MySql Syntax error

I am creating a method that will take in an object with properties that have data that is needed for the DB Update. I am getting an error that is included in a screen shot. Here is my code:

C#
public static void UpdateCustomerName(Customer customer)
{
  string updateName = $"UPDATE client_schedule.customer SET customerName = '{customer.CustomerName}'" +
      $"WHERE customerId = {customer.CustomerID}";
  
  DBConnect.StartConnection(); // Starts the connection
  
  try
  {
    using (DBConnect.Conn)
      using (MySqlCommand cmd = new MySqlCommand(updateName, DBConnect.Conn))
      {
        cmd.ExecuteNonQuery();
      }
    }
  }
  catch (MySqlException ex) 
  {
    DBConnect.StopConnection();
    
    MessageBox.Show(ex.Message);
  }

  DBConnect.StopConnection();
}

C#
public static void UpdateCustomerName(Customer customer)
{
  string updateName = $"UPDATE client_schedule.customer SET customerName = '{customer.CustomerName}'" +
      $"WHERE customerId = {customer.CustomerID}";
  
  DBConnect.StartConnection(); // Starts the connection
  
  try
  {
    using (DBConnect.Conn)
      using (MySqlCommand cmd = new MySqlCommand(updateName, DBConnect.Conn))
      {
        cmd.ExecuteNonQuery();
      }
    }
  }
  catch (MySqlException ex) 
  {
    DBConnect.StopConnection();
    
    MessageBox.Show(ex.Message);
  }

  DBConnect.StopConnection();
}

14 Replies

Jimmacle•9mo ago

i can see it, but print out the string and see if you can find it also, you should absolutely not be using string concatenation to build SQL queries like this you're making your application vulnerable to SQL injection use parameterized queries instead

XantresOP•9mo ago

is that when you use the @ symbol?

Jimmacle•9mo ago

yes it stops someone from trying to change their name to, for example, ';-- and wiping out all the names in your database or generally executing any SQL query they want to

XantresOP•9mo ago

I have printed it out and have reviewed the MySql manual, but am not seeing a difference, can you point out where my mistake is other then the vulnerable code?

Jimmacle•9mo ago

you're missing a space between the end quote of the customer name and the WHERE

XantresOP•9mo ago

So you need a space after the end of the Where clause?

Jimmacle•9mo ago

try it and see and read what i said more closely

XantresOP•9mo ago

So with parameterized queries, if I have an object as a perameter, I could do something like "@customer.CustomerID" or do I need to break out the object perameters into separate variables before using them in the string?

Jimmacle•9mo ago

you would not reference C# variables directly in the query the SQL parameters are placeholders, then you write additional code to add values for those placeholders https://visualstudiomagazine.com/articles/2017/07/01/parameterized-queries.aspx

XantresOP•9mo ago

thanks!

Jimmacle•9mo ago

this separates the actual command you want to execute and the data the command needs which makes it impossible for the data to change the command that's executed

XantresOP•9mo ago

so I changed out my original string with this:

C#
  string updateName = @"UPDATE client_schedule.customer SET customerName = '@customer.CustomerName' WHERE customerId = @customer.CustomerID ";

C#
  string updateName = @"UPDATE client_schedule.customer SET customerName = '@customer.CustomerName' WHERE customerId = @customer.CustomerID ";

and I got a fatal error during execution I apologize for my apparent ignorance, I am currently in school learning C#

Jimmacle•9mo ago

if that's all you changed then you're missing steps, read the article i linked

XantresOP•9mo ago

Thank you for your time @Jimmacle, I appreciate the direction you provided.

Gaming

Programming

MySql Syntax error