c# System.Data.SQLite.SQLiteException: "SQL logic error unrecognized token: '#' "

Question

I want to Capture network traffic and save the ASCII code in my database and I still got this(is it unsupported character in database?):

Unhandled exception. code = Error (1), message = System.Data.SQLite.SQLiteException (0x800007BF): SQL logic error unrecognized token: "#" at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain) at System.Data.SQLite.SQLiteCommand.BuildNextCommand() at System.Data.SQLite.SQLiteDataReader.NextResult() at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave) at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior) at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior) at CapturingAndParsingPackets.MainClass.Main(String[] args) in C:\Users\Administrator\CSharp\packetnet-master\packetnet-master\Examples\CapturingAndParsingPackets\Main.cs:line 196

here's my code:

try
    {
        string sql = $"insert into log (Time, TYPE, DstIP, DstPort, SrcIP, SrcPort, Flag, TotalPacketLength, AsciiCode) values ('{DateTime.Now.ToString("yyyy'-'MM'-'dd HH:mm:ss")}', 'TCP', '{ipPacket.DestinationAddress}', '{tcpPacket.DestinationPort}', '{ipPacket.SourceAddress}', '{tcpPacket.SourcePort}', '{tcpPacket.Flags}', '{tcpPacket.TotalPacketLength}', '{ipPacket.PrintAscii()}')";
        SQLiteCommand command = new SQLiteCommand(sql, m_dbconnection);
        command.ExecuteNonQuery();
        m_dbconnection.Close();
    }
catch(Exception) //if Ascii code is too long
    { 
    string sql = $"insert into log (Time, TYPE, DstIP, DstPort, SrcIP, SrcPort, Flag, TotalPacketLength, AsciiCode) values ('{DateTime.Now.ToString("yyyy'-'MM'-'dd HH:mm:ss")}', 'TCP', '{ipPacket.DestinationAddress}', '{tcpPacket.DestinationPort}', '{ipPacket.SourceAddress}', '{tcpPacket.SourcePort}', '{tcpPacket.Flags}', '{tcpPacket.TotalPacketLength}', 'Encrypted Code')";
        SQLiteCommand command = new SQLiteCommand(sql, m_dbconnection);
        command.ExecuteNonQuery();
        m_dbconnection.Close();
    }

And this is my database structure:

string sql = "create table log(PacketID INTEGER PRIMARY KEY AUTOINCREMENT, Time DATETIME," +
                    "TYPE CHAR(6) NULL DEFAULT '', DstIP varchar(15), DstPort INT, SrcIP varchar(15), SrcPort INT, Flag varchar(2), TotalPacketLength INT, AsciiCode BLOB)";
                SQLiteCommand command = new SQLiteCommand(sql, m_dbconnection);
                command.ExecuteNonQuery();

It's amazing how many people ask for help with SQL code and then don't show us the SQL code. The C# code that builds your SQL code is not the SQL code. Regardless, don't build SQL code that way in the first place. Use parameters and you solve all sorts of problems, including those that result from formatting issues with literal values. — jmcilhinney, Dec 28 '22 at 04:29
you are passing some invalid characters like `#` in the value — Vivek Nuna, Dec 28 '22 at 10:09

score 0 · Answer 1 · answered Dec 28 '22 at 04:31

A Simple Wrapper class I provided on another answer.

You should never build a SQL command like you are. You will be open to SQL-Injection which is bad. By working directly with a class structure object that matches your table, this might offer better control and readability without the exposure to injection and bad formatting of string commands.

c# System.Data.SQLite.SQLiteException: "SQL logic error unrecognized token: '#' "

1 Answers1