1

Title is a bit weird, but I am not quite sure how to formulate this problem quite right. Maybe this is why I cannot find the solution...?

I am currently crawling through a SQLite database which has the following format:

filename id name value
orig_db_1.db 1 A_USERNAME_VAR user
orig_db_1.db 1 A_PASSWORD_VAR password
orig_db_1.db 1 SOME_OTHER_VAR helloworld
orig_db_1.db 2 A_USERNAME_VAR user
orig_db_1.db 3 A_PASS_VAR password
orig_db_2.db 12 A_USER_VAR user
orig_db_2.db 12 A_PASS_VAR password
orig_db_2.db 12 SOMETHING_DIFFERENT 12345
orig_db_3.db 42 IDK_WHAT_TO_TYPE_HERE idksomething

Intuitively, let's say the goal is to find out if someone has exposed their full logins here. This means for the same filename and ID, someone has both defined a username variable, and a password variable, which is bad and we want to analyze more closely by looking at the rest of the rows and trying to identify which piece of software this is.

In SQL terms, my goal is to find a SQL query, which returns all rows for a combination of filename + ID, if under this combination there is one row with a name containing "USER" (LIKE '%USER%'), and a row with a name containing "PASS" (LIKE '%PASS%').

For the same example, this is what I want as a result:

filename id name value
orig_db_1.db 1 A_USERNAME_VAR user
orig_db_1.db 1 A_PASSWORD_VAR password
orig_db_1.db 1 SOME_OTHER_VAR helloworld
orig_db_2.db 12 A_USER_VAR user
orig_db_2.db 12 A_PASS_VAR password
orig_db_2.db 12 SOMETHING_DIFFERENT 12345

How could I do this in a SQLite compatible query?

2 Answers 2

0
  1. Find id+filename which have passwords
  2. Find id+filename which have usernames
  3. Inner join with the original table to filter out those rows that have no corresponding values in passwordss and usernames

Something along these lines:

with passwords as (
  select filename, id from files
  where name like '%PASS%'
  group by filename, id
),
usernames as (
  select filename, id from files
  where name like '%USER%'
  group by filename, id
)
select files.* from files
inner join passwords using(filename, id)
inner join usernames using(filename, id);
5
  • Thanks! This looks quite good, however it has the issue multiple entries matching %PASS% cause the second condition to never be actually required due to the count(*) > 2 being the final condition. Any idea how I could modify the querz so that both issues are actually required.? So multiple %PASS% matches but no %USER% matches are not selected? Sorry, I should have specified this earlier...
    – Nirusu
    Dec 27, 2021 at 10:03
  • Are you saying that A_PASS + B_USER isn't a problem and shouldn't be returned by this query? Only records like A_PASS + A_USER are considered suspecious? Dec 27, 2021 at 10:09
  • Not quite. I was saying the query above returns when A_USER, B_USER and C_USER exist, yet there being no A_PASS, B_PASS or C_PASS, as the condition is count > 2 and multiple USER entries alone would fulfill this. So at least one *_USER and *_PASS should exist, but for simplicity a combination such as A_USER and B_PASS can count as suspicious. That can be handled manually.
    – Nirusu
    Dec 27, 2021 at 10:19
  • Got it. Updated, I think this should work. Dec 27, 2021 at 12:12
  • Yep, this is what I need exactly and seems to do the job. Thank you very much!
    – Nirusu
    Dec 27, 2021 at 15:31
0

In modern versions of SQLite 3.25+, you can do this very efficiently with window functions. No self-joins needed.

SELECT
  filename,
  id,
  name,
  value
FROM (
    SELECT *,
      COUNT(CASE WHEN name LIKE '%USER%' THEN 1 END) OVER (PARTITION BY id) usr,
      COUNT(CASE WHEN name LIKE '%PASS%' THEN 1 END) OVER (PARTITION BY id) pass
    FROM files
) f
WHERE usr > 0
  AND pass > 0;

db<>fiddle

1
  • Needs to be PARTITION BY filename, id to respect the combination of filename and id, but can confirm this works, too! Though the self-join variant seems to query quite a bit faster on my machine.
    – Nirusu
    Dec 27, 2021 at 19:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.