File not found!

I’ve seen it before, a customer deletes a file and then needs it restored.

Normally a challenging request, but under special circumstances a process may have the file opened.

While showing my son some fun and exciting Linux security scenarios, I recalled all those times I was able to recover data from the /proc (in memory) filesystem. In order to visualize this scenario I threw together a small Dockerfile and had him poke around:

Once logged in you notice a server.py script, for our scenario this is our companies server daemon. If we browse the code we notice a file named password.txt was opened, but never closed.

Ignore the sleep() function, this is just here to keep the file opened, and to simulate a daemon process.

The next things you should do it verify the server is actively running! If we hope to recover that file the server needs to have a lock on password.txt

root@4df66824ad14:~# ps aux
USER  PID %CPU %MEM VSZ   RSS  TTY    STAT START   TIME COMMAND
root   1  0.0  0.0  18504 3396 pts/0  Ss   01:37   0:00 /bin/bash
root  10  0.0  0.1  22548 6076 pts/0  S    01:37   0:00 python server.py
root  24  0.0  0.0  34396 2904 pts/0  R+   01:45   0:00 ps aux

We are in luck!

From here we can use the lsof command to verify password.txt is still opened:

root@4df66824ad14:~# lsof | grep password.txt
python 10 root 3r REG 8,1 30 15860072 /root/password.txt (deleted)

Bingo! we see the file is opened, and it is marked for deletion. Good thing our service is still running!

All that is left is to track down our service’s process id (we see above it’s process id, or PID is 10).

We now have all the pieces to track down our password file from the in memory filesystem.

The /proc filesystem has a number of useful files, it also includes a directory for every running process (named after the PID).

Within the process directory we have a slew of files; however, the directory we care about is fd (standing for file descriptor):

root@4df66824ad14:~# cd /proc/10
root@4df66824ad14:/proc/10# ls -l fd
total 0
lr-x------ 1 root root 64 Sep 28 01:43 0 -> /dev/null
lrwx------ 1 root root 64 Sep 28 01:43 1 -> /dev/pts/0
lrwx------ 1 root root 64 Sep 28 01:43 2 -> /dev/pts/0
lr-x------ 1 root root 64 Sep 28 01:43 3 -> '/root/password.txt (deleted)'

And just like that we found a file representing our in memory password.txt file!

From here we can show its contents, or even restore it using cp:

root@4df66824ad14:/proc/10# cat fd/3
the-most-secret-password-ever

Happy hacking!

MySQL IN Operator Not Using Index

After being alerted for elevated cpu on a staging mysql instance, I logged into the mysql console and ran a SHOW PROCESS LIST:

SHOW PROCESSLIST\G
*************************** 1. row ***************************
     Id: 1
   User: my_user
   Host: x.x.x.x:xxx
     db: my_db
Command: Sleep
   Time: 2
  State:
   Info: NULL
*************************** 2. row ***************************
     Id: 3
   User: my_user
   Host: x.x.x.x:xxx
     db: my_db
Command: Query
   Time: 786
  State: Sending data
   Info: SELECT file_id, fs_file_id
FROM my_data
WHERE file_id IN ('123456', 123457);

A query running for 786 seconds quickly caught my eye, my next course of action was to run an EXPLAIN:

EXPLAIN SELECT file_id, fs_file_id FROM my_data WHERE file_id IN ('123456', 123457)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: my_data
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 131144420
        Extra: Using where
1 row in set (0.00 sec)

The EXPLAIN confirmed my suspicions, this query was not using an index, and ultimately caused a full table scan.

I was expecting to not find an index for the file_id field, but as you can see this field has an index by being a PRIMARY KEY:

SHOW CREATE TABLE my_data\G
*************************** 1. row ***************************
Table: my_data
Create Table: CREATE TABLE `my_data` (
`file_id` int(10) unsigned NOT NULL,
`fs_file_id` binary(16) DEFAULT NULL,
PRIMARY KEY (`file_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

Something I didn’t immediately identify earlier was the mixed types in my IN operator‘s list, they contained both integers and strings:

file_id IN ('123456', 123457)

Once I noticed the mixed type I began debugging, I finally learned the IN operator works great with sets of integers:

EXPLAIN SELECT file_id, fs_file_id FROM my_data WHERE file_id IN (123456, 123457)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: my_data
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 2
        Extra: Using where
1 row in set (0.00 sec)

It even works with sets of strings:

EXPLAIN SELECT file_id, fs_file_id FROM my_data WHERE file_id IN ('123456', '123457')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: my_data
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 2
        Extra: Using where
1 row in set (0.00 sec)

But as we observed from the begining, the IN operator doesn’t act as expected with mixed strings and integers.