Can't retrieve data from sqlite3 file with unexpected characters

Question

I have sqlite3 database file that I'm trying to get data from. When I try to open it in sqliteBrowser it will generate the tables but it ignores the data within the file so I get empty tables. This is the file: localData.sqlite

I don't know if the file is bad formatted or if I'm missing some function I have to run over it.

The file was created by a third party application on windows with the code below.

File creation

        {
            try
            {
                if (!File.Exists("queueData.sqlite"))
                {
                    SQLiteConnection.CreateFile("queueData.sqlite");
                }

                using (SQLiteConnection c = new SQLiteConnection(connection))
                {
                    c.Open();
                    using (SQLiteCommand cmd = new SQLiteCommand("create table if not exists vehicles (data TEXT)", c))
                    {
                        cmd.ExecuteNonQuery();
                    }
                    using (SQLiteCommand cmd = new SQLiteCommand("create table if not exists vehiclesMQTT (data TEXT)", c))
                    {
                        cmd.ExecuteNonQuery();
                    }
                    using (SQLiteCommand cmd = new SQLiteCommand("create table if not exists faces (data TEXT)", c))
                    {
                        cmd.ExecuteNonQuery();
                    }
                    using (SQLiteCommand cmd = new SQLiteCommand("create table if not exists facesMQTT (data TEXT)", c))
                    {
                        cmd.ExecuteNonQuery();
                    }
                    c.Close();
                }
            }
            catch (Exception ex)
            {
                AdLogsManager.instance.writeErrorMessage("Creating DB and Table: " + ex.Message,"", "", 7001);
            }
        }

Data insert

        {
            try
            {
                var tableName = getTableNameByType(type);
                string sql = "insert into " + tableName + " (data) values (\'" + data + "\')";
                using (SQLiteConnection c = new SQLiteConnection(connection))
                {
                    c.Open();
                    using (SQLiteCommand cmd = new SQLiteCommand(sql, c))
                    {
                        cmd.ExecuteNonQuery();
                    }
                    c.Close();
                }
                return true;
            }
            catch (Exception ex)
            {
                AdLogsManager.instance.writeErrorMessage("Saving data: " + ex.Message,"", "", 7002);
                return false;
            }
        }

If anyone could help me solve the issue or find a way to obtain the data, I would greatly appreciate it.

Thanks.

Answer 1

With no warranties at all!

Your database is corrupt or, somehow, records were deleted.

However, you still can open your file in a text editor and see a lot of records like:

...{"channel":"app-emit","payload":{"type":"vehicle","deviceId":"d4f9bb04b6d5","time":1595510864502,"appName":"vehicle","appVersion":"1.0.1","data":{"count":1,"speed":0.0,"zoneId":"inbound","time":1595510864502}}}...

I don't know which table they belong or if they are actual or outdated, not even how many are lost.

Using this method, could recovered 4087 records: https://justpaste.it/38j03

Further knowledge on your application should help you evaluate if they are useful or not.

EDIT: Method used:

I used a hexadecimal/text editor (MadEdit) to:

1. place a break before {"channel": (replace hex 7B226368616E6E656C223A with 0A7B226368616E6E656C223A)
2. place a break after }}} (replace hex 7D7D7D with 7D7D7D0A)
3. removed every occurrence of null (replace hex 00 with nothing)

Then copied everything an pasted into spreadsheet application (Excel), so some sorting and filtering may be done.

Better method: open in Word, make same replacements ({"channel": to ^p"channel": and }}} to }}}^p), then paste to Excel.