Error when uploading a file to media store #609

AndreasBilke · 2019-05-08T17:24:16Z

s9y version: 2.3-beta
PHP version: 7.3.4 (debian buster package)
Postgres version: 11.2 (debian buster package)

When I upload a file to the media store, I get the following error site displayed:

== ERROR-REPORT (BETA/ALPHA-BUILDS) ==

Warning: pg_query(): Query failed: ERROR: value too long for type character varying(5) in <...>web/include/db/postgres.inc.php on line 222.
For more details set $serendipity['production'] = 'debug' in serendipity_config_local.inc.php to receive a stack-trace.



Fatal error:  Uncaught ErrorException: Warning: pg_query(): Query failed: ERROR:  value too long for type character varying(5) in <...>web/include/db/postgres.inc.php:222
Stack trace:
#0 [internal function]: errorToExceptionHandler(2, 'pg_query(): Que...', '/data/www/<...>...', 222, Array)
#1 <...>web/include/db/postgres.inc.php(222): pg_query(Resource id #1, 'INSERT INTO ser...')
#2 <...>web/include/functions_images.inc.php(633): serendipity_db_query('INSERT INTO ser...')
#3 <...>web/include/functions_images.inc.php(1631): serendipity_insertImageInDatabase('header1.pngt134...', '') 
#4 <...>web/include/functions_images.inc.php(3697): serendipity_displayImageList(1, 2, true, NULL, false, NULL, Array)
#5 <...>web/include/admin/images.inc.php(424): showMediaLibrary(true)
#6 <...>web/serendipity_admin.php(109): include('/data/w in <...>web/include/db/postgres.inc.php on line 222 

 == ERROR-REPORT (BETA/ALPHA-BUILDS) ==  

Fatal Error: Uncaught ErrorException: Warning: pg_query(): Query failed: ERROR:  value too long for type character varying(5) in <...>web/include/db/postgres.inc.php:222
Stack trace:
#0 [internal function]: errorToExceptionHandler(2, 'pg_query(): Que...', '/data/www/<...>...', 222, Array)
#1 <...>web/include/db/postgres.inc.php(222): pg_query(Resource id #1, 'INSERT INTO ser...')
#2 <...>web/include/functions_images.inc.php(633): serendipity_db_query('INSERT INTO ser...')
#3 <...>web/include/functions_images.inc.php(1631): serendipity_insertImageInDatabase('header1.pngt134...', '') 
#4 <...>web/include/functions_images.inc.php(3697): serendipity_displayImageList(1, 2, true, NULL, false, NULL, Array)
#5 <...>web/include/admin/images.inc.php(424): showMediaLibrary(true)
#6 <...>web/serendipity_admin.php(109): include('/data/w in <...>web/include/db/postgres.inc.php on line 222.
For more details set $serendipity['production'] = 'debug' in serendipity_config_local.inc.php to receive a stack-trace.



Fatal error:  Uncaught ErrorException: Fatal Error: Uncaught ErrorException: Warning: pg_query(): Query failed: ERROR:  value too long for type character varying(5) in <...>web/include/db/po
Stack trace:
#0 [internal function]: errorToExceptionHandler(2, 'pg_query(): Que...', '/data/www/<...>...', 222, Array)
#1 <...>web/include/db/postgres.inc.php(222): pg_query(Resource id #1, 'INSERT INTO ser...')
#2 <...>web/include/functions_images.inc.php(633): serendipity_db_query('INSERT INTO ser...')
#3 <...>web/include/functions_images.inc.php(1631): serendipity_insertImageInDatabase('header1.pngt134...', '') 
#4 <...>web/include/functions_images.inc.php(3697): serendipity_displayImageList(1, 2, true, NULL, false, NULL, Array)
#5 <...>web/include/admin/images.inc.php(424): showMediaLibrary(true)
#6 <...>web/serend in <...>web/include/db/postgres.inc.php on line 222

