Update multiple mysql columns using parametarized update command

Question

In my application I want to update multiple MySQL columns using the UPDATE command. I tried it with the following code but I know that it's really insecure. Because it led to SQL Injection attacks. But I have no idea how to write a query with parameters to update multiple MySQL columns.

This is my code:

string constring = string.Format("datasource='{0}';port='{1}';database='{2}';username=claimsprologin;password=xxxxxxxxxxxxxxxx;Connect Timeout=180;Command Timeout=180", serveriplable.Text, portno.Text, databasenamelable.Text);
string Query = "update claimloans set loannumber= '" + this.loannumbertextbox.Text.Trim() + " ', pool = '" + this.loanpooltextbox.Text.Trim() + "' , disblid = '" + this.disbidtextbox.Text.Trim() + "' , category = '" + this.categorytxtbox.Text.Trim() + " ', subcacategory = '" + this.subcategorytxtbox.Text.Trim() + " ', invoice = '" + this.invoicenumbertextbox.Text.Trim() + " ', invoicedate = '" + this.invoicedatetextbox.Text.Trim() + " ', docs = '" + this.docscombobox.Text.Trim() + "' , where username = '" + this.usernamelable.Text.Trim() + "' ;";
MySqlConnection conwaqDatabase = new MySqlConnection(constring);
MySqlCommand cmdwaqDatabase = new MySqlCommand(Query, conwaqDatabase);
MySqlDataReader myreader;

try {
    conwaqDatabase.Open();
    myreader = cmdwaqDatabase.ExecuteReader();
    while (myreader.Read()) { }

    MessageBox.Show("Credential informations are updated");

    conwaqDatabase.Close();
}

catch {

}

Answer 1

You can use something like this. You add place holders in you sql command and then add the parameter values in after then you run the ExecuteReader.

string constring = string.Format("datasource='{0}';port='{1}';database='{2}';username=claimsprologin;password=gfx)C#G$aD3bL`@;Connect Timeout=180;Command Timeout=180", serveriplable.Text, portno.Text, databasenamelable.Text);
string Query = "update claimloans set loannumber= @loannumbertextbox, pool = @loanpooltextbox, disblid = @disbidtextbox, category = @categorytxtbox, subcacategory = @subcategorytxtbox, invoice = @invoicenumbertextbox, invoicedate = @invoicedatetextbox, docs = @docscombobox, where username = @usernamelable;";
MySqlConnection conwaqDatabase = new MySqlConnection(constring);
MySqlCommand cmdwaqDatabase = new MySqlCommand(Query, conwaqDatabase);

cmdwaqDatabase .Parameters.AddWithValue("@loannumbertextbox", this.loannumbertextbox.Text.Trim());