Summary

After upgrading Confluence on Microsoft Windows, Confluence is unable to display images that have a colon character (':') in the attachment file name.

Environment

The following Confluence versions running on Microsoft Windows operating system are affected:

• Confluence 7.11.6
• Confluence 7.12.1 and later

Diagnosis

Attachments on a Confluence page containing a colon character (':') in their file name will display a broken image on the View page and on the Edit page:

The application log file atlassian-confluence.log  will display the following stack trace error:

NTFS ADS separator (':') in file name is forbidden.

2023-12-01 20:02:26,123 ERROR [http-nio-8090-exec-1] [[Standalone].[localhost].[/].[file-server]] log Servlet.service() for servlet [file-server] in context with path [] threw exception
java.lang.IllegalArgumentException: NTFS ADS separator (':') in file name is forbidden.
	at org.apache.commons.io.FilenameUtils.indexOfExtension(FilenameUtils.java:955)
	at org.apache.commons.io.FilenameUtils.getExtension(FilenameUtils.java:614)
	at org.apache.commons.io.FilenameUtils.isExtension(FilenameUtils.java:1033)
	at com.atlassian.confluence.util.AttachmentMimeTypeTranslator$CSVMimeTypeTranslationStrategy.handles(AttachmentMimeTypeTranslator.java:149)
	at com.atlassian.confluence.util.AttachmentMimeTypeTranslator.resolveMimeType(AttachmentMimeTypeTranslator.java:182)
	at com.atlassian.confluence.servlet.download.DefaultAttachmentSafeContentHeaderGuesser.computeAttachmentHeaders(DefaultAttachmentSafeContentHeaderGuesser.java:51)
	at com.atlassian.confluence.servlet.download.AttachmentDownload.getHeadersForAttachment(AttachmentDownload.java:265)
	at com.atlassian.confluence.servlet.download.AttachmentDownload.setHeadersForAttachment(AttachmentDownload.java:247)
	at com.atlassian.confluence.servlet.download.AttachmentDownload.sendResponseHeaders(AttachmentDownload.java:155)
	at com.atlassian.confluence.servlet.download.AttachmentDownload.getStreamForDownload(AttachmentDownload.java:109)
	at com.atlassian.confluence.servlet.download.ServeAfterTransactionDownload$StreamResultCallback.doInTransaction(ServeAfterTransactionDownload.java:122)
	at com.atlassian.confluence.servlet.download.ServeAfterTransactionDownload$StreamResultCallback.doInTransaction(ServeAfterTransactionDownload.java:105)
	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
	at com.atlassian.confluence.servlet.download.ServeAfterTransactionDownload.getStreamInTransaction(ServeAfterTransactionDownload.java:41)
	at com.atlassian.confluence.servlet.download.ServeAfterTransactionDownload.serveFile(ServeAfterTransactionDownload.java:47)
...

Run the following SQL to confirm the number of attachments that contain a colon character (':'):

select count(*) from CONTENT where CONTENTTYPE = 'ATTACHMENT' and LOWERTITLE like '%:%';

Cause

Confluence versions that bundle Apache commons IO library newer than commons-io-2.6.jar enforces file names on Windows Operating Systems cannot contain the colon (':') character.

Resolution

We will need to perform the following directly in the Confluence database:

1. Rename all attachment filenames in the CONTENT table to not contain the (':') character; and
2. Update all pages to point to the updated attachment filenames without the (':') character in the BODYCONTENT  table.

The steps to perform the above are detailed in the below four stages.

Stage 1 - Shutdown Confluence

1. Whilst Confluence is still running, navigate to Confluence Administration » General Configuration » Collaborative Editing » Disable Collaborative Editing (if Collaborative Editing is enabled)
2. Shutdown Confluence
   1. If Confluence is running as a cluster, shut down Confluence on every node
3.  Take a backup of the Confluence database as the below steps will be making direct SQL updates to the Confluence database!

Stage 2 - SQL file preparation

1. Perl is required to run the below attached fixfilename.pl  script file.
   1. Download free Strawberry Perl for Microsoft Windows from https://strawberryperl.com/releases.html:
      1. The latest 64 bit portable edition is fine. e.g. v5.38.2.2 Portable 64-bit
   2. Extract to C:\strawberry-perl-5.38.2.2-64bit-portable
2. Download this fixfilename.pl Perl script into C:\strawberry-perl-5.38.2.2-64bit-portable

3. Work with your DBA to create a CSV text file named content.csv from the Confluence database from this SQL:

   select CONTENTID, CONTENTTYPE, TITLE, LOWERTITLE FROM CONTENT where LOWERTITLE like '%:%' and CONTENTTYPE = 'ATTACHMENT';

   Sample file contents:

   CONTENTID,CONTENTTYPE,TITLE,LOWERTITLE
   100123,ATTACHMENT,image2013-9-11 12:58:01.png,image2013-9-11 12:58:01.png
   100124,ATTACHMENT,image2013-9-10 11:52:03.png,image2013-9-10 11:52:03.png
   100125,ATTACHMENT,image2013-9-10 13:50:08.png,image2013-9-10 13:50:08.png
   100126,ATTACHMENT,image2013-9-10 12:0:59.png,image2013-9-10 12:0:59.png
   ...

   1. Field delimiter must be a comma in the CSV file extract

