Path is not stored in the database correctly
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 the database is not correct. The stored path is like this ( C:Users hesisDesktopREDEFENSEResourcesImagesRED1f.png
) where it should be like this ( C:P/Users/thesis/Desktop..../1f.png
).
But when I checked the "sr" value with this code, 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 this happening?
Perhaps MySQL thinks the "\" is an escape character, so it doesn't include it in the string. Try
c.Replace(@"\", @"\\")
when you insert, so the escape character will be escaped.
EDIT: For example, replace your command text initialization string with the following. Also add 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 better solution using parameterized queries.
It has to do with how you write your request. In MySQL, the backslash character \
(which appears in file paths) has a special meaning to avoid the next character. You need to code them, many different DBMS have templates for this.
Besides, your code is susceptible to SQL injection .
To fix both of these problems, you can use parameterized 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 may 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 on how to approach this issue.
Why aren't you using a parameterized query? You can avoid most string escaping problems, prevent a whole class of security risks, and get tiny performance from query caching if you do.
It usually 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 wrote any C # explication query code (6 months or so) that I can't remember if that's accurate, and I know the MySql provider uses a slightly different parameterization convention than MSSQL for named parameters (which uses @path instead of?), but that should lead you to the correct path. See C # MySqlParameter Problem for more detailed guidance that may be relevant to you.