Image attachments with a colon character (':') do not load after upgrading Confluence

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:

Embedded Spreadsheet macro from the Table Filter, Charts & Spreadsheets for Confluence app show "Attachment not found" message on the View page or the attempt to Edit spreadsheet:

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

log sample 1

log sample 2

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

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.

Solution

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 mentioned fixfilename.pl script file.

   1. Download free Strawberry Perl for Microsoft Windows from https://strawberryperl.com/releases.html:

      1. The latest 64 bit portableedition is fine. e.g. v5.38.2.2 Portable 64-bit

   2. Extract to C:\strawberry-perl-5.38.2.2-64bit-portable

2. Copy the fixfilename.pl Perl script into C:\strawberry-perl-5.38.2.2-64bit-portable

   fixfilename.pl

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 #!/usr/bin/perl # ####################################################################### # Author: Eric Lam # Created Date: 23/11/2023 # Product: fixfilename.pl # Version: 0.8 # Updated: 8 Jan 2025 # Description: Clean up filename with ":" characters # # ####################################################################### use strict; use Getopt::Std; # ####################################################################### # Global Variables my $MODE_SQL_SERVER = 0; my $MODE_POSTGRES = 1; my $MODE_MYSQL = 2; my $MODE_ORACLE = 3; my $mode_sql_g = $MODE_SQL_SERVER; my %content_attachment_fname_g = (); my $attachmentname_regex_sg = 'image'; my $attachmentname_regex1_sg = 'tfss'; my $total_embedded_matches_g=0; my $total_external_matches_g=0; my $total_stiltsoft_spreadsheet_matches_g=0; my %body_content_id_g = (); my $fix_stiltsoft_spreadsheet_macro_g = 1; # ####################################################################### # Functions sub Usage { print "Usage : $0 [-d mssql|postgres|mysql|oracle] [-S] <CONTENT_TABLE_CSV> <BODYCONTENT>\n" . "\n" . "Options:\n" . " -d <db_type> mssql - generates SQL for Microsoft SQL Server (default)\n" . " postgres - generates SQL for Postgres\n" . " mysql - generates SQL for MySQL\n" . " oracle - generates SQL for Oracle\n" . "\n" . " -S Skip fixing Stiltsoft spreadsheet macro file names containing colon characters (enabled by default)\n" . "\n" . "Description:\n" . "This script will produce SQL UPDATES to clean up BODYCONTENT tables for attachments containing ':' character\n" . "This script will produce SQL UPDATES to clean up BODYCONTENT tables for Stiltsoft spreadsheet macro file names containing ':' character\n" . "This script should only be run as per guidance from Atlassian Support team.\n"; exit 1; } sub SearchLineForAttachment { my ($bodycontentid, $l) = @_; #if ($l =~ m|^"| && $l =~ m|"$|) #{ # $l =~ s|^"||; $l =~ s|"$||; # $l =~ s|""|"|g; #} ## print "$l\n"; # #print "LINE LENGTH: " . length ($l) . "\n"; my $found_fname_colon_char = 0; while ($l =~ m|<ri:attachment ri:filename="+([^"]+)"+|g) # " { my $fname = $1; #print "** $bodycontentid, $fname\n"; next unless $fname =~ m|^$attachmentname_regex_sg|; # improvise code my $fnamestripped = $fname; $fnamestripped =~ s|:||g; next unless $fname =~ m|:| || exists $content_attachment_fname_g{$fnamestripped}; my $fname_prestripped = $fname; if ($fname !~ m|:| && exists $content_attachment_fname_g{$fnamestripped}) { $fname_prestripped = $content_attachment_fname_g{$fnamestripped}; # print "MAPPING '$fnamestripped' back to pre stripped '$fname_prestripped'\n"; } die "$fname :: '$fname_prestripped'" if $fname_prestripped !~ m|:|; next if exists $body_content_id_g{'embedded'}->{$bodycontentid} && exists $body_content_id_g{'embedded'}->{$bodycontentid}->{$fname_prestripped}; $body_content_id_g{'embedded'}->{$bodycontentid}->{$fname_prestripped} = $fnamestripped; $found_fname_colon_char++; # print "$bodycontentid ------- $fname ($fnamestripped)\n"; # print "$bodycontentid - $fname\n"; ++$total_embedded_matches_g; } while ($l =~ m|<ri:url ri:value="+https?://[^/]+(?:/[^/]+)?(/download/attachments/\d+/[^"\?]+)|g) # " { my $fname = $1; next unless $fname =~ m|\%3A|i; #print "** $bodycontentid, $fname\n"; my $fnamestripped = $fname; $fnamestripped =~ s|\%3A||ig; next if exists $body_content_id_g{'external'}->{$bodycontentid} && exists $body_content_id_g{'external'}->{$bodycontentid}->{$fname}; $body_content_id_g{'external'}->{$bodycontentid}->{$fname} = $fnamestripped; $found_fname_colon_char++; ++$total_external_matches_g; } # fix up stiltsoft spreadsheet attachment macro name containing colons (prior to Stilitsoft v8.2.3) while ($l =~ m|<ac:parameter ac:name="+attachment"+>([^<]+)</ac:parameter>|g) { my $fname = $1; die "Error: unexpected colon where filename was not $attachmentname_regex1_sg: $fname" if $fname =~ m|:| && $fname !~ m|$attachmentname_regex1_sg$|i; next unless $fname =~ m|$attachmentname_regex1_sg$|i; my $fnamestripped = $fname; $fnamestripped =~ s|:||g; next unless $fname =~ m|:| || exists $content_attachment_fname_g{$fnamestripped}; #print "** $bodycontentid, $fname\n"; next if exists $body_content_id_g{'stiltsoft_spreadsheet'}->{$bodycontentid} && exists $body_content_id_g{'stiltsoft_spreadsheet'}->{$bodycontentid}->{$fname}; $body_content_id_g{'stiltsoft_spreadsheet'}->{$bodycontentid}->{$fname} = $fnamestripped; $found_fname_colon_char++; ++$total_stiltsoft_spreadsheet_matches_g; } # die "$bodycontentid" if $found_fname_colon_char ==0; } # ####################################################################### # main # auto flush output select STDERR; $|=1; select STDOUT; $|=1; # Check args Usage () if @ARGV == 1 && $ARGV[0] eq '--help'; Usage () if @ARGV == 1 && $ARGV[0] eq '-h'; @ARGV or Usage(); my %opts; Getopt::Std::getopts('d:S', \%opts) or Usage(); if (exists $opts{'d'} && defined $opts{'d'}) { my $dbtype = $opts{'d'}; $dbtype =~ s|[^A-Za-z]||g; if ($dbtype =~ m|^mssql$|i) { $mode_sql_g = $MODE_SQL_SERVER; } elsif ($dbtype =~ m|^postgres$|i) { $mode_sql_g = $MODE_POSTGRES; } elsif ($dbtype =~ m|^mysql$|i) { $mode_sql_g = $MODE_MYSQL; } elsif ($dbtype =~ m|^oracle$|i) { $mode_sql_g = $MODE_ORACLE; } else { warn "Error: unknown database type '$dbtype'!\n"; Usage(); } } $fix_stiltsoft_spreadsheet_macro_g = 0 if exists $opts{'S'} && defined $opts{'S'}; my ($content_csv_fname, $body_content_csv_fname) = @ARGV; # read CONTENT table first { print "reading '$content_csv_fname'... "; open my $fh, "<$content_csv_fname" or die "error: could not open file '$content_csv_fname' for reading: $!\n"; my $lineco=0; while (defined (my $l = <$fh>)) { $lineco++; # strip out leading BOM characters my $bom = chr(239) . chr(187) . chr(191); $l =~ s|^$bom||; $l =~ s|[\r\n\s]+$||s; next if $l =~ m|^"?CONTENT|; #" next if $l =~ m|^SQL>|; next unless length $l; if ($l =~ m|^(\d+)[\t,]"?ATTACHMENT"?[\t,]([^\t]+)[\t,]|) { my ($contentid, $title) = ($1, $2); next unless $title =~ m|:|; my $titlestripped = $title; $titlestripped =~ s|:||g; next if exists $content_attachment_fname_g{$titlestripped}; # print "== $contentid, $title / $titlestripped == \n"; $content_attachment_fname_g{$titlestripped} = $title; } else { die "error: unexpected tab delimited row received on '$content_csv_fname' / Line number $lineco:\n'$l'\n"; } } close $fh; print "done\n"; } print " found " . scalar(keys %content_attachment_fname_g) . " unique ATTACHMENT file names with a ':' character\n"; # read BODYCONTENT table next #if (scalar(keys %content_attachment_fname_g)) { print "reading '$body_content_csv_fname'... "; open my $fh, "<$body_content_csv_fname" or die "error: could not open file '$body_content_csv_fname' for reading: $!\n"; my $lineco=0; my $last_seen_bodycontentid = -1; my $last_seen_line_full = ''; my $foundRowStart = 0; while (defined (my $l = <$fh>)) { $lineco++; # strip out leading BOM characters my $bom = chr(239) . chr(187) . chr(191); $l =~ s|^$bom||; next if $l =~ m|^"?CONTENT|; next if $l =~ m|BODYTYPEID"?[\r\n\s]*$|s; next if $l =~ m|^"?'ROWSTART'"?|; next if $l =~ m|^SQL>|; next unless length $l; if ($foundRowStart == 0) { next if $l =~ m|^[\r\n\s]*$|s; } if ($l =~ m|^"?ROWSTART"?,(\d+)|) { my $bodycontentid = $1; $foundRowStart = 1; if (length $last_seen_line_full) { SearchLineForAttachment($last_seen_bodycontentid, $last_seen_line_full); } $last_seen_bodycontentid = $bodycontentid; $last_seen_line_full = $l; } elsif ($last_seen_bodycontentid != -1) { # append line $last_seen_line_full .= $l; } else { die "error: unexpected line that did not start with ROWSTART on '$body_content_csv_fname' / Line number $lineco:'\n'$l'\n"; } } close $fh; if (length $last_seen_line_full) { SearchLineForAttachment($last_seen_bodycontentid, $last_seen_line_full); $last_seen_line_full = ''; } print "done\n"; print " identified $total_embedded_matches_g total SQL updates needed for embedded images\n"; print " identified $total_external_matches_g total SQL updates needed for external images\n"; print " identified $total_stiltsoft_spreadsheet_matches_g total SQL updates needed for stiltsoft spreadsheet macro file names"; print " (skipped)" if $total_stiltsoft_spreadsheet_matches_g > 0 && !$fix_stiltsoft_spreadsheet_macro_g; print "\n"; } if ($total_embedded_matches_g || $total_external_matches_g || $total_stiltsoft_spreadsheet_matches_g) { if (exists $body_content_id_g{'embedded'}) { for my $bid (sort keys %{$body_content_id_g{'embedded'}}) { for my $fname (sort keys %{$body_content_id_g{'embedded'}->{$bid}}) { my $stripped_fname = $body_content_id_g{'embedded'}->{$bid}->{$fname}; die "-- $bid / $fname" if $fname !~ m|:|; if ($mode_sql_g == $MODE_SQL_SERVER) { # SQL Server print "UPDATE BODYCONTENT set BODY=cast(REPLACE(cast(BODY as nvarchar(max)), '<ri:attachment ri:filename=\"" . $fname . "\"', '<ri:attachment ri:filename=\"$stripped_fname\"') as ntext) where BODYCONTENTID = $bid;\n"; } elsif ($mode_sql_g == $MODE_MYSQL || $mode_sql_g == $MODE_ORACLE) { # MySQL or Oracle 19c print "UPDATE BODYCONTENT set BODY=REPLACE(BODY, '<ri:attachment ri:filename=\"" . $fname . "\"', '<ri:attachment ri:filename=\"$stripped_fname\"') where BODYCONTENTID = $bid;\n"; } elsif ($mode_sql_g == $MODE_POSTGRES) { # Postgres (**************** UNVERIFIED *****************) print "UPDATE BODYCONTENT set BODY=REPLACE(cast(BODY as TEXT), '<ri:attachment ri:filename=\"" . $fname . "\"', '<ri:attachment ri:filename=\"$stripped_fname\"') where BODYCONTENTID = $bid;\n"; } } } } if (exists $body_content_id_g{'external'}) { for my $bid (sort keys %{$body_content_id_g{'external'}}) { for my $fname (sort keys %{$body_content_id_g{'external'}->{$bid}}) { my $stripped_fname = $body_content_id_g{'external'}->{$bid}->{$fname}; die "-- $bid / $fname" if $fname !~ m|\%3A|i; if ($mode_sql_g == $MODE_SQL_SERVER) { # SQL Server print "UPDATE BODYCONTENT set BODY=cast(REPLACE(cast(BODY as nvarchar(max)), '" . $fname . "', '$stripped_fname') as ntext) where BODYCONTENTID = $bid;\n"; } elsif ($mode_sql_g == $MODE_MYSQL || $mode_sql_g == $MODE_ORACLE) { # MySQL or Oracle 19c print "UPDATE BODYCONTENT set BODY=REPLACE(BODY, '" . $fname . "', '$stripped_fname') where BODYCONTENTID = $bid;\n"; } elsif ($mode_sql_g == $MODE_POSTGRES) { # Postgres print "UPDATE BODYCONTENT set BODY=REPLACE(cast(BODY as TEXT), '" . $fname . "', '$stripped_fname') where BODYCONTENTID = $bid;\n"; } } } } # only do this part if enabled if ($fix_stiltsoft_spreadsheet_macro_g && exists $body_content_id_g{'stiltsoft_spreadsheet'}) { for my $bid (sort keys %{$body_content_id_g{'stiltsoft_spreadsheet'}}) { for my $fname (sort keys %{$body_content_id_g{'stiltsoft_spreadsheet'}->{$bid}}) { my $stripped_fname = $body_content_id_g{'stiltsoft_spreadsheet'}->{$bid}->{$fname}; die "-- $bid / $fname" if $fname !~ m|:|i; if ($mode_sql_g == $MODE_SQL_SERVER) { # SQL Server print "UPDATE BODYCONTENT set BODY=cast(REPLACE(cast(BODY as nvarchar(max)), '<ac:parameter ac:name=\"attachment\">$fname</ac:parameter>', '<ac:parameter ac:name=\"attachment\">$stripped_fname</ac:parameter>') as ntext) where BODYCONTENTID = $bid;\n"; } elsif ($mode_sql_g == $MODE_MYSQL || $mode_sql_g == $MODE_ORACLE) { # MySQL or Oracle 19c print "UPDATE BODYCONTENT set BODY=REPLACE(BODY, '<ac:parameter ac:name=\"attachment\">$fname</ac:parameter>', '<ac:parameter ac:name=\"attachment\">$stripped_fname</ac:parameter>') where BODYCONTENTID = $bid;\n"; } elsif ($mode_sql_g == $MODE_POSTGRES) { # Postgres print "UPDATE BODYCONTENT set BODY=REPLACE(cast(BODY as TEXT), '<ac:parameter ac:name=\"attachment\">$fname</ac:parameter>', '<ac:parameter ac:name=\"attachment\">$stripped_fname</ac:parameter>') where BODYCONTENTID = $bid;\n"; } } } } } exit 0;

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

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

   Sample file contents:

   1 2 3 4 5 6 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:

   1 select 'ROWSTART', BODYCONTENT.* from BODYCONTENT where BODY like '%ri:attachment ri:filename="%:%' OR BODY like '%<ri:url ri:value="%/download/attachments%' or BODY like '%ac:name="spreadsheet-table"%';

   Sample file contents:

   1 2 3 4 5 6 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.

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

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

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 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] [-S] <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 -S Skip fixing Stiltsoft spreadsheet macro file names containing colon characters (enabled by default) Description: This script will produce SQL UPDATES to clean up BODYCONTENT tables for attachments containing ':' character This script will produce SQL UPDATES to clean up BODYCONTENT tables for Stiltsoft spreadsheet macro file names 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:

      1 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:

      1 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

      1 2 3 4 5 6 7 8 9 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.txtand run against the Confluence database to update the BODYCONTENT table:

   1 2 3 4 5 ... <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:

   1 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:

   1 2 3 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