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?
source to share
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.
source to share
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.
source to share
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.
source to share