How do I create a safe query for performing a bulk insert in MySQL using MySQLCommand in C# without using a stored proc?

Question

In the following sample, I build a query to do a bulk insert into a MySQL database:

const string QUERY = "INSERT INTO contacts (first_name,last_name) VALUES{0};";

public string BuildQuery(IEnumerable<contact> contacts)
{
    List<string> values = new List<string>();

    foreach (var contact in contacts)
    {
        values.Add(string.Format("('{0}','{1}')", contact.first_name, contact.last_name));
    }

    return string.Format(QUERY, string.Join(",", values));
}

The result might look something like this:

INSERT INTO contacts (first_name,last_name) VALUES("J","Kappers"),("A","Temple")

What can I do to write a safer query that isn't prone to SQL Injection?

Use Stored procedure in mysql and pass the values in this function. — Romil Kumar Jain
– Romil Kumar Jain, Commented Jun 25, 2012 at 13:09
Thanks for the suggestion, Romil! Unfortunately, using a stored proc is not an option for me. Hoping for a different solution. — Kappers
– Kappers, Commented Jun 25, 2012 at 13:12
@Kappers you can construct MysqlCommand parameters instead of query itself. See this link for an answer stackoverflow.com/a/10191104/661933 — nawfal
– nawfal, Commented Jun 25, 2012 at 13:41

Community · Accepted Answer · 2017-05-23 10:34:26Z

const string QUERY = "INSERT INTO contacts (first_name,last_name) VALUES" + 
                      BuildQuery(c, contacts);

public string BuildQuery(MySQLCommand c, IEnumerable<contact> contacts)
{
    List<string> values = new List<string>();
    string query = null;
    int i = 0;
    foreach (var contact in contacts)
    {
       i++;
       query += "(@firstName" + i + ", @lastName" + i + ")";
       c.Parameters.AddWithValue("@firstName" + i, contact.first_name);
       c.Parameters.AddWithValue("@lastName" + i, contact.last_name);

       if(i < contacts.Count) 
          query += ",";
    }

    return query
}

You can see a relevant thread here!. I must have missed somethin trivial, but thats trivial for u to fix. Of course you know what happens when contacts has no elements. I dont see more edge cases. Btw, mind u there is a limit to how many such parameters you can add depending on mysql's max allowed packet size. You can change it, or take care of not exceeding that limit. Cheers! :)

Alex F · Accepted Answer · 2012-06-25 13:34:42Z

-1

You can escape your MySQL command arguments almost the same way as in normal SQL command. Here is example from official MySQL manual

 private void PrepareExample()
 {
     MySqlCommand cmd = new MySqlCommand("INSERT INTO mytable VALUES (?val)", myConnection);
     cmd.Parameters.Add( "?val", 10 );
     cmd.Prepare();
     cmd.ExecuteNonQuery();

     cmd.Parameters[0].Value = 20;
     cmd.ExecuteNonQuery();
 }

P.S. Whatever you choose - never user string manipulation to add/insert parameters to SQL command. This is main source for SQL-Injections attacks

answered Jun 25, 2012 at 13:34

Alex F

3,5592 gold badges35 silver badges53 bronze badges

1 Comment

nawfal Over a year ago

he is talking abt bulk insert!

Collectives™ on Stack Overflow

How do I create a safe query for performing a bulk insert in MySQL using MySQLCommand in C# without using a stored proc?

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related