Possible to see solution before starting mystery

[tomwj]

It's possible to accidentally solve this without working through any of the mystery.

After the introduction.

SELECT sql 
  FROM sqlite_master
 where name = 'crime_scene_report'

Spoiler warning

I then wanted to see what other tables there were.

SELECT * FROM sqlite_master

This reveals the solution check and text. I'm not very familiar with SQLite so it may be that it's not possible to obscure this.

[marcus-kempe]

Well, I'd argue this is not an issue. If you are able to solve the mystery this way, solving it the intended way should be trivial.

It's rather a good third challenge, finding both culprits, using a single query, mastering some more advanced SQL concepts:

Spoiler warning

The following query would accomplish this:

WITH RECURSIVE split(content, last, rest) AS (
VALUES('', '', (select sql from sqlite_master where type = 'trigger' and name = 'check_solution'))
UNION ALL
  SELECT 
    CASE WHEN last = "'" 
            THEN
                substr(rest, 1, 1)
            ELSE
                content || substr(rest, 1, 1)
    END,
     substr(rest, 1, 1),
     substr(rest, 2)
  FROM split
  WHERE rest <> ''
)
SELECT 
       name     
FROM 
       split, person
WHERE 
       (last = "'" OR rest ='') AND hex(name) = REPLACE(content, "'",'');```
    
</details>  

[RavindraWiguna]

If this was like CTF game, seeing the trigger's source code to check the answer would be a great solution (I did try this as my first attempt hehe), but this is more of a fun detective game to train/learn sql, so being able to see the answer right away with little modification kinda defeat the purpose.
Maybe keep it as possibility like we can do this after solving the intended way. So when we try to query the trigger, it give the result like " Haha, nice one detective, it is indeed a fast way to solve this mystery, but hey you are the greatest detective!, no need to cheat, use your SQL and BrainPower!. You can do this after solving the mystery"

[Pyrolistical]

even tho the names are hex obfuscated the rest of the text isn't. could the text be base64 encoded in the trigger?