-1

I try to insert pathname to mysql database, but its working but its strange. What I found is the path isnt the path of my project directory its something strange. when i use my program the result is "D:musicFOREIGN!!New folderarat cakepHilary Duff - Wake Up.mp3". and when I insert the query manually from phpmyadmin, its result "D:\music\FOREIGN!!\New folder\barat cakep\Hilary Duff - Wake Up.mp3", nothing strange. Am I did something wrong? here is my code:

public static void add_song(song input)
    {
        establised();
        try
        {
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = string.Format("INSERT INTO song (ID_SONG, ID_GENRE, ID_CATEGORY, SONG_TITLE, SONG_ARTIST, SONG_LOCATION, SONG_PLAYED) select '', b.id_genre, c.id_category, '{0}', '{1}', '{2}', '0' from genre b, category c where b.genre = '{3}' and c.category = '{4}' ", input.title, input.artist, input.location, input.genre, input.category);
            command.ExecuteNonQuery();
        }
        catch (Exception e) { }
        release();
    }

and here is my example query:

"INSERT INTO song (ID_SONG, ID_GENRE, ID_CATEGORY, SONG_TITLE, SONG_ARTIST, SONG_LOCATION, SONG_PLAYED) select '', b.id_genre, c.id_category, 'Wake Up', 'Hilary Duff', 'D:\\music\\FOREIGN!!\\New folder\\barat cakep\\Hilary Duff - Wake Up.mp3', '0' from genre b, category c where b.genre = 'Pop' and c.category = 'international'"
skaffman
  • 398,947
  • 96
  • 818
  • 769
Azka Tiantoro
  • 1
  • 1

2 Answers2

3

Use a MysqlParameter object to pass the parameter to the query, so is automatically checked:

// 1. Use parameters label in the query
command.CommandText = "INSERT INTO song (ID_SONG, ID_GENRE, ID_CATEGORY, SONG_TITLE, SONG_ARTIST, SONG_LOCATION, SONG_PLAYED) select '', b.id_genre, c.id_category, @Title, @Artist, @Location, '0' from genre b, category c where b.genre = @GenRe and c.category = @category "

// 2. Define parameters used in command object
    MySqlParameter param  = new MySqlParameter();
    param.ParameterName = "@Location";
    param.Value         = input.location;

//3. Assign the parameter to the command
command.Parameters.Add(param);

//GO ahead with others parameters ...
aleroot
  • 71,077
  • 30
  • 176
  • 213
0

You merely need to escape any text variable present in your query string.

The two characters that needs to be escaped are the apostrophe (') and slash (\).

Create a simple "FixSQL" function

Public Shared Function FixSQL(item As String) As String
    Dim result As String

    result = item
    result = Replace(result, "\", "\\")
    result = Replace(result, "'", "''")
    return result
End Function

Apply the function only to the variables that are in your sql query

glenn
  • 31
  • 1