Apostrophes Not Handled when Adding to Database using C#

[radderfire]

Hi Guys

I have some old C# code that I wrote that adds data into a MariaDB using the MySql.Data.MySqlClient namespace.

The problem is I can't add text containing apostrophes into the database properly. I know this is a classic problem, but I can't remember how to fix it, apart from using stored procedures instead, which looks a bit complicated, especially when using MariaDB.

Anyway, my code, and in particular the SQL insert statement, is:

 try {

            // Open database connection

            // Technique is passing the connection string to the constructor of the MySqlConnection class
            // Note you have to import the MySql.Data.MySqlClient namespace to be able to use this
            MySqlConnection conn = new MySqlConnection(connStr);
            conn.Open();

            // For testing
            // lblMyTestLabel.Text = "Connection Opened!";

            // Get values from web form

            String BrandName = txtBrandName.Text;
            String BrandDesc = txtBrandDesc.Text;
            String BrandUrl = txtBrandUrl.Text;
            String BrandPage = txtBrandPage.Text;

            // Add the brand


            String SqlInsert = "INSERT into brands (BrandName, BrandDesc, BrandUrl, BrandPage) VALUES ('" + BrandName + "', '" + BrandDesc + "', '" + BrandUrl + "', '" + BrandPage + "')";

            // Create a command to run against the database
            MySqlCommand cmd = new MySqlCommand(SqlInsert);
            // Specify the CommandType for the connection, in this case it's text from a string, not a stored procedure
            cmd.CommandType = System.Data.CommandType.Text;
            // Give the MySQLCommand a connection to use
            cmd.Connection = conn;

            // Execute the non query
            cmd.ExecuteNonQuery();

            // Close the connection
            conn.Close();

            // Hide the form

            formBrandAdd.Visible = false;

            // Confirm done

            lblUserMessage.Text = "Brand added! <a href='brand-add.aspx'>Add another</a>";
            lblUserMessage.BackColor = System.Drawing.Color.LightGreen;

            //For testing
            // lblMyTestLabel.Text = "Yes, that's completed! <a href='brand-add.aspx'>Add another brand</a>";
            // lblMyTestLabel.BackColor = System.Drawing.Color.LightGreen;

        }

What do I need to do to be able to handle apostrophes added in the text fields?

Any suggestions or links to code examples appreciated.

Thanks

 

[radderfire]

This is the line that is the problem:

String SqlInsert = "INSERT into brands (BrandName, BrandDesc, BrandUrl, BrandPage) VALUES ('" + BrandName + "', '" + BrandDesc + "', '" + BrandUrl + "', '" + BrandPage + "')";

The problem is happening because I am using ' around the variable values. Before I try anything else, is there a simple amend to this line that would make it work? I know this is a classic problem.

Thanks

 

[radderfire]

If I used a prepared statement, would this be the way to get the variables from the form data into it?

cmd.Parameters.AddWithValue("@BrandName", BrandName);

 

[radderfire]

I'm getting there, essentially that has worked, thanks, I just had to add a NULL in as well (something to do with id autoincrement I expect):

cmd.CommandText = "INSERT INTO brands VALUES(NULL, @BrandName, @BrandDesc, @BrandUrl, @BrandPage)";

 

[radderfire]

The example I was following was slightly different, so I've just checked, and your line worked too, so thanks:

cmd.CommandText = "INSERT into brands (BrandName, BrandDesc, BrandUrl, BrandPage) VALUES (@BrandName, @BrandDesc, @BrandUrl, @BrandPage)";

 

[radderfire]

OK, next question is how do I write a prepared statement for an UPDATE? I have made a guess, which is probably wrong:

cmd.CommandText = "UPDATE brands SET (BrandName, BrandDesc, BrandUrl, BrandPage) VALUES (@BrandName, @BrandDesc, @BrandUrl, @BrandPage) WHERE BrandId = (@BrandId2)";

Thanks

 

[radderfire]

Great, my update is now working, many thanks.