Adding bookmark leads to: SQLSTATE[22021]: Character not in repertoire: 7 ERROR: invalid byte sequence for encoding \"UTF8\"

[ege1]

Hi,

I'm having problems adding bookmarks.

One question is, why doesn't it use the title I entered/altered in the input field? Instead it seams to use the original site title even if I change it. That shouldn't be the case for my understanding. Otherwise the input field for new bookmark titles should be readonly.

Having that said, here the issue I'm facing:
Site is: http://www.jobs.ch/de/job/?iid=6812797

The Error in the log:

Mar 16 13:08:23 web ownCloud[18863]: {index} Exception: {"Exception":"Doctrine\\DBAL\\Exception\\DriverException","Message":"An exception occurred while executing '\n\t\t\tINSERT INTO \"oc_bookmarks\"\n\t\t\t(\"url\", \"title\", \"user_id\", \"public\", \"added\", \"lastmodified\", \"description\")\n\t\t\tVALUES (?, ?, ?, ?, cast(extract(epoch from current_timestamp) as integer), cast(extract(epoch from current_timestamp) as integer), ?)\n\t\t\t' with params [\"http:\\\/\\\/www.jobs.ch\\\/de\\\/job\\\/?iid=6812797\", \"\\x6a\\x6f\\x62\\x73\\x2e\\x63\\x68\\x20\\x2d\\x20\\x53\\x65\\x6e\\x69\\x6f\\x72\\x20\\x53\\x79\\x73\\x74\\x65\\x6d\\x20\\x45\\x6e\\x67\\x69\\x6e\\x65\\x65\\x72\\x20\\x4c\\x69\\x6e\\x75\\x78\\x20\\x28\\x6d\\x2f\\x77\\x29\\x20\\x2d\\x20\\x4c\\x69\\x6e\\x75\\x78\\x20\\x53\\x79\\x73\\x74\\x65\\x6d\\x20\\x41\\x64\\x6d\\x69\\x6e\\x69\\x73\\x74\\x72\\x61\\x74\\x6f\\x72\\x20\\x20\\x69\\x6d\\x20\\x52\\x61\\x75\\x6d\\x20\\x5a\\xfc\\x72\\x69\\x63\\x68\", \"ege\", 0, \"\"]:\n\nSQLSTATE[22021]: Character not in repertoire: 7 ERROR: invalid byte sequence for encoding \"UTF8\": 0xfc","Code":0,"Trace":"#0 \/var\/www\/owncloud\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/DBALException.php(116): Doctrine\\DBAL\\Driver\\AbstractPostgreSQLDriver->convertException('An exception oc...', Object(Doctrine\\DBAL\\Driver\\PDOException))\n#1 \/var\/www\/owncloud\/3rdparty\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Statement.php(175): Doctrine\\DBAL\\DBALException::driverExceptionDuringQuery(Object(Doctrine\\DBAL\\Driver\\PDOPgSql\\Driver), Object(Doctrine\\DBAL\\Driver\\PDOException), '\\n\\t\\t\\tINSERT INTO...', Array)\n#2 \/var\/www\/owncloud\/lib\/private\/db\/statementwrapper.php(73): Doctrine\\DBAL\\Statement->execute(Array)\n#3 \/var\/www\/owncloud\/apps\/bookmarks\/controller\/lib\/bookmarks.php(472): OC_DB_StatementWrapper->execute(Array)\n#4 \/var\/www\/owncloud\/apps\/bookmarks\/controller\/rest\/bookmarkcontroller.php(105): OCA\\Bookmarks\\Controller\\Lib\\Bookmarks::addBookmark('ege', Object(OC\\AppFramework\\Db\
I can change the tilte to whatever I want using the input field before clicking save and it still tries the same input. So for me these are two issues:

• it uses the wrong "title"
• it fails to escape/replace properly

If you need additional information, please let me know.

Erik

[ege1]

OK, finally I found where the form values get overwritten.

Is there reason for not taking the content of the add bookmark form?

In my case the German "Umlaut" (ü) is causing the issue. If I log the title line I can see that it is printed wrong even in the log. But if I use the content of the form fields it is printed correctly and also saved without any issue.

That is my change:

 --- controller/rest/bookmarkcontroller.php.orig 2016-04-13 20:41:20.693988997 +0200
+++ controller/rest/bookmarkcontroller.php      2016-04-13 20:39:31.734001754 +0200
@@ -82,7 +82,7 @@
                                $url_http = 'http://' . $url;
                                $datas_http = Bookmarks::getURLMetadata($url_http);
                        }
-
+               /**
                        if (isset($datas['title'])) { // prefer original url if working
                                $title = $datas['title'];
                                //url remains unchanged
@@ -93,6 +93,7 @@
                                $title = $datas_http['title'];
                                $url = $url_http;
                        }
+               **/
                }

                // Check if it is a valid URL (after adding http(s) prefix)