This is not the case for every picture, but for many (I didn't found a pattern).

Putting $serendipity['production'] = 'debug' in the config does not help, since then no error is shown at all.

The text was updated successfully, but these errors were encountered:

AndreasBilke · 2019-05-08T17:25:23Z

I forgot to mention that, despite of the error, the media file is uploaded nevertheless.

onli · 2019-05-08T20:01:06Z

Hi. Thanks for testing the beta!

So what seems to happen here based on the stack trace (I hate that s9y/PHP does truncate that!) is that a string that is too long for a field in the image table gets inserted. The stacktrace references

Serendipity/include/functions_images.inc.php

Lines 594 to 619 in 5e36b3d

    
               $query = sprintf( 
        
                 "INSERT INTO {$serendipity['dbPrefix']}images ( 
        
                               name, 
        
                               extension, 
        
                               mime, 
        
                               size, 
        
                               dimensions_width, 
        
                               dimensions_height, 
        
                               thumbnail_name, 
        
                               date, 
        
                               authorid, 
        
                               path, 
        
                               realname 
        
                              ) VALUES ( 
        
                               '%s', 
        
                               '%s', 
        
                               '%s', 
        
                               %s, 
        
                               %s, 
        
                               %s, 
        
                               '%s', 
        
                               %s, 
        
                               %s, 
        
                               '%s', 
        
                               '%s' 
        
                              )",

and a field with type character varying(5). There is one of that in the definition of the images table: extension.

Are you trying to upload images that have an extension longer than 5 characters?

In any case this is a bug on our side if we are not catching that, but if the file extension is shorter there might be an additional bug in serendipity_parseFileName where the extension is read, or maybe

Serendipity/include/functions_images.inc.php

Line 584 in 5e36b3d

list($filebase, $extension) = serendipity_parseFileName($filename);

is not working anymore with PHP 7.3.4.

AndreasBilke · 2019-05-09T11:36:58Z

Are you trying to upload images that have an extension longer than 5 characters?

It also crashes for "test.jpg".

ophian · 2019-05-09T13:24:52Z

@AndreasBilke Interesting. I never found anyone using postgresql with Serendipity. Thanks for reporting.
You might want to test the Serendipity Styx Edition at https://github.com/ophian/styx and read about it at https://ophian.github.io, which is just about to release a new 2.8.0 version that has quite some tweaks regarding sql error reporting and the MediaLibrary, beside tons of other fixes and enhancements. I would be very interested to hear about any issue popping up over my Styx issues page, before I release that version soon. I wont mess around here so you both can handle this "bug" quietly.

Regards, Ian

AndreasBilke · 2019-05-09T13:45:16Z

@ophian back in the days when I set up the blog, s9y was the only blogging system I know which supports postgresql. It acutally ran super smooth so far :) This issue popped up when I upgraded to debian buster (with newest php and postgres) and I was therefore "forced" so use the 2.3 beta version of s9y. I fear that I cannot simply switch from s9y to your Styx edition (with current db)?

onli · 2019-05-09T14:06:36Z

It also crashes for "test.jpg".

Okay, then this is likely indeed related to PHP 7.3. My development installation is sadly stuck on PHP 7.2 so far. @th-h or @yellowled, you ran tests under PHP 7.3, no? Does uploading an image work for you under PHP 7.3 and ends up with the proper extension in the database?

ophian · 2019-05-09T14:42:16Z

@AndreasBilke

I fear that I cannot simply switch from s9y to your Styx edition (with current db)?

If that is a question please handle at Styx https://github.com/ophian/styx/issues. Thanks.

th-h · 2019-08-10T13:45:07Z

Okay, then this is likely indeed related to PHP 7.3. My development installation is sadly stuck on PHP 7.2 so far. @th-h or @yellowled, you ran tests under PHP 7.3, no? Does uploading an image work for you under PHP 7.3 and ends up with the proper extension in the database?

Both our test blogs (current and stable - identical for now) run under PHP 7.3 as of now, if you want to debug that yourself. Uploading of pictures does work there (with mySQL as backend).