4. Create a second CSV text file named bodycontent.csv from the Confluence database from this SQL:

   select 'ROWSTART', BODYCONTENT.* from BODYCONTENT where BODY like '%ri:attachment ri:filename="%:%' OR BODY like '%<ri:url ri:value="%/download/attachments%';

   Sample file contents:

   ROWSTART,BODYCONTENTID,BODY,CONTENTID,BODYTYPEID
   ROWSTART,720899,"<p>......</p>",90045,2
   ROWSTART,820200,"<p>......</p>",80012,2
   ROWSTART,610305,"<p>...
   ...</p>",70008,2
   ...

   1. Field delimiter must be a comma in the CSV file extract
5. Save both the created content.csv and bodycontent.csv into the same folder where fixfilename.pl was saved to

6. Open a command prompt and change to the directory where fixfilename.pl, content.csv and bodycontent.csv are saved, e.g.

   cd /d C:\strawberry-perl-5.38.2.2-64bit-portable

7. Run the fixfilename.pl script to see the script Usage screen:

   C:\strawberry-perl-5.38.2.2-64bit-portable> C:\strawberry-perl-5.38.2.2-64bit-portable\perl\bin\perl fixfilename.pl
   Usage  : fixfilename.pl [-d mssql|postgres|mysql|oracle] <CONTENT_TABLE_CSV> <BODYCONTENT>
   
   Options:
      -d <db_type>              mssql    - generates SQL for Microsoft SQL Server (default)
                                postgres - generates SQL for Postgres
                                mysql    - generates SQL for MySQL
                                oracle   - generates SQL for Oracle
   
   Description:
   This script will produce SQL UPDATES to clean up BODYCONTENT tables for attachments containing ':' character
   This script should only be run as per guidance from Atlassian Support team.

8. Run the fixfilename.pl for your respective Confluence DB engine type against the content.csv and bodycontent.csv file set to generate the UPDATE SQL statements.

   1. E.g. for Microsoft SQL Server database engine (default mode), simply run:

      C:\strawberry-perl-5.38.2.2-64bit-portable\perl\bin\perl fixfilename.pl content.csv bodycontent.csv > generated_sql.txt

   2. E.g. for an Oracle database engine, run with -d oracle option flag:

      C:\strawberry-perl-5.38.2.2-64bit-portable\perl\bin\perl fixfilename.pl -d oracle content.csv bodycontent.csv > generated_sql.txt

   3. Sample generated_sql.txt output

      reading 'content.csv'... done
         found 2120 unique ATTACHMENT file names with a ':' character
      reading 'bodycontent.csv'... done
         identified 28090 total SQL updates needed for embedded images
         identified 2306 total SQL updates needed for external images
      UPDATE BODYCONTENT set BODY=..... where BODYCONTENTID = X;
      UPDATE BODYCONTENT set BODY=..... where BODYCONTENTID = 100456;
      UPDATE BODYCONTENT set BODY=..... where BODYCONTENTID = 100789;
      ...

   4. We're most interested in the UPDATE BODYCONTENT... generated rows.
   5. The generated SQL statements are specific to your Confluence data set at the time the CSV files were generated.
   6. If the fixfilename.pl script throws an error, check that both CSV files are in the same format as the above sample CSV files.
   7. If the fixfilename.pl script still fails to run successfully, please contact Atlassian Support.

Stage 3 - Performing the SQL update

1. Copy the generated UPDATE BODYCONTENT...  SQL lines from the above generated_sql.txt and run against the Confluence database to update the BODYCONTENT table:

   ... <Paste and run the generated SQL UPDATE statements>...
   e.g.
   update BODYCONTENT set BODY = cast(REPLACE(cast(BODY as....;
   update BODYCONTENT set BODY = cast(REPLACE(cast(BODY as....;
   ...

   1. Run this in chunks of roughly 10,000 rows
   2. Make sure no errors are returned

   
   

2. Now, run this SQL to update the CONTENT table in the Confluence database:

   update CONTENT set TITLE = REPLACE(TITLE, ':', ''), LOWERTITLE= REPLACE(LOWERTITLE, ':', '') FROM CONTENT where CONTENTTYPE = 'ATTACHMENT' and LOWERTITLE like '%:%';

   
   

   1. Make sure no errors are returned

3. Empty out the Synchrony tables in the Confluence database:

   truncate table "EVENTS";
   truncate table "SECRETS";
   truncate table "SNAPSHOTS";

Stage 4 - Start Confluence

1. Backup these Confluence lucene index directories:
   1. <confluence-local-home>/index
   2. <confluence-local-home>/journal
2. Delete the two Confluence lucene index directories:
   1. <confluence-local-home>/index
   2. <confluence-local-home>/journal
3. Start Confluence
4. Navigate to Confluence Administration » General Configuration » Collaborative Editing » Enable Collaborative Editing (if Collaborative Editing was disabled in Stage (1) / Step (1) above)
5. Navigate to Confluence Administration » General Configuration » Content Indexing » Rebuild the indexes
6. Check all pages now show image attachments correctly
7. Feel free to delete C:\strawberry-perl-5.38.2.2-64bit-portable once all is confirmed okay in Confluence