MySQL parameterized query is functioning like it is not parameterized in C# application

Question

I keep getting this error: MySql.Data.MySqlClient.MySqlException: '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 ''Feature: SecurityTesting @mytag Scenario: SQL Injection Given I visit ' at line 1'

The parameter Gherkin is the one causing the query to fail. I have tried both ? and @ as parameter prefixes and nothing changes.

Here's my code:

string CommandText = " INSERT INTO Feature(`Path`, Gherkin, RepoID, `Name`, Updated) VALUES (?Path,  ?Gherkin , ?RepoID, ?Name, ?Updated) ON DUPLICATE KEY UPDATE Gherkin = VALUES(?Gherkin); ";


       

            using (MySqlConnection connection = new MySqlConnection())
            {
                connection.ConnectionString = ConfigurationManager.ConnectionStrings["TAF_DB"].ConnectionString;
                using (MySqlCommand command = new MySqlCommand())
                {

                    var gherkinParam = new MySqlParameter("Gherkin", test.Gherkin);
                    //var gherkinParam = new MySqlParameter("Gherkin", MySqlDbType.VarChar);
                    var pathParam = new MySqlParameter("Path", MySqlDbType.VarChar);
                    var RepoIDParam = new MySqlParameter("RepoID", MySqlDbType.Int64);
                    var nameParam = new MySqlParameter("Name", MySqlDbType.VarChar);
                    var updatedParam = new MySqlParameter("Updated", MySqlDbType.VarChar);

                    gherkinParam.Value = test.Gherkin;
                    command.Parameters.Add(gherkinParam);
                    pathParam.Value = test.Path;
                    command.Parameters.Add(pathParam);
                    RepoIDParam.Value = test.RepoID;

                    command.Parameters.Add(RepoIDParam);
                    nameParam.Value = test.Name;

                    command.Parameters.Add(nameParam);
                    updatedParam.Value = test.Updated;
                    command.Parameters.Add(updatedParam);

                    command.Connection = connection;
                    command.CommandType = CommandType.Text;
                    command.CommandText = CommandText;
                    connection.Open();
                    command.ExecuteNonQuery();
                    connection.Close();

                }
            }

Some side tips: command.CommandType = CommandType.Text; is not necessary, it's the default. connection.Close(); also not necessary, using will do that. You can use construcotr parameters to shorten to using (var connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["TAF_DB"].ConnectionString)) using (var command = new MySqlCommand(CommandText, connection)) {... you can also shorten the parameters to command.Parameters.Add(new MySqlParameter("@Gherkin", MySqlDbType.VarChar).Value = test.Gherkin; etc. You can use a multi-line string for the command-text with @" — Charlieface
– Charlieface, Commented Dec 20, 2021 at 16:01

forpas · Accepted Answer · 2021-12-20 15:48:53Z

2

You should use the name of the column Gherkin inside the function VALUES() and not the named parameter ?Gherkin:

UPDATE Gherkin = VALUES(Gherkin)

answered Dec 20, 2021 at 15:48

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user16951074 Over a year ago

Thanks! This did the trick for the last part of the statement but I still had to have a @ or ? prefix for the first 'VALUES" part or else the value was just always "Gherkin".

forpas Over a year ago

@Benny of course you have to use ?Gherkin inside the VALUES list. My answer is about the VALUES() function in the UPDATE statement.

Collectives™ on Stack Overflow

MySQL parameterized query is functioning like it is not parameterized in C# application

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related