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

 

[Rroff]

String literals?

Middle of the document:

Strings - C# Programming Guide - C#

Learn about strings in C# programming. See information on declaring and initializing strings, the immutability of string objects, and string escape sequences.

learn.microsoft.com

Personally I hate C# so a bit leery of giving anything too specific in case there are things I'm not aware of.

 

[Armageus]

You could check for a single quote within the string and then replace it with an escape character \'

 

[Mr Jack]

The way to do it is to use prepared statements. Which is just generally a good idea when interacting with the database anyway - you need to keep potential forms of attack in mind as you write.

 

[Rroff]

you need to keep potential forms of attack in mind as you write.

Definitely something you need to do these days and a good habit even if writing more casual stuff/prototyping but also far easier said than done.

 

[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);

 

[Mr Jack]

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.

No.

You want to do this (untested so I might typo):

MySqlCommand cmd = new MySqlCommand();

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

cmd.Parameters.AddWithValue("@BrandName", BrandName);
cmd.Parameters.AddWithValue("@BrandDesc", BrandDesc);
cmd.Parameters.AddWithValue("@BrandUrl", BrandUrl);
cmd.Parameters.AddWithValue("@BrandPage", BrandPage);

cmd.

For some reason the board software won't let me write ExecuteNonQuery(); in the code block.

 

[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)";

 

[Mr Jack]

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

Yeah, your first version inserts into all the values in the order they're specified in the table. The one I used just copied how you were doing it in the first pace by inserting into only a selection of values and in a specified order.

 

[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

 

[Mr Jack]

You write it exactly how you would write the sql statement that you want but with @whatever substituting for each value and without any quotes around the values. You don't change the structure of the query at all, so your SQL needs to look like:

UPDATE brands SET BrandName=@BrandName, BrandDesc=@BrandDesc, ... etc ... WHERE BrandId=@BrandId

Just like a normal update.

 

[radderfire]

Great, my update is now working, many thanks.