C#•3mo 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•3mo 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

Xantres•3mo ago

is that when you use the @ symbol?

Jimmacle•3mo 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

Xantres•3mo 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•3mo ago

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

Xantres•3mo ago

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

Jimmacle•3mo ago

try it and see and read what i said more closely

Xantres•3mo 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•3mo 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

Xantres•3mo ago

thanks!

Jimmacle•3mo 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

Xantres•3mo 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•3mo ago

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

Xantres•3mo ago

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

Gaming

Programming

MySql Syntax error