Journal encore du SQL noir 🎭

Sommaire

• 4^th Case
  • 1^st step
  • 2^nd step
• 5^th Case
  • access logs check
  • mails logs check
• à suivre

On s’était laissé précédemment avec la promesse d’examiner plus d’enquêtes s’il y a de la demande.¹

4^th Case

Le dossier est présenté en commençant par sa date précise, mais ce serait trop facile

SELECT COUNT(*) FROM crime_scene WHERE date = 19871031; -- 51
SELECT COUNT(*) FROM crime_scene WHERE location LIKE '%Coconut%'; -- 2
SELECT COUNT(*) FROM crime_scene WHERE description LIKE '%Leonard%'; -- 0

On peut associer le lieu, mais ce n’est pas 'Coconut Grove' comme je l’aurais cru.

1^st step

Un fois le dossier ouvert, on nous résume les deux lignes de witness_statements.clue à trouver.

-- imbrication
SELECT * FROM witness_statements
WHERE crime_scene_id = (
  SELECT id FROM crime_scene
  WHERE date = 19871031 AND location LIKE '%Coconut %'
);

-- jointure
SELECT w.* FROM witness_statements w
  INNER JOIN crime_scene c ON w.crime_scene_id = c.id
WHERE c.date = 19871031 AND c.location LIKE '%Coconut %';

Petite interrogation ici : que représente ce champ witness_id ?

Poursuivons. Les deux confessions renvoient à une réservation de chambre.

SELECT COUNT(*) FROM hotel_checkins; -- 200
SELECT COUNT(*) FROM hotel_checkins WHERE hotel_name LIKE '% regency'; -- 16
SELECT COUNT(*) FROM hotel_checkins WHERE hotel_name LIKE 'grand %'; -- 1
SELECT COUNT(*) FROM hotel_checkins WHERE check_in_date = 19871030; -- 65
SELECT COUNT(*) FROM hotel_checkins WHERE room_number = 707; -- 14
SELECT COUNT(*) FROM hotel_checkins
WHERE (room_number = 707) AND (check_in_date = 19871030); -- 10

Étrange toutes les réservations faites au même hôtel, même jour et même chambre. Est-ce que le journal des observations va nous apprendre quelque chose ?

-- imbrication
SELECT id, note
FROM surveillance_records
WHERE hotel_checkin_id IN (
  SELECT id FROM hotel_checkins
  WHERE (room_number = 707) AND (check_in_date = 19871030)
);

-- jointure
SELECT s.id, s.note
FROM surveillance_records s
  INNER JOIN hotel_checkins h ON s.hotel_checkin_id = h.id
WHERE (h.room_number = 707) AND (h.check_in_date = 19871030)
;

Quatre lignes sans note (valeur NULL) et une grosse comme un nez de clown…

Subject was overheard yelling on a phone: "Did you kill him?"

Mais si l’on tente de remonter naïvement la personne qui a fait cette réservation, cela ne marche pas…

--- requêtes imbriquées (en remontant le puzzle)
SELECT * FROM person WHERE id IN (
  SELECT person_id FROM hotel_checkins
  WHERE (room_number = 707) AND (check_in_date = 19871030)
    AND (id =(
      SELECT hotel_checkin_id FROM surveillance_records
      WHERE note LIKE '%Did you kill him?%'
      ) -- surveillance_records.hotel_checkin_id
    ) -- hotel_checkins.person_id
);

-- jointure
SELECT p.*
FROM surveillance_records s
  INNER JOIN hotel_checkins h ON s.hotel_checkin_id = h.id
  INNER JOIN person p ON h.person_id = p.id
WHERE (h.room_number = 707) AND (h.check_in_date = 19871030)
  AND (s.note LIKE '%Did you kill him?%')
;

En fait, n’allons pas croire que le coupable se trouve parmi les sept personnes ayant réservée…

--- imbrication
SELECT * FROM person WHERE id IN (
  SELECT person_id FROM hotel_checkins
  WHERE (room_number = 707) AND (check_in_date = 19871030)
);

-- jointure
SELECT p.*
FROM hotel_checkins h
  INNER JOIN person p ON h.person_id = p.id
WHERE (h.room_number = 707) AND (h.check_in_date = 19871030)
;

Au passage, je note l’égalité entre surveillance_records.id et surveillance_records.hotel_checkin_id sans savoir si cela a quelque signification.

2^nd step

