Cannot delete apostrophe from string to make MySQL query work in C#

Question

I'm working on a project, where I use metadata of mp3 songs in a string class. These are the data (i.e. artist, title, etc.) I want to copy into a mySQL database (from a list I made from the data) for which I have the next query:

SELECT * FROM database.tablename WHERE artist='"+song[i].artist+ "' AND title='" + song[i].title + "' AND genre='"+song[i].genre+"';

Code works perfectly (knowing that 12 lines copied successfully) until the for loop reaches a song what contains an apostrophe ('), it exits with syntax error at the given symbol. For example: song[i].title is I'm not her. I tried everything i could imagine:

1. I tried replace ' to string.empty
2. Tried to replace to ""
3. Even tried to replace to "''" (2 apostrophes) so the query can recognize an apostrophe but nothing seems to help (always got the same syntax error, like when replace even wasn't there)

Here is a line, so you can see how I tried: song[i].artist = song[i].artist.Replace("'","");

So my question is: Is there any solution to get rid of the apostrophe, or any way to make the query work?

EDIT: Here is my original code (only the for loop) for better understanding.

for (int i = 0; i < Array.Length; i++)
            {
                Command = @"SELECT * FROM database.tablename WHERE artist='"+song[i].artist+ "' AND title='" + song[i].title + "' AND genre='"+song[i].genre+"';";
                MySqlCommand myCommand = new MySqlCommand(Command);
                using (MySqlConnection mConnection = new MySqlConnection(ConnectionString.ToString()))
                {
                    adapter = new MySqlDataAdapter(Command,mConnection);
                    mConnection.Open();
                    adapter.Fill(dataset);
                    int adat = dataset.Tables[0].Rows.Count;
                    if (adat <= 0)       //if current data does not exist
                    {
                        song[i].artist = song[i].artist.Replace("'","\'");
                        song[i].title = song[i].title.Replace("'", "\'");
                        song[i].genre = song[i].genre.Replace("'", "\'");
                        myCommand = new MySqlCommand("INSERT INTO database.tablename (artist,title,length,genre) VALUES ('"+song[i].artist+"','"+song[i].title+"','"+song[i].length+"','"+song[i].genre+"');",mConnection);
                        myCommand.ExecuteNonQuery();
                        dataset.Clear();
                    }
                    mConnection.Close();
                }
            }

And here is what I'm "using" (sorry, don't know how to make it look proper):

using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; using System.IO; using TagLib; using TagLib.Id3v2; using System.Configuration; using System.Data.SqlClient; using MySql; using MySql.Data; using MySql.Data.MySqlClient; using Programname.Properties;

Answer 1

Just to add in a data provider agnostic solution into the mix, this is an approach you could take which decouples the code from MySql.

The ConnectionString can be set in the app/web.config:

<connectionStrings>
  <add name="Example" providerName="MySql.Data.MySqlClient" connectionString="Data Source=1.1.1.1"/>
</connectionStrings>

And the code would use the System.Data.Common classes:

var connectionSettings = ConfigurationManager.ConnectionStrings["Example"];
var dbFactory = DbProviderFactories.GetFactory(connectionSettings.ProviderName);

using (DbConnection connection = dbFactory.CreateConnection(connectionSettings.ConnectionString))
using (DbCommand countCommand = connection.CreateCommand())
{
    string sql = @"
SELECT COUNT(*) 
FROM database.tablename 
WHERE artist=@artist AND title=@title 
AND genre=@genre";

    countCommand.CommandText = sql;
    countCommand.Parameters.Add(dbFactory.GetParameter("@artist", null));
    countCommand.Parameters.Add(dbFactory.GetParameter("@title", null));
    countCommand.Parameters.Add(dbFactory.GetParameter("@genre", null));

    for (int i = 0; i < songs.Length; i++)
    {
        var song = songs[i];

        countCommand.Parameters["@artist"].Value = song.artist;
        countCommand.Parameters["@title"].Value = song.title;
        countCommand.Parameters["@genre"].Value = song.genre;

        int matches = (int)countCommand.ExecuteScalar();
        if (matches == 0)
            continue;

        using (DbCommand insertCommand = connection.CreateCommand())
        {
            string insertSql = @"
INSERT INTO database.tablename(artist, title, length, genre) 
VALUES(@artist, @title, @length, @genre";

            insertCommand.CommandText = insertSql;
            insertCommand.Parameters.Add(dbFactory.GetParameter("@artist", song.artist));
            insertCommand.Parameters.Add(dbFactory.GetParameter("@title", song.title));
            insertCommand.Parameters.Add(dbFactory.GetParameter("@length", song.length));
            insertCommand.Parameters.Add(dbFactory.GetParameter("@genre", song.genre));

            int result = insertCommand.ExecuteNonQuery();
        }
    }
}

And you can create custom extensions that offer slightly better options than what comes out of the DbProviderFactory:

public static class FactoryExtensions
{
    public static DbParameter GetParameter(this DbProviderFactory factory, string name, object value)
    {
        var param = factory.CreateParameter();
        param.Value = value ?? DBNull.Value;
        param.Name = name;
        return param;
    }

    public static DbConnection CreateConnection(this DbProviderFactory factory, string connectionString, bool open = true)
    {
        DbConnection connection = factory.CreateConnection();
        connection.ConnectionString = connectionString;

        if (open)
            connection.Open();

        return connection;
    }
}

Obviously your code is more complex than the example you provided, but this is a starting point.

Answer 2

As @Chad mentioned, parameters are the way to go.

With the standard System.Data classes, you'd probably be looking at something like this:

// set up command using @parameters
using (SqlCommand cmd = new SqlCommand("SELECT * FROM database.tablename WHERE artist=@artist AND title=@title AND genre=@genre", connection))
{
    // add parameter values to command
    cmd.Parameters.Add(new SqlParameter("@artist", song[i].artist));
    cmd.Parameters.Add(new SqlParameter("@title", song[i].title));
    cmd.Parameters.Add(new SqlParameter("@genre", song[i].genre));

    SqlDataReader reader = cmd.ExecuteReader();

    // do useful stuff here
}

I found (and then butchered) a MySQL Connector/NET tutorial from here that might be a bit more MySQL-specific:

string connStr = "*** your connection string here ***";
MySqlConnection conn = new MySqlConnection(connStr);
conn.Open();

string sql = "SELECT * FROM database.tablename WHERE artist=@artist AND title=@title AND genre=@genre";
MySqlCommand cmd = new MySqlCommand(sql, conn);

cmd.Parameters.AddWithValue("@artist", song[i].artist);
cmd.Parameters.AddWithValue("@title", song[i].title);
cmd.Parameters.AddWithValue("@genre", song[i].genre);

MySqlDataReader rdr = cmd.ExecuteReader();

// do useful stuff here

rdr.Close();
conn.Close();