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?

Windows doesn't use forward slashes for local file paths, for starters. — Tieson T., Feb 16 '13 at 16:54
Yes, Windows does use forward slashes (as well as backslashes). Try it from a command prompt using, for example, CD "C:/Program Files" — Matthew Watson, Feb 16 '13 at 16:55

Zoltán Tamási · Accepted Answer · 2013-02-16T17:12:51.557

6

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.

edited Feb 16 '13 at 17:12

answered Feb 16 '13 at 16:57

Zoltán Tamási

12,249
8
65
93

Try replacing this line, `string c = openFileDialog1.FileName;` with `string c = openFileDialog1.FileName.Replace(@"\", @"\\");`. – Jesse Feb 16 '13 at 17:02
Yes, for example there. But I meant when you form the insert query and add the "... + c + ..." concatenation. – Zoltán Tamási Feb 16 '13 at 17:03
4

Hopefully your filename doesn't have a single quotation mark in it. – JasonTrue Feb 16 '13 at 17:08

score 3 · Answer 2 · answered Feb 16 '13 at 17:03

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.

this thing fix my problem also, without using the c.Replace above :) — Jurel Jacinto, Feb 16 '13 at 17:30

score 0 · Answer 3 · edited May 23 '17 at 12:17

Why aren't you using a parameterized query? You can avoid most of the trouble with escaping strings, prevent a whole class of security risks, and gain a tiny bit of performance from query caching if you do.

Normally, it looks something like this:

cmd.CommandText = "INSERT INTO data (path) values(?path)";

cmd.Prepare();
cmd.Parameters.AddWithValue("?path", c);

It's been long enough since I've written any explicity C# query code (6 months or so) that I can't remember if this is exactly right, and I know the MySql provider uses a slightly different parameterization convention than MSSQL for named parameters (which uses @path instead of ?path), but that should get you on the right path. See C# MySqlParameter problem for a little more guidance that might be relevant to you.

Path Stored in database is not right

3 Answers3