Bien, au stade actuel, on sait qui a causé de meurtre mais on n’a pas encore d’assassin, tout au plus un commanditaire ou une personne qui en sait trop ?
Bonne nouvelle, comme une certaine police secrète d’époque ou une certaine agence gouvernementale actuelle, nous surveillons les conversations afin de préserver les libertés…

SELECT COUNT(*) FROM phone_records; -- 200
SELECT COUNT(DISTINCT call_date) FROM phone_records; -- 3
SELECT DISTINCT call_date FROM phone_records; -- 19871028 19871029 19871030
SELECT COUNT(*) FROM phone_records WHERE call_date = 19871030 -- 67
SELECT COUNT(*) FROM phone_records WHERE call_date = 19871028 -- 66
SELECT COUNT(*) FROM phone_records WHERE call_date = 19871028 -- 67

Ça va, que septante-sept lignes à scruter… Ou on peut ruser en cherchant directement les appels depuis ou vers la personne ayant réservé…

SELECT * FROM phone_records
WHERE (call_date = 19871030) AND (
  ( caller_id IN (11, 34, 78, 123, 178, 156, 198) )
  OR
  ( recipient_id IN (11, 34, 78, 123, 178, 156, 198) )
  );

Ça nous fait deux personnes à entendre…

SELECT id, confession FROM final_interviews WHERE
  person_id = (SELECT caller_id FROM phone_records WHERE note LIKE '%kill %')
  OR
  person_id = (SELECT recipient_id FROM phone_records WHERE note LIKE '%kill %')
  ;

Noter qu’ici aussi on a final_interviews.id qui a la même valeur que final_interviews.person_id pour une raison que j’ignore.

Mais on n’est pas plus avancé… Mais la conversion téléphonique contient un indice que l’on peut tenter d’exploiter.

You should have left the carpenter do it himself!

On va donc s’intéresser aux menuisiers…

-- imbrication
SELECT id, confession FROM final_interviews WHERE
WHERE person_id IN (
  SELECT id FROM person
  WHERE occupation LIKE 'carpenter'
);

-- jointure
SELECT i.id, i.confession FROM final_interviews i
  INNER JOIN person p ON i.person_id = p.id
WHERE p.occupation LIKE 'carpenter';

…et il y en a bien un qui avoue la commandite.
Dans le lot, il y a deux tables qui n’ont pas été utilisées : vehicle_registry et catering_orders. Dites moi si vous savez quelle est leur utilité…

5^th Case

Pour finir en beauté, temporairement (puisqu’on espère d’autres enquêtes), on va appliquer notre forensic à l’industrie des µprocs.

access logs check

Plus de table crime_scene et aucune idée de laquelle par laquelle commencer. Mais le incident_reports me semble bien…

SELECT * FROM incident_reports LIMIT 9; -- confirmation
SELECT * FROM incident_reports WHERE location LIKE '%quantum%'; -- bingo

Comme les tables sont données pratiquement dans l’ordre d’exploration conseillé, la seconde étape va être la consultations des témoignages comme précédemment.

SELECT * FROM witness_statements WHERE incident_id =
(SELECT id FROM incident_reports WHERE location LIKE 'Quantum%');

Le premier témoignage mentionne un serveur

SELECT DISTINCT server_location FROM computer_access_logs LIMIT 9; -- check

SELECT * FROM computer_access_logs
WHERE server_location = 'Helsinki'; -- 14

SELECT * FROM computer_access_logs
WHERE server_location = 'Helsinki' AND access_date =
  (SELECT date FROM incident_reports WHERE location LIKE 'Quantum%'); -- 4

Le second témoignage mentionne une carte d’accès

SELECT COUNT(*) FROM keycard_access_logs; -- 200
SELECT COUNT(*) FROM keycard_access_logs WHERE access_date =
  (SELECT date FROM incident_reports WHERE location LIKE 'Quantum%'); -- 200
SELECT DISTINCT keycard_code FROM keycard_access_logs LIMIT 9; -- overview
SELECT COUNT(*) FROM keycard_access_logs WHERE keycard_code LIKE 'QX-0%'; -- 70

SELECT * FROM keycard_access_logs
WHERE REGEX_LIKE(keycard_code,'QX-0[0-9][13579]');
-- [Error in query] no such function: REGEX_LIKE

SELECT * FROM keycard_access_logs
WHERE keycard_code REGEX 'QX-0[0-9][13579]';
-- [Error in query] near "REGEX": syntax error