But now for something completey different (see #618):

What doesn't seem to work is selecting one or more images and deleting them via the mass delete button - s9y 2.3.0 (german language) reports "Verzeichnis und Dateien wurden erfolgreich nach verschoben" (yes, with obviously missing target) and does not delete anything ...

yellowled · 2019-08-10T13:51:21Z

Does uploading an image work for you under PHP 7.3 and ends up with the proper extension in the database?

Yes, it works. Tested with GIF, JPG and PNG in s9y 2.3 running on PHP 7.3.5. It seems faster than ever, but that may be my new-ish bandwidth (haven't actually used the blog in a while).

mattsches · 2019-08-20T20:05:52Z

I can reproduce the error. I added a file called test.jpg, and then tried to add the same file again.

For some reason, in my uploads/ directory a file named test.jpg1234 turns up.

Right after each upload, a redirect to the media library happens, where the function serendipity_displayImageList() is called. You can see this in the original error message:

#3 <...>web/include/functions_images.inc.php(1631): serendipity_insertImageInDatabase('header1.pngt134...', '')
#4 <...>web/include/functions_images.inc.php(3697): serendipity_displayImageList(1, 2, true, NULL, false, NULL, Array)
#5 <...>web/include/admin/images.inc.php(424): showMediaLibrary(true)

In this rather unreadable function, files in the uploads/ directory are collected, thumbnails are filtered out, and if any other "unmatched" image is found, it is added to the database (L1631) - that's when the error occurs. test.jpg1234 is exploded into test and the suffix jpg1234, and the latter is too long for the column in the db.

Serendipity/include/functions_images.inc.php

Lines 1608 to 1634 in 573fefc

    
           foreach ($aUnmatchedOnDisk AS $sFile) { 
        
               if (preg_match('@\.' . $serendipity['thumbSuffix'] . '\.@', $sFile)) { 
        
                   if ($debug) echo "<p>Skipping thumbnailed file $sFile</p>"; 
        
                   continue; 
        
               } else { 
        
                   if ($debug) echo "<p>Checking $sFile</p>"; 
        
               } 
        
               // MTG: 21/01/06: put files which have just 'turned up' into the database 
        
               $aImageData = serendipity_getImageData($sFile); 
        
               if (serendipity_isImage($aImageData, false, '(image)|(video)|(audio)/')) { 
        
                   $nPos = strrpos($sFile, "/"); 
        
                   if (is_bool($nPos) && !$nPos) { 
        
                      $sFileName  = $sFile; 
        
                      $sDirectory = ""; 
        
                   } else { 
        
                      ++$nPos; 
        
                      $sFileName  = substr($sFile, $nPos); 
        
                      $sDirectory = substr($sFile, 0, $nPos); 
        
                   } 
        
                   if ($debug) echo "<p>Inserting image $sFileName from $sDirectory <pre>" . print_r($aImageData, true) . "</pre> into database</p>"; 
        
                   # TODO: Check if the thumbnail generation goes fine with Marty's code 
        
                   serendipity_makeThumbnail($sFileName, $sDirectory); 
        
                   serendipity_insertImageInDatabase($sFileName, $sDirectory); 
        
                   ++$nCount; 
        
               } 
        
           }

Now I wonder where the file comes from in the first place, why it has this strange suffix, and why it's added to the db when we're just trying to list images?!?

th-h · 2019-08-21T06:18:10Z

I can reproduce the error. I added a file called test.jpg, and then tried to add the same file again.

For some reason, in my uploads/ directory a file named test.jpg1234 turns up.

If I upload a file test.jpg with the media manager, and then upload again, I get an error message about duplicate filenames, and the file is renamed to test1.jpg (Screenshot). And that's what happens on disk, I checked.

It seems possible that the name-changing code runs amok and adds '1234' to the extension, but can't reproduce here (2.4-alpha1, i.e. tip of master branch, on PHP 7.3.5.

mattsches · 2019-08-21T09:15:01Z

Now I wonder where the file comes from in the first place, why it has this strange suffix, and why it's added to the db when we're just trying to list images?!?

At least figured out this part: I manually entered test.jpg1234 in the Save the file as input field while testing uploading an image. 😊

There don't seem to be any checks on allowed extensions when uploading (which is okay, I guess). The file is uploaded correctly.

But then when trying to add the image to the DB, an error occurs. I guess the MIME type check correctly identifies the file as an image, regardless of the file extension.

The same thing happens when opening the media library: The routine I quoted above looks for files/images that are missing from the DB and tries to add them -> same error.

Thumbnail creation fails for such oddly named images, too.

In the original issue, there seems to be a file called header1.pngt134, and this is causing the error.

So, to sum it up: It's not a problem with PostgreSQL (IMHO), but with how S9y handles odd file extension for images and MIME type detection.

th-h · 2019-08-21T09:39:57Z

There don't seem to be any checks on allowed extensions when uploading (which is okay, I guess). The file is uploaded correctly.

We should just enforce length constraints for the extension, corresponding to the database fields. That would fix this bug and keep the flexibility.

mattsches · 2019-08-21T10:30:24Z

We should just enforce length constraints for the extension, corresponding to the database fields. That would fix this bug and keep the flexibility.

And keep the otherwise awkward behavior 🤷‍♂️ But yes, let's implement the length constraint and see what happens. Could be we end up with broken images in the media library, though.

th-h · 2019-08-21T10:44:18Z

And keep the otherwise awkward behavior

We could use a larger database field for the extension, but I don't think that makes much sense here; it's good practice to use standardized extension for media.

We could try to fix wrong extensions, but that's error prone, and I don't think it's needed - errors like that are rare and easily fixed by the uploader.

We could use a whitelist of extensions, but that would limit flexibility. "Media" can be sound files, too (think of podcasting), videos, PDF files ...

Lastly, I think it's correct to scan the directory/directories for unknwon files on disk that are not known in the database, as that allows to upload files by FTP (or similar means, like SCP).

Could be we end up with broken images in the media library, though.

~~If the extension is wrong, it can be easily fixed in the ML by renaming, I think. Or which scenario do you imagine?~~

Yes, I see. - We'll have to decide: if we check media against a whitelist, we may make it impossible to upload media of other types at all. If we don't, someone may upload files with wrong extensions, but he'll always be able to delete the wrong file and re-upload it.

AndreasBilke · 2019-08-21T16:37:11Z

I can confirm that the installation where the error occurs has multiple files with weird file extensions.

Examples:

[...].serendipityThumb.jpgresize4202C420
[...].jpgw300
header1.pngt1348323194

Since I host this installation for someone else I can't reconstruct how and when they appeared.

That's the max length of the extensin database field. Fixes #609. Signed-off-by: Thomas Hochstein <thh@inter.net>

That's the max length of the extension database field. Fixes s9y#609. Signed-off-by: Thomas Hochstein <thh@inter.net>

onli added the bugs label May 8, 2019

onli added this to the Minor milestone May 8, 2019

th-h modified the milestones: Minor, 2.3.1 Aug 15, 2019

th-h modified the milestones: 2.3.1, 2.3.2 Aug 21, 2019

th-h added the backend label Aug 21, 2019

th-h added the backport needed Fix that has to be backported to older release branches. label Sep 5, 2019

th-h modified the milestones: 2.3.2, 2.3.3 Oct 16, 2019

th-h modified the milestones: 2.3.3, 2.3.4 Mar 23, 2020

th-h closed this as completed in 4ee1066 Mar 25, 2020

th-h added a commit that referenced this issue Mar 28, 2020

Truncate extension of media items to 5 chars.

ee62c48

That's the max length of the extensin database field. Fixes #609. Signed-off-by: Thomas Hochstein <thh@inter.net>

th-h added a commit to th-h/Serendipity that referenced this issue Apr 20, 2020

Truncate extension of media items to 5 chars.

6ad0f05

That's the max length of the extension database field. Fixes s9y#609. Signed-off-by: Thomas Hochstein <thh@inter.net>

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Error when uploading a file to media store #609

Error when uploading a file to media store #609

AndreasBilke commented May 8, 2019

AndreasBilke commented May 8, 2019

onli commented May 8, 2019 •

edited

Loading

AndreasBilke commented May 9, 2019 via email

ophian commented May 9, 2019 •

edited

Loading

AndreasBilke commented May 9, 2019 via email

onli commented May 9, 2019

ophian commented May 9, 2019

th-h commented Aug 10, 2019

yellowled commented Aug 10, 2019

mattsches commented Aug 20, 2019

th-h commented Aug 21, 2019 •

edited

Loading

mattsches commented Aug 21, 2019

th-h commented Aug 21, 2019

mattsches commented Aug 21, 2019

th-h commented Aug 21, 2019 •

edited

Loading

AndreasBilke commented Aug 21, 2019

Error when uploading a file to media store #609

Error when uploading a file to media store #609

Comments

AndreasBilke commented May 8, 2019

AndreasBilke commented May 8, 2019

onli commented May 8, 2019 • edited Loading

AndreasBilke commented May 9, 2019 via email

ophian commented May 9, 2019 • edited Loading

AndreasBilke commented May 9, 2019 via email

onli commented May 9, 2019

ophian commented May 9, 2019

th-h commented Aug 10, 2019

yellowled commented Aug 10, 2019

mattsches commented Aug 20, 2019

th-h commented Aug 21, 2019 • edited Loading

mattsches commented Aug 21, 2019

th-h commented Aug 21, 2019

mattsches commented Aug 21, 2019

th-h commented Aug 21, 2019 • edited Loading

AndreasBilke commented Aug 21, 2019

onli commented May 8, 2019 •

edited

Loading

ophian commented May 9, 2019 •

edited

Loading

th-h commented Aug 21, 2019 •

edited

Loading

th-h commented Aug 21, 2019 •

edited

Loading