Is it safe to not parameterize an MySQL query, and instead use the method MySqlHelper.EscapeString(string) when very large inserts are made?

Question

For example:

StringBuilder sCommand = new StringBuilder("INSERT INTO User (FirstName, LastName) VALUES ");
            using (MySqlConnection mConnection = new MySqlConnection(ConnectionString))
            {
                List<string> Rows = new List<string>();
                for (int i = 0; i < 100000; i++)
                {
                    Rows.Add(string.Format("('{0}','{1}')", MySqlHelper.EscapeString("test"), MySqlHelper.EscapeString("test")));
                }
                sCommand.Append(string.Join(",", Rows));
                sCommand.Append(";");
                mConnection.Open();
                using (MySqlCommand myCmd = new MySqlCommand(sCommand.ToString(), mConnection))
                {
                    myCmd.CommandType = CommandType.Text;
                    myCmd.ExecuteNonQuery();
                }
            }

I'd like to know if it's just as safe not to parameterize a MySQL query and, instead, to use MySqlHelper.EscapeString when mass inserts are made.

Answer 1

You cannot use the method whatever.EscapeString(string) instead of parameters. Simply because these two options are not equal. They are not even related. Despite the common belief, whatever escaping is not intended for protection and never has been. It has a very specific purpose and only accidentally may prevent an SQL injection. Whereas a parameterized query, when used properly, would guarantee the protection.

That said, using parameters will be faster for multiple inserts when you Prepare() the query once and then only execute() that prepared query in a loop.

I would also recommend to wrap your inserts in a transaction, it may speed the thing up significantly.