SELECT * FROM keycard_access_logs
WHERE keycard_code SIMILAR TO 'QX-0[0-9][13579]';
-- [Error in query] near "SIMILAR": syntax error

SELECT * FROM keycard_access_logs
WHERE keycard_code LIKE 'QX-0%'
  --AND (SUBSTR(keycard_code,5,1) BETWEEN 0 AND 9)
  AND SUBSTR(keycard_code,6,1) IN (1,3,5,7,9); -- 0

SELECT * FROM keycard_access_logs
WHERE keycard_code LIKE 'QX-0%'
  --AND (SUBSTR(keycard_code,5,1) BETWEEN '0' AND '9')
  AND SUBSTR(keycard_code,6,1) IN ('1','3','5','7','9'); -- 36

SELECT * FROM keycard_access_logs
WHERE keycard_code LIKE 'QX-0%'
  --AND (SUBSTRING(keycard_code,5,1) BETWEEN '0' AND '9')
  AND SUBSTRING(keycard_code,6,1) IN ('1','3','5','7','9'); -- 36

SELECT * FROM keycard_access_logs
WHERE keycard_code LIKE 'QX-0%'
  --AND (SUBSTRING(keycard_code FROM 5 FOR 1) BETWEEN '0' AND '9')
  AND SUBSTRING(keycard_code FROM 6 FOR 1) IN ('1','3','5','7','9'); -- 36
-- [Error in query] near "from": syntax error

Ici, tous les accès sont datés du même jour ; donc ce critère ne sera pas utile/pertinent. J’ai pensé à utiliser l’information d’imparité en prenant en compte le fait que tous les badges ici commencent par « QX- » avec trois chiffres (et non deux… à moins que ce ne soient deux impaires et que j’ai mal compris ?) Je pense que l’usage des expressions rationnelles est fort utile (on réduit de 70 à 36 entrées, ce qui est encore beaucoup.)

• Sauf erreur, la fonction REGEX_LIKE(column,'POSIX ERE'[,opt-flags]) connue de plusieurs éditeurs (en tout cas DB2, My/Maria, Oracle, Pg, T-SQL, et certainement d’autres), est dans le standard (1999 ?) Mais ce n’est pas reconnu ici…
• On retrouve aussi la syntaxe column REGEX 'some RE' chez certains éditeurs (dont Sybase/SAP, SQLite via une fonction personnalisée, MySQL et MariaDB qui ont aussi l’alias RLIKE, etc.) Ce n’est pas reconnu non plus…
• Il y a la syntaxe column SIMILAR TO 'SQL RE' qui est dans SQL:1999 et fonctionne comme column LIKE 'pattern' (les méta caractères _ et % ont la même signification et c’est tout le champ qui est validé), mais avec des extensions des expressions rationnelles POSIX (il n’y a pas le méta caractère . ni les ancres ^ et $.) C’est un excellent entre deux supporté par certains éditeurs (dont PostgreSQL et SQLanywhere) mais pas encore par tous (notamment pas My/Maria et SQLite), ce qui est le cas (non support) ici.
• Moins connu (et ne fonctionne pas ici…) SQL:2003 a également spécifié SUBSTRING(column SIMILAR 'pattern' [ESCAPE escape]) comme écriture alternative. Pour l’instant, ça ne semble reconnu que par PostgreSQL…

Tout espoir n’est pas perdu car l’expression rationnelle peut être traduite facilement par des comparaisons sur des sous-chaînes… Mais j’y vais avec un peu d’appréhension car c’est un peu le zoo…

• Dans le sillage d’Oracle, beaucoup de SGBD (dont SQLite) utilisent SUBSTR(column,start[,length]) avec le comptage à partir de 1 et optionnellement le nombre de caractères à extraire (tout le reste si rien n’est précisé.) Beaucoup d’implémentations permettent de compter à partir de la fin en utilisant un nombre négatif (avec -1 donc pour le dernier caractère.) Ça marche ici…
• D’autres éditeurs (dont SQL Anywhere), dans le sillage de T-SQL, utilisent plutôt SUBSTRING(column,start[,length]) pareillement. Avec des signatures similaires, on retrouvera les deux formes (c’est le cas ici…), SUBSTRING étant alors un alias de SUBSTR (cas par exemple de SQLite≥3.34) ou l’inverse (cas par exemple de Sybase, mais aussi My/Maria qui ajoutent aussi l’alias MID en clin d’œil aux BASIC.)
• On peut retrouver d’autres fonctions (comme MID(column,start[,length]) déjà évoqué et qui n’est pas reconnu ici…) et SQL:2008 a finalement retenu SUBSTRING(column [FROM start] [FOR length]) (pas reconnu ici non plus, mais pris en charge uniquement dans My/Maria, Ingres, Pg, aucunement par les grosses solutions propriétaires au moment où j’écris…) évidemment (le standard privilégie toujours une formulation explicite et un peu verbeuse.)
• Parmi les fonctions d’extraction de chaîne, SQL Server et d’autres dans son sillage proposent les raccourcis (pas exactement mais on va dire cela pour faire simple) LEFT(column,length) et RIGHT(column,length) pour SUBSTRING(column,1,length) (ou SUBSTRING(column FOR length)…) et SUBSTRING(column,-1,length) respectivement.

