c# SQLITE logic error ' escape sequence

Question

Good morning all been working on a big music project and come across an annoying error when coming to retrieving data from a database,

I noticed with some songs - for example, the one throwing an error is,

30 seconds to mars - I'll attack -

now I know ' is used in SQL in general what I am not sure on how to do is to get it to ignore the ' in the name,

        #region update_existing_db
        public void updateDB(string id, string arti, string titl, string bpmt, string time, string genr, string comm, string albm, string trck, string fname, string bpm, string duration_ms, string pitch_key, string eql, string eqm, string eqh, string currentcue, string cue_in, string cue_out, string size, string mediatype, string isnew, string isexplicit, string playCount, string bitRate, string bitDepth, string sr, string ch, string dtAdded, string dtPlayed)
        {
            string getdb = "select * from Songs where ID = '" + id + "'";
            SQLiteCommand elfeninfo = new SQLiteCommand(getdb, YunoConnect);
            OpenConnection();
            SQLiteDataReader result = elfeninfo.ExecuteReader();

            if(result.HasRows)
            {
                while(result.Read())
                {
                    //var _id = result["ID"];
                    ifnull(elfendataset.ID = result["ID"].ToString());
                    ifnull(elfendataset.Artist = result["arti"].ToString());
                    ifnull(elfendataset.Title = result["titl"].ToString());
                    ifnull(elfendataset.BPMT = result["bpmt"].ToString());
                    ifnull(elfendataset.TIME = result["time"].ToString());
                    ifnull(elfendataset.GENRE = result["genr"].ToString());
                    ifnull(elfendataset.COMM = result["comm"].ToString());
                    ifnull(elfendataset.ALBUM = result["albm"].ToString());
                    ifnull(elfendataset.TRACK = result["trck"].ToString());
                    ifnull(elfendataset.COL_Filename = result["fnam"].ToString());
                    ifnull(elfendataset.BPM = result["bpm"].ToString());
                    ifnull(elfendataset.DURATION_MS = result["duration_ms"].ToString());
                    ifnull(elfendataset.PITCH_KEY = result["pitch_key"].ToString());
                    ifnull(elfendataset.EQL = result["eql"].ToString());
                    ifnull(elfendataset.EQM = result["eqm"].ToString());
                    ifnull(elfendataset.EQH = result["eqh"].ToString());
                    ifnull(elfendataset.CURRENTCUE = result["currentcue"].ToString());
                    ifnull(elfendataset.CUE_IN = result["cue_in"].ToString());
                    ifnull(elfendataset.CUE_OUT = result["cue_out"].ToString());
                    ifnull(elfendataset.SIZE = result["size"].ToString());
                    ifnull(elfendataset.MEDIATYPE = result["mediatype"].ToString());
                    ifnull(elfendataset.ISNEW = result["isnew"].ToString());
                    ifnull(elfendataset.EXPLICIT = result["explicit"].ToString());
                    ifnull(elfendataset.PLAYCOUNT = result["playCount"].ToString());
                    ifnull(elfendataset.BITRATE = result["bitRate"].ToString());
                    ifnull(elfendataset.BITDEPTH = result["bitDepth"].ToString());
                    ifnull(elfendataset.SR = result["sr"].ToString());
                    ifnull(elfendataset.CH = result["ch"].ToString());
                    ifnull(elfendataset.DTADDED = result["dtAdded"].ToString());
                    ifnull(elfendataset.PLAYCOUNT = result["dtPlayed"].ToString());
                    XtraMessageBox.Show(elfendataset.ID + Environment.NewLine + elfendataset.Artist.ToString() + Environment.NewLine + elfendataset.Title + Environment.NewLine + elfendataset.BPMT + Environment.NewLine + elfendataset.TIME + Environment.NewLine + elfendataset.GENRE + Environment.NewLine + elfendataset.COMM + Environment.NewLine + elfendataset.ALBUM + Environment.NewLine + elfendataset.TRACK + Environment.NewLine + elfendataset.COL_Filename + Environment.NewLine +
                       elfendataset.BPM +Environment.NewLine + elfendataset.DURATION_MS + Environment.NewLine + elfendataset.PITCH_KEY + Environment.NewLine + elfendataset.EQL + Environment.NewLine + elfendataset.EQM + 
                       elfendataset.EQH + Environment.NewLine + elfendataset.CURRENTCUE + Environment.NewLine + elfendataset.CUE_IN + Environment.NewLine + elfendataset.CUE_OUT + Environment.NewLine + elfendataset.SIZE + 
                       elfendataset.MEDIATYPE + Environment.NewLine + elfendataset.ISNEW + Environment.NewLine + elfendataset.EXPLICIT + Environment.NewLine + elfendataset.PLAYCOUNT + Environment.NewLine + elfendataset.BITRATE + 
                       Environment.NewLine + elfendataset.SR + Environment.NewLine + elfendataset.CH + Environment.NewLine + elfendataset.DTADDED + Environment.NewLine + elfendataset.DTPLAYED,"Results : ID" + id);
                }
            }
            CloseConnection();
            updatevalues(elfendataset.ID);




            //string query = "Update Songs (`ID`, `arti`, `titl`, `bpmt`, `time`,`comm`, `albm`, `trck`, `fnam`, `bpm`,`duration_ms`, `pitch_key`, `eql`, `eqm`, `eqh`,`currentcue`, `cue_in`, `cue_out`, `size`, `mediatype`,`isnew`, `explicit`, `playCount`, `bitRate`, `bitDepth`,`sr`, `ch`, `dtAdded`, `dtPlayed`) where ID= '"+id+'"';
            //SQLiteCommand updateinfo = new SQLiteCommand(query, YunoConnect);
        }
        #endregion




        public void updatevalues(string id)
        {
            string Qeury = "UPDATE Songs SET arti = '" +elfendataset.Artist+ "', titl= '" + elfendataset.Title + "', bpmt= '" + elfendataset.BPMT + "', time= '" + elfendataset.TIME + "', genr= '"+elfendataset.GENRE  +"' WHERE ID= '" + id + "'";
            SQLiteCommand setupusername = new SQLiteCommand(Qeury, YunoConnect);
            OpenConnection();
            var result = setupusername.ExecuteNonQuery();
            CloseConnection();
        }

and skip over it like it's not there,

is that possible in SQL I assume this is quite a common issue?

Answer 1

To avoid apostrophes and other syntax from sneaking into your queries and potentially causing security problems it's best to use query parameters rather than concatenating values directly into the query string. Parameters are automatically sanitised:

public void updatevalues(string id) {
    SQLiteCommand command = YunoConnect.CreateCommand();
    command.CommandText = "UPDATE Songs SET arti = @artist, titl = @title, bpmt = @bpmt, time = @time, genr = @genre WHERE ID = @id";
    command.Parameters.Add(new SQLiteParameter("@artist", elfendataset.Artist));
    command.Parameters.Add(new SQLiteParameter("@title", elfendataset.Title));
    command.Parameters.Add(new SQLiteParameter("@bpmt", elfendataset.BPMT));
    command.Parameters.Add(new SQLiteParameter("@time", elfendataset.TIME));
    command.Parameters.Add(new SQLiteParameter("@genre", elfendataset.GENRE));
    command.Parameters.Add(new SQLiteParameter("@id", id));

    OpenConnection();
    var result = command.ExecuteNonQuery();
    CloseConnection();
}