Syntax error when inserting many columns into MySQL

Question

I'm trying to insert some data into a MySQL database using the below C# code. If I run this command it throws an exception like the following:

some error: You have an error in your SQL syntax; check the manual that correspo
nds to your MySQL server version for the right syntax to use near ': The Third Reich,
                                ,
                         ' at line 25

Is there a syntax error somewhere? I can't find anything wrong.

 var rowContent = String.Format(@"
                            INSERT INTO animes
                            (
                            `JapanTitle`,
                            `AmericanTitle`,
                            `GermanTitle`,
                            `AnimeType`,
                            `CoverPath`,
                            `Episodes`,
                            `MinutesPerEpisodes`,
                            `JapanStatus`,
                            `AmericanStatus`,
                            `GermanStatus`,
                            `JapanTimeSpan`,
                            `AmericanTimeSpan`,
                            `GermanTimeSpan`,
                            `MainGenres`,
                            `SubGenres`,
                            `JapanStudios`,
                            `AmericanStudios`,
                            `GermanStudios`,
                            `GermanDescription`,
                            `EnglishDescription`)
                            VALUES
                            ({0},
                            {1},
                            {2},
                            {3},
                            {4},
                            {5},
                            {6},
                            {7},
                            {8},
                            {9},
                            {10},
                            {11},
                            {12},
                            {13},
                            {14},
                            {15},
                            {16},
                            {17},
                            {18},
                            {19});", entity.JapanTitle,
                                   entity.AmericanTitle,
                                   entity.GermanTitle,
                                   entity.AnimeType.ToString(),
                                   entity.WallPaper.FileName,
                                   entity.Episodes,
                                   entity.MinutesPerEpisode,
                                   entity.JapanStatus.ToString(),
                                   entity.AmericanStatus.ToString(),
                                   entity.GermanStatus.ToString(),
                                   entity.JapanTimeSpan.ToString(),
                                   entity.AmericanTimeSpan.ToString(),
                                   entity.GermanTimeSpan.ToString(),
                                   string.Join(",", entity.MainGenres),
                                   string.Join(",", entity.SubGenres),
                                   string.Join(",", entity.JapanStudios),
                                   string.Join(",", entity.AmericanStudios),
                                   string.Join(",", entity.GermanStudios),
                                   entity.GermanDescription,
                                   entity.EnglishDescription);

If I look at this code, I think it is pretty bad code. Is there a better way to insert many columns into a database without something like the entity framework?

Don't use `string.Format`; use a prepared statement instead. — p.s.w.g, Dec 19 '14 at 22:54
MySQL's error messages leave much to be desired but your life will be a little bit easier if you use less whitespace in your queries. That is really excessive. — Air, Dec 19 '14 at 23:03

score 4 · Accepted Answer · edited May 23 '17 at 12:05

Your main error is caused by the missing quotes around your strings. In a sql command text, when you want to pass a value for a text field you need to put this value between single quotes.
But also with appropriate quotes around your values you have another big problem called Sql Injection.

The only secure way to use a command like that is through a parameterized query.
Something like this (I will cut your code because is too long)

string cmdText = @"INSERT INTO animes
                  (JapanTitle,AmericanTitle,GermanTitle,AnimeType,.....)
                  VALUES(@japtitle, @usatitle, @germantitle, @animtype, ....)";
MySqlCommand cmd = new MySqlCommand(cmdText, connection);
cmd.Parameters.AddWithValue("@japtitle",  entity.JapanTitle);
cmd.Parameters.AddWithValue("@usatitle",  entity.AmericanTitle);
cmd.Parameters.AddWithValue("@germantitle",  entity.GermanTitle);
cmd.Parameters.AddWithValue("@animtype",  entity.AnimeType);
....
cmd.ExecuteNonQuery();

In this way you don't write directly the values in the command text, but put placeholders for the parameters value. It is the database engine that will figure out how to use the parameters passed along the command. (And no need to worry about quotes around your string or escape quotes that are part of your values)

Notice also that the AddWithValue method of the Parameters collection has its own quirks. You need to pass the value with the exact datatype expected by the database field (So, no ToString() for AnimeType if the field expects an integer)

score 3 · Answer 2 · answered Dec 19 '14 at 23:22

First of all, your database should probably look something like this:

An animes table with non-language values like CoverPath, Episodes, etc. - one row per movie
An animes_lang table with language-specific values like Title, Status, TimeSpan, Studios, etc. - one row per language per movie

That would probably make it easier to maintain.

At any rate, I can make the query construction a little easier but you'll need to specify all of the parameters.

String[] cols = {
  "JapanTitle",
  "AmericanTitle",
  "GermanTitle",
  "AnimeType",
  "CoverPath",
  "Episodes",
  "MinutesPerEpisodes",
  "JapanStatus",
  "AmericanStatus",
  "GermanStatus",
  "JapanTimeSpan",
  "AmericanTimeSpan",
  "GermanTimeSpan",
  "MainGenres",
  "SubGenres",
  "JapanStudios",
  "AmericanStudios",
  "GermanStudios",
  "GermanDescription",
  "EnglishDescription"
};

String query =
  "INSERT INTO animes (" + String.Join(", ", cols) +
  ") VALUES (" + String.Join(", @", cols) + ");";

MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("@JapanTitle", entity.JapanTitle);
cmd.Parameters.AddWithValue("@AmericanTitle", entity.AmericanTitle);
cmd.Parameters.AddWithValue("@GermanTitle", entity.GermanTitle);
. . .
cmd.Parameters.AddWithValue("@EnglishDescription", entity.EnglishDescription);
cmd.ExecuteNonQuery()

Please use Steve's answer or this one. Don't go the String.Format route. It may be that SQL Injection isn't a worry here, but make a habit of doing this the safe and right way.

Good point, normalization is a must here. Suppose to have X languages. — Steve, Dec 19 '14 at 23:33

Syntax error when inserting many columns into MySQL

2 Answers2