On a eu du bol… Mais il reste quand même beaucoup de lignes à parcourir (14 et 36) Puisque nos deux tables ont des champs communs, on peut faire faire la comparaison au serveur…

SELECT employee_id, access_date, access_time FROM computer_access_logs
WHERE server_location = 'Helsinki' AND access_date =
  (SELECT date FROM incident_reports WHERE location LIKE 'Quantum%')

INTERSECT

SELECT employee_id, access_date, access_time FROM keycard_access_logs
WHERE keycard_code LIKE 'QX-0%'
  AND SUBSTRING(keycard_code,6,1) IN ('1','3','5','7','9')

; -- 0/1 avec/sans access_time

Non, cette personne n’est pas notre coupable comme il en ressort de sa confession… Il est normal que nous soyons passé à côté vu que cette entrée (de witness_statements) n’est associée à aucun incident… 😉

mails logs check

Bien, voyons donc les mails reçus par cette personne ce jour là.

SELECT * FROM email_logs WHERE
  (recipient_employee_id = 99) -- mettre la valeur trouvée
  AND (email_date =
  (SELECT date FROM incident_reports WHERE location LIKE 'Quantum%')
  ); -- le premier critère seul suffit en fait

La source du message n’a rien noté d’anormal (i.e. pas d’entrée dans witness_statements) et n’a envoyé que ce message. Par contre cette source a reçu deux autres messages anonymes…

SELECT * FROM email_logs WHERE recipient_employee_id =
  (SELECT sender_employee_id FROM email_logs
  WHERE recipient_employee_id = 99);

On connaît deux éléments de la chaîne d’événements de l’affaire.

SELECT * FROM employee_records WHERE id = 99 OR id =
  (SELECT sender_employee_id FROM email_logs
  WHERE recipient_employee_id = 99);

Il nous manque une troisième personne mentionnée dans l’un des mails

[…] but we need her inside F18 before 9. […]
Unlock 18 quietly by 9. He’ll use his own credentials to access it shortly after L leaves. […]

La lumière est, quand on comprend à quoi il est fait référence…

SELECT COUNT(*) FROM facility_access_logs; -- 100
SELECT COUNT(*) FROM facility_access_logs WHERE access_date =
  (SELECT date FROM incident_reports WHERE location LIKE 'Quantum%'); -- 100
SELECT COUNT(DISTINCT facility_name) FROM facility_access_logs; -- 79
SELECT DISTINCT facility_name FROM facility_access_logs LIMIT 9; -- check…
SELECT COUNT(*) FROM facility_access_logs WHERE facility_name LIKE '%18'; -- 3

-- jointure
SELECT e.*, f.access_time FROM facility_access_logs f
  INNER JOIN employee_records e ON e.id = f.employee_id
WHERE f.facility_name LIKE '%18'
ORDER BY f.access_time;

-- imbrication
SELECT * FROM employee_records WHERE id IN (
  SELECT employee_id FROM facility_access_logs
  WHERE facility_name LIKE '%18'
  ORDER BY access_time
);

Et voilà…

à suivre

Six affaires résolues et 1900 points d’expérience engrangés.
Il y a des requêtes que j’aurais voulu refaire pour tester le support des CTE et autres constructions un peu sympathiques. Mais la longue pose (et je ne sais même plus pourquoi j’avais oublié ce writeup en cours) me rend l’exercice peu attrayant (en plus j’ai peu de bande passante.) Entre temps, le site s’est doté d’un blog que je viens de découvrir : www.sqlnoir.com/blog/. Le premier post présente d’autres jeux similaires qui occuperont probablement mes prochains congés. Les deux posts suivants donnent les clés pour SQL Noir et le quotidien des analystes…