nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How do you reverse search attachment in Confluence?

Leonard Hu

We're getting alerts that some file is insecure from our scanner but before we delete it, we'd like to see on Confluence what the attachment actually is.

https://confluence.atlassian.com/doc/hierarchical-file-system-attachment-storage-704578486.html

Is there any way to reverse mod or at least retrieve the space ID from the file path so we can view/find the attachment on the site?

Accepted answers

Davin Studer

This was a fun one to figure out. You can query that from the database. Here is the SQL to get the file path from the CONTENT table.

select
	'/ver003'
	+ '/' + cast(right(SPACEID,3) % 250 as varchar)
	+ '/' + cast(left(right(SPACEID,6),3) % 250 as varchar)
	+ '/' + cast(SPACEID as varchar)
	+ '/' + cast(right(PAGEID,3) % 250 as varchar)
	+ '/' + cast(left(right(PAGEID,6),3) % 250 as varchar)
	+ '/' + cast(PAGEID as varchar)
	+ '/' + case when PREVVER is null then cast(CONTENTID as varchar) else cast(PREVVER as varchar) end
	+ '/' + cast([VERSION] as varchar) as FILEPATH,
	*
from [dbo].[CONTENT]
where CONTENTTYPE = 'ATTACHMENT'

All comments

Davin Studer

This was a fun one to figure out. You can query that from the database. Here is the SQL to get the file path from the CONTENT table.

select
	'/ver003'
	+ '/' + cast(right(SPACEID,3) % 250 as varchar)
	+ '/' + cast(left(right(SPACEID,6),3) % 250 as varchar)
	+ '/' + cast(SPACEID as varchar)
	+ '/' + cast(right(PAGEID,3) % 250 as varchar)
	+ '/' + cast(left(right(PAGEID,6),3) % 250 as varchar)
	+ '/' + cast(PAGEID as varchar)
	+ '/' + case when PREVVER is null then cast(CONTENTID as varchar) else cast(PREVVER as varchar) end
	+ '/' + cast([VERSION] as varchar) as FILEPATH,
	*
from [dbo].[CONTENT]
where CONTENTTYPE = 'ATTACHMENT'

Davin Studer

So, if you needed to figure out what a specific file equates to you could run something like this.

select *
from (
	select
		'/ver003'
		+ '/' + cast(right(SPACEID,3) % 250 as varchar)
		+ '/' + cast(left(right(SPACEID,6),3) % 250 as varchar)
		+ '/' + cast(SPACEID as varchar)
		+ '/' + cast(right(PAGEID,3) % 250 as varchar)
		+ '/' + cast(left(right(PAGEID,6),3) % 250 as varchar)
		+ '/' + cast(PAGEID as varchar)
		+ '/' + case when PREVVER is null then cast(CONTENTID as varchar) else cast(PREVVER as varchar) end
		+ '/' + cast([VERSION] as varchar) as FILEPATH,
		*
	from [dbo].[CONTENT]
	where CONTENTTYPE = 'ATTACHMENT'
) as X
where FILEPATH = '{your file path}'

Leonard Hu

Wow that's a really impressive query to reverse search a filepath with a specific file!

Thank you for responding so quickly with a solution, I didn't think it was even doable.

However, I tried running it on my database and I'm having a little bit of an issue with one of the lines, I'm wondering if you know what would be the syntax error?

# mysql database -u root -p < wiki_script
Enter password:
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar)
 + '/' + cast(left(right(SPACEID,6),3) % 250 as varchar)
 + '/' + cas' at line 5

Davin Studer

Ah ... MySQL. I'm on MSSQL. Try this. I converted it with http://www.sqlines.com/online.

select *
from (
 select
  Concat('/ver003'
  , '/' , cast(right(SPACEID,3) % 250 as varchar(10))
  , '/' , cast(left(right(SPACEID,6),3) % 250 as varchar(10))
  , '/' , cast(SPACEID as varchar(10))
  , '/' , cast(right(PAGEID,3) % 250 as varchar(10))
  , '/' , cast(left(right(PAGEID,6),3) % 250 as varchar(10))
  , '/' , cast(PAGEID as varchar(10))
  , '/' , case when PREVVER is null then cast(CONTENTID as varchar(10)) else cast(PREVVER as varchar(10)) end
  , '/' , cast(`VERSION` as varchar(10))) as FILEPATH,
  *
 from CONTENT
 where CONTENTTYPE = 'ATTACHMENT'
) as X
where FILEPATH = '{your file path}'

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

server

confluence-server