Path Stored in database is not right

Question

private void button14_Click(object sender, EventArgs e)
        {
            if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                    string c = openFileDialog1.FileName;

                    string connString = "Server=Localhost;Database=test;Uid=root;password=root;";
                    MySqlConnection conn = new MySqlConnection(connString);
                    MySqlCommand command = conn.CreateCommand();
                    command.CommandText = ("Insert into data (path) values('" + c + "')");
                    conn.Open();
                    command.ExecuteNonQuery();
                    conn.Close();
                    MessageBox.Show("Success");
                }
            }

This code works for me, but unfortunately, the path stored in database is not right .. the stored path is like this (C:Users hesisDesktopREDEFENSEResourcesImagesRED1f.png) where it supposed to be like this (C:P/Users/thesis/Desktop..../1f.png).

But when I checked the "sr" value with this code.. the msgbox show just right..

private void button14_Click(object sender, EventArgs e)
{
    if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
    {         
        MessageBox.Show(openFileDialog1.FileName);
    }
}

why is it happening then?

Answer 1

Perhaps MySQL thinks that the "\" character is an escape, so that's why it does not contain it in the string. Try

c.Replace(@"\", @"\\")

when you insert, so the escape character will be escaped.

EDIT: For example, replace the command text initializing line like this. Also add an escape for single quotes.

string escapedPath = c.Replace(@"\", @"\\").Replace("'", @"\'");    
command.CommandText = ("Insert into data (path) values('" + escapedPath + "')");

EDIT: See @Matthew's answer for an even more "best practice" solution, using parameterized queries.

Answer 2

This is due to the way you're writing your query. In MySQL, the backslash character \ (which is present in file paths) has special meaning, which is to escape the next character. You need to encode these, many different DBMS's have patterns to do this.

Other than that, your code is susceptible to SQL injection.

To fix both these problems, you can use parametrized queries.

public void InsertPath(string path)
{
    string connString = "Server=Localhost;Database=test;Uid=root;password=root;";

    using (var connection = new MySqlConnection(connString))
    {
        connection.Open();

        using (var command = connection.CreateCommand())
        {
            command.CommandText = "INSERT INTO data(path) VALUES(?path)";

            command.Parameters.AddWithValue("?path", path);

            command.ExecuteNonQuery();
        }
    }
}

This answer might not be 100% accurate, because I don't have MySQL on my computer, but hopefully if it doesn't work, it should at least give you some information about how to approach this problem.