How to check if a song exists?-CodePudding

I can Insert, Update, Delete and Search the MySql Database

I wish to know how to check if a Song Exists in MySql DataBase ...

void CheckIfFileExists(String file, String dir)
{
    //   $mysqli = new mysqli(SERVER, DBUSER, DBPASS, DATABASE);
    string song = Path.GetFileName(file);
            song = MySql.Data.MySqlClient.MySqlHelper.DoubleQuoteString(song);
    string ConString = " datasource = localhost; port = *; 
                         username = ***; password = *****";
     string sql = "SELECT id FROM music WHERE song = "   song);
 
   
    using (MySqlConnection cn = new MySqlConnection(ConString))
    {
        cn.Open();
        using (MySqlCommand cmd = new MySqlCommand(sql, cn))
        {
             //if id exixts?
            if (???????????)
            {
               // "Song exists";
            }
            else
            {
              // "Song does not exist";
              InsertIntoDataBase(String file, String dir);
            } 
        }
    }

}

CodePudding user response：

I'll go straight to the real issue, how to upsert correctly, before the question gets closed. Use MySQL's custom INSERT IGNORE and parameterized queries. Using Dapper makes this a one-liner:

var sql=@"INSERT IGNORE INTO music
    (ID,song,folder)
VALUES (1, @song,@folder)";

using (var cn= new MySqlConnection(ConString))
{
    cn.Execute(sql,new {song=song,folder=@dir});
}

Dapper will open the connection to execute the command and close it afterwards. It will generate a MySqlCommand, generate parameters for every property in the supplied object (@song for song, @folder for folder).

INSERT IGNORE will try to insert a new row to the table. If a row with the same primary key already exists, the INSERT will be ignored.

Generating SQL strings by concatenating input is very dangerous. No amount of quoting can prevent SQL injection attacks, or more benign conversion errors. What would happen if the song was named '0'; DROP TABLE music;-- ? What if the value was a date or number? The query string would end up with invalid characters.

Parameterized queries on the other hand are like C# functions. The parameters are passed to the server outside the string itself, as part of the RPC call. The server generates an execution plan from the query and feeds the parameter values to the compiled execution plan.

The values never change to text or get combined with the query, so there's no way to inject SQL or end up with type conversion issues