How to Update paragraph from mysql table using c#

Question

I tried to update a paragraph from mysql table,but i got error like this

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's first-ever super-villainess."

My mysql Query

cmd.CommandText = "UPDATE `moviemaster` SET  `Runtime`='" + runtime + "',`DateMasterId`='" + dateid + "',`Trailer`='" + trailer + "',`Synopsis`='" + synopsis + "' WHERE `MovieMasterId`='" + movieid + "'";

I got error in 'synopsis',it's a big data containing a large paragraph.If i romove 'Synopsis' section from the query,everything working fine.What exactly the problem.How can i resolve this?

What is the `synopsis` variable _exactly_? `MovieMasterId` seems like a numerical column as well. If so, you don't need single quotes with it. You should always use [parameterized queries](http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/) by the way. This kind of string concatenations are open for [SQL Injection](http://en.wikipedia.org/wiki/SQL_injection) attacks. And parameterized statements handle the escape characters as well. — Soner Gönül, Nov 19 '15 at 09:37
Just a side-node: please use parameters instead string-concatenation otherwise your code is potentially open for SQL-Injection. — Marc, Nov 19 '15 at 09:40
@Unnikrishnan.S No, I mean it's _real_ value that it contains. The value when you see on debugger. — Soner Gönül, Nov 19 '15 at 09:43
You probably have some sort of character in the paragraph that ends the statement such as a '`'. Using parametrized queries will fix that issue. Add a line break to the update statement and check to see what query is being passed. — Paul, Nov 19 '15 at 09:44

score 1 · Answer 1 · answered Nov 19 '15 at 12:41

@SonerGönül:Ok,fine.. then please show me an example of parameterised query

Sure. I also wanna add a few best practice as well.

Use using statement to dispose your connection and command automatically.
You don't need to escape every column with `` characters. You should only escape if they are reserved keywords for your db provider. Of course, at the end, changing them to non-reserved words is better.
Do not use AddWithValue method. It may generate upexpected and surprising result sometimes. Use Add method overload to specify your parameter type and it's size.

using (var con = new SqlConnection(conString))
using(var cmd = con.CreateCommand())
{
     cmd.CommandText = @"UPDATE moviemaster 
                         SET Runtime = @runtime, DateMasterId = @dateid, Trailer = @trailer, Synopsis = @synopsis
                         WHERE MovieMasterId = @movieid";
     cmd.Parameters.Add("@runtime", MySqlDbType.VarChar).Value = runtime; ;
     cmd.Parameters.Add("@dateid", MySqlDbType.VarChar).Value = dateid;
     cmd.Parameters.Add("@trailer", MySqlDbType.VarChar).Value = trailer;
     cmd.Parameters.Add("@synopsis", MySqlDbType.VarChar).Value = synopsis;
     cmd.Parameters.Add("@movieid", MySqlDbType.VarChar).Value = movieid;
     // I assumed your column types are VarChar.
     con.Open();
     cmd.ExecuteNonQuery();
}

@Downvoter care to comment at least so I can see where I _might_ be wrong? — Soner Gönül, Nov 19 '15 at 15:08
@SonerGönül I up voted this answer since I myself have had bad experience with weird down votes. And in any case this was a useful answer — chrisl08, Nov 25 '15 at 08:53
@chrisl08 Thanks. I just didn't figure out anything wrong about my answer. — Soner Gönül, Nov 25 '15 at 08:58

score 0 · Accepted Answer · edited May 23 '17 at 11:44

Please avoid using inline query. Your database can be subjected to SQL Injection. See this example, on what can be done using SQL Injection.

And use paramterized query instead. Here is the example taken from here. This way, even if your string has special characters, it will not break and let you insert/update/select based on parameters.

private String readCommand = "SELECT LEVEL FROM USERS WHERE VAL_1 = @param_val_1 AND VAL_2 = @param_val_2;";
public bool read(string id)
{
    level = -1;
    MySqlCommand m = new MySqlCommand(readCommand);
    m.Parameters.AddWithValue("@param_val_1", val1);
    m.Parameters.AddWithValue("@param_val_2", val2);
    level = Convert.ToInt32(m.ExecuteScalar());
    return true;
}

and finally, your query will become

cmd.CommandText = "UPDATE `moviemaster` SET  `Runtime`= @param1,`DateMasterId`= @dateid, `Trailer`= @trailer,`Synopsis`= @synopsis WHERE `MovieMasterId`= @movieid";

cmd.Parameters.AddWithValue("@param1", runtime);
cmd.Parameters.AddWithValue("@dateid", dateid);
cmd.Parameters.AddWithValue("@trailer", trailer);
cmd.Parameters.AddWithValue("@synopsis", synopsis);
cmd.Parameters.AddWithValue("@movieid", movieid);

How to Update paragraph from mysql table using c#

2 Answers2