I see no reason to prefer the Site content instead of what the user chose to save.

[blizzz]

@ege1 thanks for reporting the issue. The original page does not exist in the form anymore. I'd like to figure out what the issues is in the end, aside of preferring a self-written title for the bookmark.

So the page did have an ü in it's title? Do experience the same with e.g. https://de.wikipedia.org/wiki/Ü (i see the link does not work subsequently, but not the reported error)? Which versions and database are you using?

[ege1]

Hi @blizzz, I think the problem was that the ü came with the wrong encoding or the like.
I'm using latest stable owncloud 9,0,1,3 and bookmarks 0.7 (as of the version file) with postgreSQL database.
On the wikipedia article I also can't reproduce the error message (I think I upgraded in the meanwhile). But it also doesn't save.

I also searched for similar sites as the original one but couldn't find one. I found another one but it is the same as for the wikipedia one. It doesn't save but nothing in the logs :(

[ege1]

I know that I implemented some debug output into a log file to see what should be saved to the database but can't find that specific log entry any more :(
I just know that the ü was only displayed as a "sign", not a letter...

[ege1]

Hi @blizzz , searched on the wrong date. Maybe that helps a bit:
2016/04/13 20:05:17 [error] 27451#0: *240 FastCGI sent in stderr: "PHP message: test
PHP message: http://www.jobs.ch/de/job/?iid=6857415,jobs.ch - Network und Security Engineer, Region Zrich,ege,0," while reading response header from upstream, client: 2001:1620:f64:0:e0d0:e38a:24da:4d1a, server: owncloud.geiger-online.ch, request: "POST /index.php/apps/bookmarks/bookmark HTTP/1.1", upstream: "fastcgi://unix:/var/run/php5-fpm.sock:", host: "owncloud.geiger-online.ch"

This also gives you some information I forgot to mention. I'm running owncloud on nginx with php-fpm.

HTH

Erik

[blizzz]

Maybe that helps a bit:
2016/04/13 20:05:17 [error] 27451#0: *240 FastCGI sent in stderr: "PHP message: test
PHP message: http://www.jobs.ch/de/job/?iid=6857415,jobs.ch - Network und Security Engineer, Region Zrich,ege,0," while reading response header from upstream, client: 2001:1620:f64:0:e0d0:e38a:24da:4d1a, server: owncloud.geiger-online.ch, request: "POST /index.php/apps/bookmarks/bookmark HTTP/1.1", upstream: "fastcgi://unix:/var/run/php5-fpm.sock:", host: "owncloud.geiger-online.ch"

Tastes like an encoding issue indeed. I will try to reproduce it, thanks for looking it up.

On the wikipedia article I also can't reproduce the error message (I think I upgraded in the meanwhile). But it also doesn't save.

It does not save it because https://github.com/owncloud/bookmarks/pull/239

I will go for a different fix though, this is too ugly. Found this thanks to investigating your report 💐

[blizzz]

@ege1 I tried a couple of things, but without success. Could you recheck if you can reproduce it with one of those?

• https://www.jobs.ch/de/job/?iid=6924344,jobs.ch
• https://minion.arthur-schiwon.de/test.html
• https://minion.arthur-schiwon.de/test2.html

[ege1]

@blizzz I can reproduce the behavior with the first link:

https://www.jobs.ch/de/job/?iid=6924344,jobs.ch
Leads to access.log:
[24/Apr/2016:18:00:13 +0200] "POST /index.php/apps/bookmarks/bookmark HTTP/1.1" 500 .....

error.log:
2016/04/24 18:00:13 [error] 27450#0: *49857 FastCGI sent in stderr: "PHP message: ERROR: ARRAY: https://www.jobs.ch/de/job/?iid=6924344,jobs.ch,jobs.ch - Personalberater kaufmnnischer Bereich, Region Zrich,ege,0," while reading response header from upstream, client: 2001:1620:f64:0:242c:78d2:a419:878, server: owncloud.geiger-online.ch, request: "POST /index.php/apps/bookmarks/bookmark HTTP/1.1", upstream: "fastcgi://unix:/var/run/php5-fpm.sock:", host: "owncloud.geiger-online.ch"

While the error log still includes my debug print I think.
If I use the content of the input fields instead (which is my local workaround, I just commented out a few lines) this lookes like that:

web rest # less /var/log/nginx/owncloud-error.log
2016/04/24 17:58:39 [error] 27450#0: *49857 FastCGI sent in stderr: "PHP message: ERROR: ARRAY: https://www.jobs.ch/de/job/?iid=6924344,jobs.ch,jobs.ch - Personalberater kaufmännischer Bereich, Region Zürich,ege,0," while reading response header from upstream, client: 2001:1620:f64:0:242c:78d2:a419:878, server: owncloud.geiger-online.ch, request: "POST /index.php/apps/bookmarks/bookmark HTTP/1.1", upstream: "fastcgi://unix:/var/run/php5-fpm.sock:", host: "owncloud.geiger-online.ch"

And now the DB-Error:
syslog (as php-fpm logs to syslog on my server):
Apr 24 18:00:13 web ownCloud[3768]: {index} Exception: {"Exception":"Doctrine\DBAL\Exception\DriverException","Message":"An exception occurred while executing '\n\t\t\tINSERT INTO "oc_bookmarks"\n\t\t\t("url", "title", "user_id", "public", "added", "lastmodified", "description")\n\t\t\tVALUES (?, ?, ?, ?, cast(extract(epoch from current_timestamp) as integer), cast(extract(epoch from current_timestamp) as integer), ?)\n\t\t\t' with params ["https:\/\/www.jobs.ch\/de\/job\/?iid=6924344,jobs.ch", "\x6a\x6f\x62\x73\x2e\x63\x68\x20\x2d\x20\x50\x65\x72\x73\x6f\x6e\x61\x6c\x62\x65\x72\x61\x74\x65\x72\x20\x6b\x61\x75\x66\x6d\xe4\x6e\x6e\x69\x73\x63\x68\x65\x72\x20\x42\x65\x72\x65\x69\x63\x68\x2c\x20\x52\x65\x67\x69\x6f\x6e\x20\x5a\xfc\x72\x69\x63\x68", "ege", 0, ""]:\n\nSQLSTATE[22021]: Character not in repertoire: 7 ERROR: invalid byte sequence for encoding "UTF8": 0xe4 0x6e 0x6e","Code":0,"Trace":"#0 /var/www/owncloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php(116): Doctrine\DBAL\Driver\AbstractPostgreSQLDriver->convertException('An exception oc...', Object(Doctrine\DBAL\Driver\PDOException))\n#1 /var/www/owncloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Statement.php(175): Doctrine\DBAL\DBALException::driverExceptionDuringQuery(Object(Doctrine\DBAL\Driver\PDOPgSql\Driver), Object(Doctrine\DBAL\Driver\PDOException), '\n\t\t\tINSERT INTO...', Array)\n#2 /var/www/owncloud/lib/private/db/statementwrapper.php(73): Doctrine\DBAL\Statement->execute(Array)\n#3 /var/www/owncloud/apps/bookmarks/controller/lib/bookmarks.php(473): OC_DB_StatementWrapper->execute(Array)\n#4 /var/www/owncloud/apps/bookmarks/controller/rest/bookmarkcontroller.php(105): OCA\Bookmarks\Controller\Lib\Bookmarks::addBookmark('ege', Object(OC\AppFramework\Db\Db), 'https://www.job...', 'jobs.ch - Perso...', Array, '', false)\n#5 [internal function]: OCA\Bookmarks\Controller\Rest\BookmarkController->newBookmark('https://www.job...', Array, 0, 'jobs.ch - Perso...', false, '')\n#6 /var/www/owncloud/lib/private/appframework/http/dispatcher.php(159): call_user_func_array(Array, Array)\n#7 /var/www/owncloud/lib/private/appframework/http/dispatcher.php(89): OC\AppFramework\Http\Dispatcher->executeController(Object(OCA\Bookmarks\Controller\Rest\BookmarkController), 'newBookmark')\n#8 /var/www/owncloud/lib/private/appframework/app.php(110): OC\AppFramework\Http\Dispatcher->dispatch(Object(OCA\Bookmarks\Controller\Rest\BookmarkController), 'newBookmark')\n#9 /var/www/owncloud/lib/private/appframework/routing/routeactionhandler.php(45): OC\AppFramework\App::main('BookmarkControl...', 'newBookmark', Object(OC\AppFramework\DependencyInjection\DIContainer), Array)\n#10 [internal function]: OC\AppFramework\routing\RouteActionHandler->__invoke(Array)\n#11 /var/www/owncloud/lib/private/route/router.php(276): call_user_func(Object(OC\AppFramework\routing\RouteActionHandler), Array)\n#12 /var/www/owncloud/lib/base.php(882): OC\Route\Router->match('/apps/bookmarks...')\n#13 /var/www/owncloud/index.php(39): OC::handleRequest()\n#14 {main}","File":"/var/www/owncloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractPostgreSQLDriver.php","Line":91}

HTH

Erik

[blizzz]

Works for me, but I am running MySQL. Bookmarking the other two links worked? What encoding is your database set up with?

[blizzz]

One question is, why doesn't it use the title I entered/altered in the input field? Instead it seams to use the original site title even if I change it. That shouldn't be the case for my understanding. Otherwise the input field for new bookmark titles should be readonly.

Totally agree btw. Fix in https://github.com/owncloud/bookmarks/pull/245

[blizzz]

@ege1 I think I have a fix! Could you please try out https://github.com/owncloud/bookmarks/pull/246 ?