Escaping commas, colons and single quotes with mySQL in C#

--- THIS IS FOR PERSONAL USE, SO DON'T WORRY ABOUT SQL INJECTION ---

I've been browsing through several tutorials on mySQL escaping for C# but cannot find one that works for me (maybe I'm just using it incorrectly)

I'm trying to insert data into a mySQL database.

Here's the code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Diagnostics;
using System.Net;
using System.IO;
using System.Security.Cryptography;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace HASHSITE
{
    class Program
    {
        static void Main(string[] args)
        {
            bool success;
            int done = 0;

            string path = @"C:\Users\somePC\Documents\someFolder\somefile.txt";

            string server = "someIP";
            string database = "some_db";
            string uid = "some_dbu";
            string password = "pass";
            string connectionstring = "SERVER=" + server + ";DATABASE=" + database + ";UID=" + uid + ";PASSWORD=" + password + ";";

            using (var connection = new MySqlConnection(connectionstring))
            {
                connection.Open();
                using (var cmd = new MySqlCommand("INSERT INTO databases(data) VALUES(@name)", connection))
                {
                    var parameter = cmd.Parameters.Add("@name", MySqlDbType.LongText);
                    foreach(string line in File.ReadLines(path))
                    {
                        success = false;
                        while (!success)
                        {
                            parameter.Value = line;
                            cmd.ExecuteNonQuery(); //ERROR IS HERE
                            success = true;
                        }
                        done += 1;
                        Console.WriteLine("\n" + done);
                    }
                }
            }
        }
    }
}

I need to escape commas present in the string line which is

name,name@name.com

ERROR:

Additional information: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'databases(data) VALUES

Jon Skeet
people
quotationmark

Don't try to escape anything - just use parameterized SQL. You may not care about SQL injection attacks now, but you will at some point... and by using parameterized SQL for your values, you'll remove both the escaping and injection attack issues. Oh, and you'll reduce type conversion concerns. And make your SQL more readable. It's a win all round.

Note that DATABASES is a reserved word, so you do need to quote that - or change the name of your table to something which isn't going to be as awkward.

// TODO: Specify the column name as well, for clarity if nothing else
cmd.CommandText = "INSERT INTO 'databases' VALUES(@name)";
// Adjust for your actual type
cmd.Parameters.Add("@name", MySqlDbType.LongText).Value = line;
...

Note that you only want to call cmd.Parameters.Add once though, so in your case with a loop you'd probably want to call that (and set the command text) outside the loop, then just set the Value property inside the loop.

When in doubt, get in the habit of doing the right thing early, rather than assuming you'll find the time to undo your bad habits later on.

While you're at it, now would be a good time to start using using statements... and you can reuse pretty much everything, just changing the parameter value on each iteration:

using (var connection = new MySqlConnection(...))
{
    connection.Open();
    using (var command = new MySqlCommand("INSERT INTO 'databases' VALUES(@name)", connection)
    {
        var parameter = command.Parameters.Add("@name", MySqlDbType.LongText);
        foreach (...)
        {
            parameter.Value = line;
            command.ExecuteNonQuery();
        }
    }
}

people

See more on this question at Stackoverflow