C
C#2mo ago
Xantres

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();
}
No description
14 Replies
Jimmacle
Jimmacle2mo 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
Xantres2mo ago
is that when you use the @ symbol?
Jimmacle
Jimmacle2mo 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
Xantres2mo 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
Jimmacle2mo ago
you're missing a space between the end quote of the customer name and the WHERE
Xantres
Xantres2mo ago
So you need a space after the end of the Where clause?
Jimmacle
Jimmacle2mo ago
try it and see and read what i said more closely
Xantres
Xantres2mo 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
Jimmacle2mo 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
Xantres2mo ago
thanks!
Jimmacle
Jimmacle2mo 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
Xantres2mo 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
Jimmacle2mo ago
if that's all you changed then you're missing steps, read the article i linked
Xantres
Xantres2mo ago
Thank you for your time @Jimmacle, I appreciate the direction you provided.
Want results from more Discord servers?
Add your server
More Posts