Windows.Forensics.FileZilla

This artifact enumerate’s all user directories on a system and will parse three files within a users AppData\Roaming\FileZilla directory: filezilla.xml, recentservers.xml, and queue.sqlite3

The three files provide valuable data to incident responders if data was exfiltrated using FileZilla.

• filezilla.xml - contains saved user settings
• recentservers.xml - contains recently accessed servers
• queue.sqlite3 - contains multiple tables that can be used to identify what files were exfiltrated and to where (remote hostname and file path).

Using the sqlite() plugin, VR will parse user’s queue.sqlite3 file and join data from various tables.

You can read more about filezilla.xml and recentservers.xml forensic artifacts here: https://www.hecfblog.com/2013/09/daily-blog-93-filezilla-artifacts.html

The queue.sqlite3 does not have much documentation out there that I could find. However, it is a sqlite database that contains 5 tables: files, local_paths, remote_paths, servers, and sqlite_sequence that provide valuable information to incident responders and shed light on what data was exfiltrated by a threat actor.

name: Windows.Forensics.FileZilla
description: |
   This artifact enumerate's all user directories on a system and will
   parse three files within a users AppData\Roaming\FileZilla
   directory: filezilla.xml, recentservers.xml, and queue.sqlite3
   
   The three files provide valuable data to incident responders if data was
   exfiltrated using FileZilla.
   
   - filezilla.xml - contains saved user settings
   - recentservers.xml - contains recently accessed servers
   - queue.sqlite3 - contains multiple tables that can be used to identify what
   files were exfiltrated and to where (remote hostname and file path).
   
   Using the sqlite() plugin, VR will parse user's queue.sqlite3 file and
   join data from various tables.
   
   You can read more about filezilla.xml and recentservers.xml forensic
   artifacts here: 
   https://www.hecfblog.com/2013/09/daily-blog-93-filezilla-artifacts.html
   
   The queue.sqlite3 does not have much documentation out there that I could
   find. However, it is a sqlite database that contains 5 tables: files,
   local_paths, remote_paths, servers, and sqlite_sequence that provide 
   valuable information to incident responders and shed light on what data
   was exfiltrated by a threat actor.
   
author: "Dan Kelly - @dan_kelly17"

# Can be CLIENT, CLIENT_EVENT, SERVER, SERVER_EVENT
type: CLIENT

parameters:
   - name: FileZillaGlob
     default: \AppData\Roaming\FileZilla\
     
   - name: queueSQLQuery
     default: |
        SELECT local_paths.path AS PATH, files.source_file AS File, servers.host FROM files JOIN local_paths ON local_paths.id = files.local_path JOIN servers ON servers.id = files.server
     
   - name: userRegex
     default: .
     type: regex

precondition: 
    SELECT OS FROM info() WHERE OS = 'windows'

sources:
  - name: FileZilla
    query: |
      -- get the filezilla.xml file
      LET filezilla_xml = SELECT * from foreach(
          row={
             SELECT Uid, Name AS User,
                    expand(path=Directory) AS HomeDirectory
             FROM Artifact.Windows.Sys.Users()
             WHERE Name =~ userRegex
          },
          query={
             SELECT 
                User, 
                OSPath, 
                parse_xml(file=OSPath).FileZilla3.Settings.Setting.Tabs.Tab as Tab,
                Mtime
             FROM glob(globs=FileZillaGlob + 'filezilla.xml', root=HomeDirectory)
          })
      
      SELECT * FROM foreach(row=filezilla_xml,
        query={
            SELECT 
                *, 
                OSPath AS SourceFilePath
                FROM foreach(row=Tab, query={
                    SELECT * FROM _value
                })
            })

  - name: RecentServers
    query: |
      LET recentservers_xml = SELECT * from foreach(
          row={
             SELECT Uid, Name AS User,
                    expand(path=Directory) AS HomeDirectory
             FROM Artifact.Windows.Sys.Users()
             WHERE Name =~ userRegex
          },
          query={
             SELECT 
                User, 
                OSPath, 
                parse_xml(file=OSPath).FileZilla3.RecentServers.Server as Server,
                Mtime
             FROM glob(globs=FileZillaGlob + 'recentservers.xml', root=HomeDirectory)
          })
          
      SELECT * FROM foreach(row=recentservers_xml,
        query={
            SELECT 
            *,
            OSPath AS SourceFilePath
            FROM foreach(row=Server, query={
                SELECT * FROM _value
            })
        })
        
  - name: Queue_SQLITE3
    query: |
      LET queue_sqlite = SELECT * from foreach(
          row={
             SELECT Uid, Name AS User,
                    expand(path=Directory) AS HomeDirectory
             FROM Artifact.Windows.Sys.Users()
             WHERE Name =~ userRegex
          },
          query={
             SELECT 
                User, 
                OSPath, 
                Mtime
             FROM glob(globs=FileZillaGlob + 'queue.sqlite3', root=HomeDirectory)
          })
      
      SELECT * FROM foreach(row=queue_sqlite,
        query={
            SELECT 
            *,
            OSPath as SourceFilePath
            FROM sqlite(file=OSPath, query=queueSQLQuery)
        })