Wierd double quote being generated by C# going into SQL syntax

Question

Could anyone point out what I'm doing wrong, I'm getting an error back in SQL saying Syntax error near '' at line 1

I have tried everything and can't seem to get rid of it ?

This is the code I'm Using -

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {

        MySqlConnection conn;
        MySqlConnection conn1;
        bool connection = false;

        public Form1()
        {
            InitializeComponent();
        }


        private void Form1_Load(object sender, EventArgs e)
        {

            this.toolStripStatusLabel1.Text = "Initialising";
            db_connect();

        }





        private void db_connect()
        {

            string mydbconn = "server=localhost;user id=root;password=lap;database=test;";
            string mydbconn1 = "server=localhost;user id=root;password=lap;database=test;";


            try
            {

                conn = new MySqlConnection(mydbconn);
                conn1 = new MySqlConnection(mydbconn1);

                conn.Open();
                conn1.Open();
                this.toolStripStatusLabel1.Text = "Connected";
                connection = true;
                if (connection == true)
                {
                    read_data();
                }

            }

            catch (Exception ex)
            {
                this.toolStripStatusLabel1.Text = "WRONG";
                connection = false;
            }
        }





        private void read_data()
        {


            string sql = "SELECT first_name, last_name FROM dan";

            MySqlCommand cmd = new MySqlCommand(sql, conn);

            MySqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {

                string newsql = "REPLACE INTO dan1 (first_name, last_name) values";

                newsql += "(";

                for (int i = 0; i < 2; i++)
                {

                    newsql += reader.GetString(i);

                }

               // System.Console.WriteLine(newsql);


                int res = 0;
                MySqlCommand cmd1 = new MySqlCommand(newsql, conn1);

                try
                {


                    res = cmd1.ExecuteNonQuery();
                    this.richTextBox1.Text = "copying";

                }

                catch (MySqlException ex)
                {

                    this.richTextBox1.Text = ex.Message;

                }

            }


        }
    }
}

Answer 1

You are creating a query that looks like this:

REPLACE INTO dan1 (first_name, last_name) values(JohnDoe

when it should look like this;

REPLACE INTO dan1 (first_name, last_name) values ('John', 'Doe')

To create SQL dynamically like that you need to escape all the string data properly, and the way that it's done correctly depends on the database you are using. For MySQL it would be:

string newsql = "REPLACE INTO dan1 (first_name, last_name) values (";
bool first = true;
for (int i = 0; i < 2; i++) {
  if (first) {
    first = false;
  } else {
    newsql += ",";
  }
  newsql += "'" + reader.GetString(i).Replace("\\", "\\\\").Replace("'", "\\'") + "'";
}
newsql += ")";

However, you would rather use a parameterised query instead, then you don't have to worry about formatting the query and escaping characters correctly:

string newsql = "REPLACE INTO dan1 (first_name, last_name) values (@FirstName, @LastName)";

MySqlCommand cmd1 = new MySqlCommand(newsql, conn1);
cmd1.Parameters.Add("@FirstName", reader.GetString(0));
cmd1.Parameters.Add("@LastName", reader.GetString(1));

Answer 2

At a minimum I think that your query might be missing a closing paren. Your code seems to generate queries that look like this:

REPLACE INTO dan1 (first_name, last_name) values (first_name last_name

which is missing the closing ). I also do not see commas being added to separate the items in the values clause, and probably the items themselves are not quoted. Try this:

string newsql = "REPLACE INTO dan1 (first_name, last_name) values";
newsql += "(";
for (int i = 0; i < 2; i++)
{
    newsql += reader.GetString(i);
}
newsql += ")";
// System.Console.WriteLine(newsql);