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?

[Davin Studer · Accepted solution]

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}'

[Leonard Hu]

It's not working yet I still get syntax errors, but I will try and figure out the mysql piece and get back to you if I can figure something out.  

I'm trying to clear out the query to see where the syntax is happening and got this message but it still errors out at 'varchar(10) as FILEPATH'.  

select *
from (
 select
 Concat('/ver003', '/', cast(`VERSION` as varchar(10)) as FILEPATH, *
 from CONTENT
 where CONTENTTYPE = 'ATTACHMENT'
) as X
where FILEPATH = '/path/1';

[Davin Studer]

Hmm. Maybe that's not how you do a cast in MySQL.

[Davin Studer]

Oh, it's because MySQL doesn't have the VARCHAR data type. Has to be a CHAR. What about something like this them. I'd test it, but I got no MySQL DB to test against.

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

[Leonard Hu]

Cool that makes sense, I also just saw that it didn't support VARCHAR for casting. I think we're getting somewhere, I just retried and that specific error message disappeared but I'm getting a new syntax error for the filepath line 

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 '*
 from CONTENT
 where CONTENTTYPE = 'ATTACHMENT'
) as X
where FILEPATH = "/data' at line 13 

 1 select *
 2 from (
 3 select
 4 replace(concat('/ver003'
 5 , '/' , cast(right(SPACEID,3) % 250 as char(10))
 6 , '/' , cast(left(right(SPACEID,6),3) % 250 as char(10))
 7 , '/' , cast(SPACEID as char(10))
 8 , '/' , cast(right(PAGEID,3) % 250 as char(10))
 9 , '/' , cast(left(right(PAGEID,6),3) % 250 as char(10))
 10 , '/' , cast(PAGEID as char(10))
 11 , '/' , case when PREVVER is null then cast(CONTENTID as char(10)) else cast(PREVVER as char(10)) end
 12 , '/' , cast(`VERSION` as char(10))), ' ', '') as FILEPATH,
 13 *
 14 from CONTENT
 15 where CONTENTTYPE = 'ATTACHMENT'
 16 ) as X
 17 where FILEPATH = '/path';

I'm wondering what's wrong with the *, I tried removing line 13 and there was no more syntax but I think we need it though. 

Thanks for your help! 

[Davin Studer]

Maybe it needs the table specified.

CONTENT.*

[Leonard Hu]

Ah I finally figured it out! That content.* worked. I also ended up having to check the database and update the filepath as it wasn't the filepath on the server but another path based off the /ver003 path.

Below is the valid working format for us. Thanks a lot Davin!

select *
from (
 select
 replace(concat('/ver003'
 , '/' , cast(right(SPACEID,3) % 250 as char(10))
 , '/' , cast(left(right(SPACEID,6),3) % 250 as char(10))
 , '/' , cast(SPACEID as char(10))
 , '/' , cast(right(PAGEID,3) % 250 as char(10))
 , '/' , cast(left(right(PAGEID,6),3) % 250 as char(10))
 , '/' , cast(PAGEID as char(10))
 , '/' , case when PREVVER is null then cast(CONTENTID as char(10)) else cast(PREVVER as char(10)) end
 , '/' , cast(`VERSION` as char(10))), ' ', '') as FILEPATH,
 CONTENT.*
 from CONTENT
 where CONTENTTYPE = 'ATTACHMENT'
) as X
where FILEPATH = '/ver003/45/224/79724545/227/194/89194977/103055526/1';

[Davin Studer]

Oh good. Glad that worked.