CODEWITHSUNDEEP
c#mysqlsql
Mark Pace
Mark Pace

Reputation: 201

UPDATE string with an apostrophe in the string

I am working on a C# windows form which is connected to MySQL and updates strings within the form. I have everything working properly except for a small issue.

Say you want to update the "notes" field to read "The dog's bone", that apostrophe is causing the SQL query to end and cause an error. How can I get around this please?

UPDATE `database`
SET `notes` = 'The dog's bone'
WHERE `Pet` = 'Dog';

Thanks!

Upvotes: 0

Views: 2743

Answers (2)

Soner Gönül
Soner Gönül

Reputation: 98760

You can escape ' character in MySQL with doubling it like ''.

Other than that, if you use parameterized queries, you will not need this at all. Just pass your The dog's bone string directly to your parameterized query and you will be fine.

Also I strongly suspect you try to use UPDATE instead of SELECT statement. In MySQL, SELECT syntax doesn't have any SET part.

And using a reserved keyword as a column name is a bad idea. As a best practice, change your database column name to non-reserved word.

using(var con = new MySqlConnection(conString))
using(var cmd = con.CreateCommand())
{
    cmd.CommandText = @"UPDATE `database` SET notes = @notes
                        WHERE Pet = @pet";
    cmd.Parameters.AddWithValue("@notes", "The dog's bone");
    cmd.Parameters.AddWithValue("@pet", "Dog");

    con.Open();
    cmd.ExecuteNonQuery();
}

I used AddWithValue method as an example in my code since I didn't know your column types but you don't use it. This method may generate unexpected and surprising results sometimes. Use Add method overloads to specify your parameter type and it's size.

Upvotes: 6

potashin
potashin

Reputation: 44581

Escape it with another single quote ':

SELECT `database`
SET `notes` = 'The dog''s bone'
WHERE `Pet` = 'Dog';

Upvotes: 2

Related Questions