How to create a secure query to perform bulk paste in MySQL using MySQLCommand in C # without using a stored procedure?

Question

In the following example, I create a query to make a large 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 more secure query that is not subject to SQL Injection?

Answer 1

 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 the relevant topic here ! I must have missed something trivial, but it is trivial to fix. Of course, you know what happens when contacts have no items. I no longer see cases with edges. Btw, of course, there is a limit to the number of parameters you can add, depending on the size of the allowed mysql max package. You can change it or take care not to exceed this limit. Hurrah!:)

Answer 2

You can avoid the arguments of the MySQL command in much the same way as in a regular SQL command. Here is an example from the 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(); } 

PS Whatever you choose, never manipulate user strings to add / insert parameters to an SQL command. This is the main source for SQL injection attacks.