Petter Reinholdtsen: New and improved sqlcipher in Debian for accessing Signal database
For a while now I wanted to have direct access to the
Signal database of messages and
channels of my Desktop edition of Signal. I prefer the enforced end
to end encryption of Signal these days for my communication with
friends and family, to increase the level of safety and privacy as
well as raising the cost of the mass surveillance government and
non-government entities practice these days. In August I came across
a nice
recipe
on how to use sqlcipher to extract statistics from the Signal
database explaining how to do this. Unfortunately this did not
work with the version of sqlcipher in Debian. The
sqlcipher
package is a "fork" of the sqlite package with added support for
encrypted databases. Sadly the current Debian maintainer
announced more than three
years ago that he did not have time to maintain sqlcipher, so it
seemed unlikely to be upgraded by the maintainer. I was reluctant to
take on the job myself, as I have very limited experience maintaining
shared libraries in Debian. After waiting and hoping for a few
months, I gave up the last week, and set out to update the package. In
the process I orphaned it to make it more obvious for the next person
looking at it that the package need proper maintenance.
The version in Debian was around five years old, and quite a lot of
changes had taken place upstream into the Debian maintenance git
repository. After spending a few days importing the new upstream
versions, realising that upstream did not care much for SONAME
versioning as I saw library symbols being both added and removed with
minor version number changes to the project, I concluded that I had to
do a SONAME bump of the library package to avoid surprising the
reverse dependencies. I even added a simple
autopkgtest script to ensure the package work as intended. Dug deep
into the hole of learning shared library maintenance, I set out a few
days ago to upload the new version to Debian experimental to see what
the quality assurance framework in Debian had to say about the result.
The feedback told me the pacakge was not too shabby, and yesterday I
uploaded the latest version to Debian unstable. It should enter
testing today or tomorrow, perhaps delayed by
a small library
transition.
Armed with a new version of sqlcipher, I can now have a look at the
SQL database in ~/.config/Signal/sql/db.sqlite. First, one need to
fetch the encryption key from the Signal configuration using this
simple JSON extraction command:
/usr/bin/jq -r '."key"' ~/.config/Signal/config.jsonAssuming the result from that command is 'secretkey', which is a hexadecimal number representing the key used to encrypt the database. Next, one can now connect to the database and inject the encryption key for access via SQL to fetch information from the database. Here is an example dumping the database structure:
% sqlcipher ~/.config/Signal/sql/db.sqlite
sqlite> PRAGMA key = "x'secretkey'";
sqlite> .schema
CREATE TABLE sqlite_stat1(tbl,idx,stat);
CREATE TABLE conversations(
id STRING PRIMARY KEY ASC,
json TEXT,
active_at INTEGER,
type STRING,
members TEXT,
name TEXT,
profileName TEXT
, profileFamilyName TEXT, profileFullName TEXT, e164 TEXT, serviceId TEXT, groupId TEXT, profileLastFetchedAt INTEGER);
CREATE TABLE identityKeys(
id STRING PRIMARY KEY ASC,
json TEXT
);
CREATE TABLE items(
id STRING PRIMARY KEY ASC,
json TEXT
);
CREATE TABLE sessions(
id TEXT PRIMARY KEY,
conversationId TEXT,
json TEXT
, ourServiceId STRING, serviceId STRING);
CREATE TABLE attachment_downloads(
id STRING primary key,
timestamp INTEGER,
pending INTEGER,
json TEXT
);
CREATE TABLE sticker_packs(
id TEXT PRIMARY KEY,
key TEXT NOT NULL,
author STRING,
coverStickerId INTEGER,
createdAt INTEGER,
downloadAttempts INTEGER,
installedAt INTEGER,
lastUsed INTEGER,
status STRING,
stickerCount INTEGER,
title STRING
, attemptedStatus STRING, position INTEGER DEFAULT 0 NOT NULL, storageID STRING, storageVersion INTEGER, storageUnknownFields BLOB, storageNeedsSync
INTEGER DEFAULT 0 NOT NULL);
CREATE TABLE stickers(
id INTEGER NOT NULL,
packId TEXT NOT NULL,
emoji STRING,
height INTEGER,
isCoverOnly INTEGER,
lastUsed INTEGER,
path STRING,
width INTEGER,
PRIMARY KEY (id, packId),
CONSTRAINT stickers_fk
FOREIGN KEY (packId)
REFERENCES sticker_packs(id)
ON DELETE CASCADE
);
CREATE TABLE sticker_references(
messageId STRING,
packId TEXT,
CONSTRAINT sticker_references_fk
FOREIGN KEY(packId)
REFERENCES sticker_packs(id)
ON DELETE CASCADE
);
CREATE TABLE emojis(
shortName TEXT PRIMARY KEY,
lastUsage INTEGER
);
CREATE TABLE messages(
rowid INTEGER PRIMARY KEY ASC,
id STRING UNIQUE,
json TEXT,
readStatus INTEGER,
expires_at INTEGER,
sent_at INTEGER,
schemaVersion INTEGER,
conversationId STRING,
received_at INTEGER,
source STRING,
hasAttachments INTEGER,
hasFileAttachments INTEGER,
hasVisualMediaAttachments INTEGER,
expireTimer INTEGER,
expirationStartTimestamp INTEGER,
type STRING,
body TEXT,
messageTimer INTEGER,
messageTimerStart INTEGER,
messageTimerExpiresAt INTEGER,
isErased INTEGER,
isViewOnce INTEGER,
sourceServiceId TEXT, serverGuid STRING NULL, sourceDevice INTEGER, storyId STRING, isStory INTEGER
GENERATED ALWAYS AS (type IS 'story'), isChangeCreatedByUs INTEGER NOT NULL DEFAULT 0, isTimerChangeFromSync INTEGER
GENERATED ALWAYS AS (
json_extract(json, '$.expirationTimerUpdate.fromSync') IS 1
), seenStatus NUMBER default 0, storyDistributionListId STRING, expiresAt INT
GENERATED ALWAYS
AS (ifnull(
expirationStartTimestamp + (expireTimer * 1000),
9007199254740991
)), shouldAffectActivity INTEGER
GENERATED ALWAYS AS (
type IS NULL
OR
type NOT IN (
'change-number-notification',
'contact-removed-notification',
'conversation-merge',
'group-v1-migration',
'keychange',
'message-history-unsynced',
'profile-change',
'story',
'universal-timer-notification',
'verified-change'
)
), shouldAffectPreview INTEGER
GENERATED ALWAYS AS (
type IS NULL
OR
type NOT IN (
'change-number-notification',
'contact-removed-notification',
'conversation-merge',
'group-v1-migration',
'keychange',
'message-history-unsynced',
'profile-change',
'story',
'universal-timer-notification',
'verified-change'
)
), isUserInitiatedMessage INTEGER
GENERATED ALWAYS AS (
type IS NULL
OR
type NOT IN (
'change-number-notification',
'contact-removed-notification',
'conversation-merge',
'group-v1-migration',
'group-v2-change',
'keychange',
'message-history-unsynced',
'profile-change',
'story',
'universal-timer-notification',
'verified-change'
)
), mentionsMe INTEGER NOT NULL DEFAULT 0, isGroupLeaveEvent INTEGER
GENERATED ALWAYS AS (
type IS 'group-v2-change' AND
json_array_length(json_extract(json, '$.groupV2Change.details')) IS 1 AND
json_extract(json, '$.groupV2Change.details[0].type') IS 'member-remove' AND
json_extract(json, '$.groupV2Change.from') IS NOT NULL AND
json_extract(json, '$.groupV2Change.from') IS json_extract(json, '$.groupV2Change.details[0].aci')
), isGroupLeaveEventFromOther INTEGER
GENERATED ALWAYS AS (
isGroupLeaveEvent IS 1
AND
isChangeCreatedByUs IS 0
), callId TEXT
GENERATED ALWAYS AS (
json_extract(json, '$.callId')
));
CREATE TABLE sqlite_stat4(tbl,idx,neq,nlt,ndlt,sample);
CREATE TABLE jobs(
id TEXT PRIMARY KEY,
queueType TEXT STRING NOT NULL,
timestamp INTEGER NOT NULL,
data STRING TEXT
);
CREATE TABLE reactions(
conversationId STRING,
emoji STRING,
fromId STRING,
messageReceivedAt INTEGER,
targetAuthorAci STRING,
targetTimestamp INTEGER,
unread INTEGER
, messageId STRING);
CREATE TABLE senderKeys(
id TEXT PRIMARY KEY NOT NULL,
senderId TEXT NOT NULL,
distributionId TEXT NOT NULL,
data BLOB NOT NULL,
lastUpdatedDate NUMBER NOT NULL
);
CREATE TABLE unprocessed(
id STRING PRIMARY KEY ASC,
timestamp INTEGER,
version INTEGER,
attempts INTEGER,
envelope TEXT,
decrypted TEXT,
source TEXT,
serverTimestamp INTEGER,
sourceServiceId STRING
, serverGuid STRING NULL, sourceDevice INTEGER, receivedAtCounter INTEGER, urgent INTEGER, story INTEGER);
CREATE TABLE sendLogPayloads(
id INTEGER PRIMARY KEY ASC,
timestamp INTEGER NOT NULL,
contentHint INTEGER NOT NULL,
proto BLOB NOT NULL
, urgent INTEGER, hasPniSignatureMessage INTEGER DEFAULT 0 NOT NULL);
CREATE TABLE sendLogRecipients(
payloadId INTEGER NOT NULL,
recipientServiceId STRING NOT NULL,
deviceId INTEGER NOT NULL,
PRIMARY KEY (payloadId, recipientServiceId, deviceId),
CONSTRAINT sendLogRecipientsForeignKey
FOREIGN KEY (payloadId)
REFERENCES sendLogPayloads(id)
ON DELETE CASCADE
);
CREATE TABLE sendLogMessageIds(
payloadId INTEGER NOT NULL,
messageId STRING NOT NULL,
PRIMARY KEY (payloadId, messageId),
CONSTRAINT sendLogMessageIdsForeignKey
FOREIGN KEY (payloadId)
REFERENCES sendLogPayloads(id)
ON DELETE CASCADE
);
CREATE TABLE preKeys(
id STRING PRIMARY KEY ASC,
json TEXT
, ourServiceId NUMBER
GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId')));
CREATE TABLE signedPreKeys(
id STRING PRIMARY KEY ASC,
json TEXT
, ourServiceId NUMBER
GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId')));
CREATE TABLE badges(
id TEXT PRIMARY KEY,
category TEXT NOT NULL,
name TEXT NOT NULL,
descriptionTemplate TEXT NOT NULL
);
CREATE TABLE badgeImageFiles(
badgeId TEXT REFERENCES badges(id)
ON DELETE CASCADE
ON UPDATE CASCADE,
'order' INTEGER NOT NULL,
url TEXT NOT NULL,
localPath TEXT,
theme TEXT NOT NULL
);
CREATE TABLE storyReads (
authorId STRING NOT NULL,
conversationId STRING NOT NULL,
storyId STRING NOT NULL,
storyReadDate NUMBER NOT NULL,
PRIMARY KEY (authorId, storyId)
);
CREATE TABLE storyDistributions(
id STRING PRIMARY KEY NOT NULL,
name TEXT,
senderKeyInfoJson STRING
, deletedAtTimestamp INTEGER, allowsReplies INTEGER, isBlockList INTEGER, storageID STRING, storageVersion INTEGER, storageUnknownFields BLOB, storageNeedsSync INTEGER);
CREATE TABLE storyDistributionMembers(
listId STRING NOT NULL REFERENCES storyDistributions(id)
ON DELETE CASCADE
ON UPDATE CASCADE,
serviceId STRING NOT NULL,
PRIMARY KEY (listId, serviceId)
);
CREATE TABLE uninstalled_sticker_packs (
id STRING NOT NULL PRIMARY KEY,
uninstalledAt NUMBER NOT NULL,
storageID STRING,
storageVersion NUMBER,
storageUnknownFields BLOB,
storageNeedsSync INTEGER NOT NULL
);
CREATE TABLE groupCallRingCancellations(
ringId INTEGER PRIMARY KEY,
createdAt INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS 'messages_fts_data'(id INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE IF NOT EXISTS 'messages_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE IF NOT EXISTS 'messages_fts_content'(id INTEGER PRIMARY KEY, c0);
CREATE TABLE IF NOT EXISTS 'messages_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE IF NOT EXISTS 'messages_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID;
CREATE TABLE edited_messages(
messageId STRING REFERENCES messages(id)
ON DELETE CASCADE,
sentAt INTEGER,
readStatus INTEGER
, conversationId STRING);
CREATE TABLE mentions (
messageId REFERENCES messages(id) ON DELETE CASCADE,
mentionAci STRING,
start INTEGER,
length INTEGER
);
CREATE TABLE kyberPreKeys(
id STRING PRIMARY KEY NOT NULL,
json TEXT NOT NULL, ourServiceId NUMBER
GENERATED ALWAYS AS (json_extract(json, '$.ourServiceId')));
CREATE TABLE callsHistory (
callId TEXT PRIMARY KEY,
peerId TEXT NOT NULL, -- conversation id (legacy) uuid groupId roomId
ringerId TEXT DEFAULT NULL, -- ringer uuid
mode TEXT NOT NULL, -- enum "Direct" "Group"
type TEXT NOT NULL, -- enum "Audio" "Video" "Group"
direction TEXT NOT NULL, -- enum "Incoming" "Outgoing
-- Direct: enum "Pending" "Missed" "Accepted" "Deleted"
-- Group: enum "GenericGroupCall" "OutgoingRing" "Ringing" "Joined" "Missed" "Declined" "Accepted" "Deleted"
status TEXT NOT NULL,
timestamp INTEGER NOT NULL,
UNIQUE (callId, peerId) ON CONFLICT FAIL
);
[ dropped all indexes to save space in this blog post ]
CREATE TRIGGER messages_on_view_once_update AFTER UPDATE ON messages
WHEN
new.body IS NOT NULL AND new.isViewOnce = 1
BEGIN
DELETE FROM messages_fts WHERE rowid = old.rowid;
END;
CREATE TRIGGER messages_on_insert AFTER INSERT ON messages
WHEN new.isViewOnce IS NOT 1 AND new.storyId IS NULL
BEGIN
INSERT INTO messages_fts
(rowid, body)
VALUES
(new.rowid, new.body);
END;
CREATE TRIGGER messages_on_delete AFTER DELETE ON messages BEGIN
DELETE FROM messages_fts WHERE rowid = old.rowid;
DELETE FROM sendLogPayloads WHERE id IN (
SELECT payloadId FROM sendLogMessageIds
WHERE messageId = old.id
);
DELETE FROM reactions WHERE rowid IN (
SELECT rowid FROM reactions
WHERE messageId = old.id
);
DELETE FROM storyReads WHERE storyId = old.storyId;
END;
CREATE VIRTUAL TABLE messages_fts USING fts5(
body,
tokenize = 'signal_tokenizer'
);
CREATE TRIGGER messages_on_update AFTER UPDATE ON messages
WHEN
(new.body IS NULL OR old.body IS NOT new.body) AND
new.isViewOnce IS NOT 1 AND new.storyId IS NULL
BEGIN
DELETE FROM messages_fts WHERE rowid = old.rowid;
INSERT INTO messages_fts
(rowid, body)
VALUES
(new.rowid, new.body);
END;
CREATE TRIGGER messages_on_insert_insert_mentions AFTER INSERT ON messages
BEGIN
INSERT INTO mentions (messageId, mentionAci, start, length)
SELECT messages.id, bodyRanges.value ->> 'mentionAci' as mentionAci,
bodyRanges.value ->> 'start' as start,
bodyRanges.value ->> 'length' as length
FROM messages, json_each(messages.json ->> 'bodyRanges') as bodyRanges
WHERE bodyRanges.value ->> 'mentionAci' IS NOT NULL
AND messages.id = new.id;
END;
CREATE TRIGGER messages_on_update_update_mentions AFTER UPDATE ON messages
BEGIN
DELETE FROM mentions WHERE messageId = new.id;
INSERT INTO mentions (messageId, mentionAci, start, length)
SELECT messages.id, bodyRanges.value ->> 'mentionAci' as mentionAci,
bodyRanges.value ->> 'start' as start,
bodyRanges.value ->> 'length' as length
FROM messages, json_each(messages.json ->> 'bodyRanges') as bodyRanges
WHERE bodyRanges.value ->> 'mentionAci' IS NOT NULL
AND messages.id = new.id;
END;
sqlite>
Finally I have the tool needed to inspect and process Signal
messages that I need, without using the vendor provided client. Now
on to transforming it to a more useful format.
As usual, if you use Bitcoin and want to show your support of my
activities, please send Bitcoin donations to my address
15oWEoG9dUPovwmUL9KWAnYRtNJEkP1u1b.
Do you know what s happening in the wires of your network? There is a major
FLOSS player in the field of real time intrusion detection (IDS), inline
intrusion prevention (IPS) and network security monitoring (NSM).
I m talking about Suricata, a mature, fast and robust network threat detection
engine. Suricata is a community driven project, supported by the
Dear all, an 
While the above medical device is similar, mine had a cross, the needle was much shorter and is injected into the vein. After that all injections are injected into that including common liquid which is salt,water and something commonly given to patients to stabilize first. I am not remembering the name atm.
I also had a urine bag which was attached to my penis in a non-invasive manner. Both my grandfather and grandma used to cry when things went wrong while I didn t feel any pain but when the urine bag was disattached and attached again, so seems things have improved there.
I was also very conscious of getting bed sores as both my grandpa and grandma had them when in hospital. As I had no strength I had to beg. plead do everything to make sure that every few hours I was turned from one side to other. I also had an air bag which is supposed to alleviate or relief this condition.
Constant physiotherapy every day for a while slowly increased my strength and slowly both the vigo and feeding tube put inside my throat was removed.
I have no remembrance as to when they had put the feeding tube as it was all rubber and felt bad when it came out.
Further physiotherapy helped me crawl till the top of the bed, the bed was around 6 feet in length and and more than enough so I could turn both sides without falling over.
Few days later I found I could also sit up using my legs as a lever and that gave confidence to the doctors to remove the air bed so I could crawl more easily.
Couple of more days later I stood on my feet for the first time and it was like I had lead legs. Each step was painful but the sense and feeling of independence won over whatever pain was there.
I had to endure wet wipes from nurses and ward boys in place of a shower everyday and while they were respectful always it felt humiliating.
The first time I had a bath after 2 weeks or something, every part of my body cried and I felt like a weakling. I had thought I wouldn t be able to do justice to the physiotherapy session which was soon after but after the session was back to feeling normal.
For a while I was doing the penguin waddle which while painful was also had humor in it. I did think of shooting the penguin waddle but decided against it as I was half-naked most of the time ( the hospital clothes never fit me properly)
Cut to today and I was able to climb up and down the stairs on my own and circled my own block, slowly but was able to do it on my own by myself.
While I always had a sense of wonderment for bi-pedal motion as well as all other means of transport, found much more respect of walking. I live near a fast food eating joint so I see lot of youngsters posing in different ways with their legs to show interest to their mates. And this I know happens both on the conscious and sub-conscious levels. To be able to see and discern that also put a sense of wonder in nature s creations.
All in all, I m probabl6y around 40% independent and still 60% interdependent. I know I have to be patient with myself and those around me and explain to others what I m going through.
For e.g. I still tend to spill things and still can t touch-type much.
So, the road is long, I can only pray and hope best wishes for anybody who is my condition and do pray that nobody goes through what I went through, especiallly not children.
I am also hoping that things like 
This question keeps on popping up in many recent newspaper articles, on discussion boards and in seminars debating immigration issues in Finland. At the core of the question is a general impression that, between the enthusiastic supporters of multiculturalism and the pessimistic nationalist rednecks, very little constructive discussion is possible in Finland.
At one end, humanitarian help organizations, as well as politicians from the Green Party and the Swedish Folk Party, keep on taking excessively generous initiatives towards refugees and asylum seekers, all while labeling their detractors as racists. At the other end, nationalist candidates in the National Coalition Party and True Finns Party keep on painting every immigrant as a refugee who is abusing social security and label their detractors as turning Finland into the easiest country to abuse social security, at the Finnish taxpayer's expense. In between, candidates from the Christian Democrats and Center Party spread equally among these two camps. Meanwhile, while the Left is currently avoiding discussions on this topic, Social Democrats have previously shown that they tend to side with the nationalists in labeling all immigrants as social security abusing refugees.
One interesting point is that, no matter at which end of the political spectrum one looks, the whole Finnish immigration debate keeps on focusing exclusively on refugees and asylum seekers, despite the fact that migrant workers and foreign spouses of Finnish nationals make up for a good 70% of the foreign population residing on Finnish soil.
This recalcitrance to discuss immigration from any other perspective than refugees is rather painfully noticeable. Some recent examples:
