Syntax error while doing C# SQL data insert

Question

string query = $"insert into MessageData values(NULL,'{rehberIsmi}','{jsonData}','{DateTime.Now}')";

using (SQLiteConnection conn = new SQLiteConnection(DatabaseInformation.ConnectionString))
{
    try
    {
        conn.Open();

        SQLiteCommand com = new SQLiteCommand(query, conn);
        com.ExecuteNonQuery();

        conn.Close();
    }
    catch (Exception ex)
    {
       MessageBox.Show("Bir hata meydana geldi : " + ex.Message, "Hata", 
       MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

I am using the above code for data entry into SQLite database, but while getting some string values, I am getting this error:

'SQL logic error near "da": syntax error'

What is the reason ?

Wrong json value : "[
{"avatar":"","position":"right","time":"2021.02.12 12:43:54","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"+905416379800","msg":"Merhabalar Sizlere nasıl yardımcı olabilirim."},
{"avatar":"","position":"left","time":"2021.02.12 12:43:55","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"Merhaba"},{"avatar":"","position":"left","time":"2021.02.12 12:44:03","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"Senet ile satışınız varmı"},{"avatar":"","position":"left","time":"2021.02.12 12:44:16","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"Kanunu mati 125 için"},{"avatar":"","position":"left","time":"2021.02.12 12:44:20","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"Bilgi almak istiyorum"},{"avatar":"","position":"left","time":"2021.02.12 12:44:29","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"10 bin peşin versek diğerini"},{"avatar":"","position":"left","time":"2021.02.12 12:44:37","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"12 aya bölsek nasıl olur"},{"avatar":"","position":"right","time":"2021.02.12 13:39:26","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"+905416379800","msg":"https://www.ekuralkan.com/mati-42313 peşin fiyatı 22.353 TL dir. Tüm modellerimizi minimum %25 peşinat ile 36 aya varan taksitler ile alabilirsiniz. https://www.ekuralkan.com/senetli-satis-basvuru\nhttps://blog.ekuralkan.com/\nhttps://www.youtube.com/channel/UCKRIe5B_60bNIR6uSkhxR1g"},{"avatar":"","position":"right","time":"2021.02.12 13:39:36","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"+905416379800","msg":"Sitemiz de vade oranlarını inceleyebilirsiniz"},{"avatar":"","position":"left","time":"2021.12.12 23:52:20","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"Bağlantı:\nhttps://www.instagram.com/p/CXTcf_-jRQM/\n\nBunu Instagram'da gördüm..."},{"avatar":"","position":"right","time":"2021.12.12 23:52:20","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"Mesajınız için teşekkür ederiz. Mesai saatlerimiz hafta içi 07:00 & 17:00’dir. Mesai saatleri içinde en kısa sürede size cevap vereceğiz."},{"avatar":"","position":"right","time":"1971.01.01 00:00:00","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"https://www.ekuralkan.com/mati-42313 peşin fiyatı 22.353 TL dir. Tüm modellerimizi minimum %25 peşinat ile 36 aya varan taksitler ile alabilirsiniz. https://www.ekuralkan.com/senetli-satis-basvuru\nhttps://blog.ekuralkan.com/\nhttps://www.youtube.com/channel/UCKRIe5B_60bNIR6uSkhxR1g"},{"avatar":"","position":"left","time":"1971.01.01 00:00:00","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"Kolay gelsin"},{"avatar":"","position":"left","time":"1971.01.01 00:00:00","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"Senentle satış varmı ?"},{"avatar":"","position":"right","time":"1971.01.01 00:00:00","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"Findeks üyeliğiniz varsa senetli alım başvurunuzu yapabiliriz"},{"avatar":"","position":"left","time":"1971.01.01 00:00:00","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"Nasıl oluyor o"},{"avatar":"","position":"right","time":"1971.01.01 00:00:00","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"kredi notudur"},{"avatar":"","position":"right","time":"1971.01.01 00:00:00","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"internetten kendiniz üye olmanız gerekiyor"},{"avatar":"","position":"left","time":"1971.01.01 00:00:00","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"Onu biliyorum"},{"avatar":"","position":"left","time":"1971.01.01 00:00:00","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"Findex üyeliğimiz var zaten de"},{"avatar":"","position":"left","time":"1971.01.01 00:00:00","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"Başvuruyu nasıl yapıyoruz"},{"avatar":"","position":"right","time":"1971.01.01 00:00:00","mediaSize":"37.65 KB","mediaName":"attachment_743","mediaTime":"0\"","localPath":"media\\attachment_743","thumbPath":"media\\attachment_743","delete":"false","type":"photo","identifier":"","msg":"IMG-20211213-WA0001.jpg"},{"avatar":"","position":"right","time":"1971.01.01 00:00:00","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"başvurunuzu buradan yapabiliriz siteden kendiniz de yapabilirsiniz"},{"avatar":"","position":"left","time":"1971.01.01 00:00:00","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"Link alabilirmiyim"},{"avatar":"","position":"left","time":"1971.01.01 00:00:00","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"Bi türlü bulamadım"},{"avatar":"","position":"right","time":"1971.01.01 00:00:00","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"https://isube.findeks.com/ers/uyelik/bireyselUyelik.xhtml"},{"avatar":"","position":"left","time":"1971.01.01 00:00:00","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"Üyeliğim var zaten"},{"avatar":"","position":"right","time":"1971.01.01 00:00:00","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"https://www.ekuralkan.com/senetli-satis-basvuru"},{"avatar":"","position":"right","time":"1971.01.01 00:00:00","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"sistemden senetli alım başvurunuzu yapabilirsiniz"},{"avatar":"","position":"left","time":"1971.01.01 00:00:00","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"Başvuru yaptım"},{"avatar":"","position":"left","time":"1971.01.01 00:00:00","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"Size ulaştımı başvuru"},{"avatar":"","position":"right","time":"1971.01.01 00:00:00","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"onaylandı mı süreciniz"},{"avatar":"","position":"left","time":"1971.01.01 00:00:00","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"Başvuru beklemede"},{"avatar":"","position":"left","time":"1971.01.01 00:00:00","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"Yazdım"},{"avatar":"","position":"left","time":"1971.01.01 00:00:00","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"Yazdı anlamadım bende"},{"avatar":"","position":"right","time":"1971.01.01 00:00:00","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"red olarak sonuçlanmıştır"},{"avatar":"","position":"right","time":"1971.01.01 00:00:00","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"ekran çıkmadı ise onaylanmamıştır"},{"avatar":"","position":"left","time":"1971.01.01 00:00:00","mediaSize":"0 Byte","mediaName":"","mediaTime":"0\"","localPath":"","thumbPath":"","delete":"false","type":"text","identifier":"","msg":"Anladım"}]"

This is _exactly_ the reason why you don't do string concatenation to create an sql command. Your input most likely has single quotes in it, and it's screwing up the command syntax. Use parameterized statements. https://bobby-tables.com/ — gunr2171, Mar 01 '22 at 16:08
Please provide the values of `rehberIsmi`, `jsonData`, and your current DateTime culture to prove this. — gunr2171, Mar 01 '22 at 16:10
The errors will probably vanish if you use [command parameters in SQLite](https://stackoverflow.com/questions/809246/adding-parameters-in-sqlite-with-c-sharp). And remove the quotes in the SQL command when using parameters. — Olivier Jacot-Descombes, Mar 01 '22 at 16:11

ByungYong Kang · Accepted Answer · 2022-03-01T17:43:00.787

0

You will get SQL syntax error if one of rehberIsmi, jsonData contains single quote.

In your case, composing SQL query by concatenating strings is not a good idea. please use parameters

command.CommandText = @"insert into MessageData values(NULL,$rehberIsmi,$jsonData,$datetime)";
command.Parameters.AddWithValue("$rehberIsmi", rehberIsmi);
command.Parameters.AddWithValue("$jsonData", jsonData);
command.Parameters.AddWithValue("$datetime", DateTime.Now);

see details here https://learn.microsoft.com/en-us/dotnet/standard/data/sqlite/parameters

edited Mar 01 '22 at 17:43

answered Mar 01 '22 at 17:08

ByungYong Kang

171
6

1

This is wrong. The @parameters must not be enclosed in quotes! `insert into MessageData values(NULL, $rehberIsmi, $jsonData, $datetime)`. The arguments are not inserted as sting but are passed to the database server as separate data. – Olivier Jacot-Descombes Mar 01 '22 at 17:22
1

You should check out [Can we stop using AddWithValue() already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) and stop using `.AddWithValue()` - it can lead to unexpected and surprising results... – marc_s Mar 01 '22 at 17:43
@OlivierJacot-Descombes Thanks for pointing out the mistake. I updated. – ByungYong Kang Mar 01 '22 at 17:43
this answer completely solved the problem. Thanks – Kaan Can Sürdem Mar 01 '22 at 21:17

Syntax error while doing C# SQL data insert

1 Answers1