Substring error though not using substring.

Question

I have the following code:

public static long CreateVendorsEmailJob(List<Recipient> recipients, string subject, string body)
        {
            long emailJobId;

            using (var connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString))
            {
                connection.Open();

                // create email job
                using (var command = new MySqlCommand())
                {
                    command.Connection = connection;
                    command.CommandType = CommandType.Text;
                    command.CommandText =
                        string.Format(
                            "INSERT INTO email_job (parent_id, job_type_lookup_id, from_email, from_name, subject, body_type_lookup_id, body, status_lookup_id, total_emails, persist_addresses) VALUES ({0}, {1}, '{2}', '{3}', '{4}', {5}, '{6}', {7}, {8}, {9})",
                            5000,
                            745,
                            "[email protected]",
                            "Company Management System",
                            subject,
                            22,
                            body,
                            27,
                            recipients.Count,
                            1);

                    command.ExecuteNonQuery();

                    emailJobId = command.LastInsertedId;
                }

                using (var command = new MySqlCommand())
                {
                    command.Connection = connection;
                    command.CommandType = CommandType.Text;

                    string commandText = "INSERT INTO email_job_email (job_id, comm_optin_id, name, email) VALUES ";
                    var valuesToAdd = new List<string>();

                    recipients.ForEach(r => valuesToAdd.Add(string.Format("({0}, {1}, '{2}', '{3}')", emailJobId, r.RecipientId, r.Name, r.EmailAddress)));

                    commandText += string.Join(",", valuesToAdd.ToArray());

                    command.CommandText = commandText;
                    command.ExecuteNonQuery();
                }

                connection.Close();
            }

            return emailJobId;
        }

This code runs fine when running for one task but then I run this same code for another task and it doesn't work. It gives me the following error:

Index and length must refer to a location within the string.
Parameter name: length

Now the only difference between each time I run it is the subject and the body of the message. They are stored in a resource file and passed in when the method is called. But what I can't seem to figure out is where would this exception even be happening. It is a windows service and runs on a remote machine so debugging it is not that easy and I don't have a good dev environment to mirror theirs.

The error I have seen before but it always seems to be with some sort of substring manipulation. The text is just some basic stuff and one is very similar to the other so I can't even see why that would be causing this.

Any ideas on what or why?

EDIT: Ok so after I had an aha moment and realized that I could print out a stack trace here is what I got -

at MySql.Data.MySqlClient.MySqlTokenizer.NextParameter()
   at MySql.Data.MySqlClient.Statement.InternalBindParameters(String sql, MySqlParameterCollection parameters, MySqlPacket packet)
   at MySql.Data.MySqlClient.Statement.BindParameters()
   at MySql.Data.MySqlClient.PreparableStatement.Execute()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
   at PCM.AutoWorkEngine.Tasks.RecurringVendorMailer.Entities.DataMappers.EmailJobMapper.CreateVendorsEmailJob(List`1 recipients, String subject, String body) in C:\Projects\PCM\PCM.AutoWorkEngine.RecurringVendorMailer\Entities\DataMappers\EmailJobMapper.cs:line 65
   at PCM.AutoWorkEngine.Tasks.RecurringVendorMailer.HOAVendorTask.Execute() in C:\Projects\PCM\PCM.AutoWorkEngine.RecurringVendorMailer\HOAVendorTask.cs:line 24
   at PCM.AutoWorkEngine.AutoWorkEngineService.Start() in C:\Projects\PCM\PCM.AutoWorkEngine\AutoWorkEngineService.cs:line 80 

What I am not familiar with as much is prepared statements for MySql. I am not trying to use anything like that. But I do have single quotes in the text. But I have those in both texts and they work fine in the first so not sure if that is it. I escape them in the resource file using backslash.

Answer 1

You need to sanitize the subject and body. For example if your subject is

');

you'll run into trouble. See for example here and here.

Answer 2

You may find this MySQL bug report interesting:

SQL string with escaped backslash inside.

examples:

insert into pb_im set m_from=1, m_to=1, m_content='\\=';
insert into pb_im set m_from=1, m_to=1, m_content='\\'; /* works sometimes, fail somtimes */

backslash in string - connector return exeption
works on Query Browser/CLI/connector 5.x, fail on 6.0.3:

Index and length must refer to a location within the string.
Parameter name: length

at System.String.InternalSubStringWithChecks(Int32 startIndex, Int32 length, Boolean fAlwaysCopy)
at MySql.Data.MySqlClient.MySqlTokenizer.NextParameter()
at MySql.Data.MySqlClient.Statement.InternalBindParameters(String sql,

It's not clear to me if this was every fixed; the bug report is ambiguous. You should at least check to make sure you're using the latest MySQL libs, though.

Maybe play with any strings containing backslashes - see if you can isolate the problem data.

Answer 3

This is usually caused by your string containing special character and name is often the culprit, as an example O'Reilly. If you have that string in your INSERT...VALUES statement, then you will get strange errors reported. There are also other special character like & and etc. to look out for.

Two suggestion to resolve your problem: - Use parameter binding rather than building your own SQL string - But if you have to use the above, try write a small routine that automatically escaped the string prior to be used in the parameter. eg.

private string EscapeSQLParamString(string param)
{
 ...............
 return escapedString;
}

then use it in your String format statement

string.Format("({0}, {1}, '{2}', '{3}')", emailJobId, r.RecipientId, EscapeSQLParamString(r.Name), EscapeSQLParamString(r.EmailAddress))