Error when adding piggy bank

[emansih]

Bug description
I am running Firefly III version 4.7.6.2

When adding a piggy bank via the API, the following error is thrown.

{
"message": "Internal Firefly III Exception. See log files.",
"exception": "Illuminate\Database\QueryException"
}

Even though the error is thrown, a piggy bank is successfully added.

Steps to reproduce

curl -X POST \
  'https://firefly.xxx.com/api/v1/piggy_banks?name=PiggyBankTest&account_id=2&target_amount=100' \
  -H 'Accept: application/json' \
  -H 'Authorization: Bearer xxx' \
  -H 'Content-Type: application/vnd.api+json' \
  -F Accept=application/json

Extra info

Logs:

[2018-09-08 23:08:32] local.ERROR: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect decimal value: '' for column 'currentamount' at row 1 (SQL: update `piggy_bank_repetitions` set `currentamount` = , `updated_at` = 2018-09-08 23:08:32 where `id` = 16) {"userId":1,"email":"xxx@email.com","exception":"[object] (Illuminate\\Database\\QueryException(code: 22007): SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect decimal value: '' for column 'currentamount' at row 1 (SQL: update `piggy_bank_repetitions` set `currentamount` = , `updated_at` = 2018-09-08 23:08:32 where `id` = 16) at /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664, Doctrine\\DBAL\\Driver\\PDOException(code: 22007): SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect decimal value: '' for column 'currentamount' at row 1 at /var/www/firefly.xxx.com/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:144, PDOException(code: 22007): SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect decimal value: '' for column 'currentamount' at row 1 at /var/www/firefly.xxx.com/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:142)
[stacktrace]
#0 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Database/Connection.php(624): Illuminate\\Database\\Connection->runQueryCallback('update `piggy_b...', Array, Object(Closure))
#1 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Database/Connection.php(490): Illuminate\\Database\\Connection->run('update `piggy_b...', Array, Object(Closure))
#2 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Database/Connection.php(423): Illuminate\\Database\\Connection->affectingStatement('update `piggy_b...', Array)
#3 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2508): Illuminate\\Database\\Connection->update('update `piggy_b...', Array)
#4 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(780): Illuminate\\Database\\Query\\Builder->update(Array)
#5 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Model.php(693): Illuminate\\Database\\Eloquent\\Builder->update(Array)
#6 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Model.php(608): Illuminate\\Database\\Eloquent\\Model->performUpdate(Object(Illuminate\\Database\\Eloquent\\Builder))
#7 /var/www/firefly.xxx.com/app/Repositories/PiggyBank/PiggyBankRepository.php(470): Illuminate\\Database\\Eloquent\\Model->save()
#8 /var/www/firefly.xxx.com/app/Api/V1/Controllers/PiggyBankController.php(154): FireflyIII\\Repositories\\PiggyBank\\PiggyBankRepository->store(Array)
#9 [internal function]: FireflyIII\\Api\\V1\\Controllers\\PiggyBankController->store(Object(FireflyIII\\Api\\V1\\Requests\\PiggyBankRequest))
#10 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Routing/Controller.php(54): call_user_func_array(Array, Array)
#11 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php(45): Illuminate\\Routing\\Controller->callAction('store', Array)
#12 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Routing/Route.php(212): Illuminate\\Routing\\ControllerDispatcher->dispatch(Object(Illuminate\\Routing\\Route), Object(FireflyIII\\Api\\V1\\Controllers\\PiggyBankController), 'store')
#13 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Routing/Route.php(169): Illuminate\\Routing\\Route->runController()
#14 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Routing/Router.php(665): Illuminate\\Routing\\Route->run()
#15 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(30): Illuminate\\Routing\\Router->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#16 /var/www/firefly.xxx.com/app/Api/V1/Controllers/PiggyBankController.php(67): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#17 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(133): FireflyIII\\Api\\V1\\Controllers\\PiggyBankController->FireflyIII\\Api\\V1\\Controllers\\{closure}(Object(Illuminate\\Http\\Request), Object(Closure))
#18 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#19 /var/www/firefly.xxx.com/app/Http/Middleware/Binder.php(78): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#20 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): FireflyIII\\Http\\Middleware\\Binder->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#21 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#22 /var/www/firefly.xxx.com/app/Http/Middleware/Authenticate.php(73): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#23 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): FireflyIII\\Http\\Middleware\\Authenticate->handle(Object(Illuminate\\Http\\Request), Object(Closure), 'api')
#24 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#25 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(104): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#26 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Routing/Router.php(667): Illuminate\\Pipeline\\Pipeline->then(Object(Closure))
#27 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Routing/Router.php(642): Illuminate\\Routing\\Router->runRouteWithinStack(Object(Illuminate\\Routing\\Route), Object(Illuminate\\Http\\Request))
#28 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Routing/Router.php(608): Illuminate\\Routing\\Router->runRoute(Object(Illuminate\\Http\\Request), Object(Illuminate\\Routing\\Route))
#29 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Routing/Router.php(597): Illuminate\\Routing\\Router->dispatchToRoute(Object(Illuminate\\Http\\Request))
#30 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(176): Illuminate\\Routing\\Router->dispatch(Object(Illuminate\\Http\\Request))
#31 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(30): Illuminate\\Foundation\\Http\\Kernel->Illuminate\\Foundation\\Http\\{closure}(Object(Illuminate\\Http\\Request))
#32 /var/www/firefly.xxx.com/vendor/fideloper/proxy/src/TrustProxies.php(57): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#33 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): Fideloper\\Proxy\\TrustProxies->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#34 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#35 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php(31): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#36 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): Illuminate\\Foundation\\Http\\Middleware\\TransformsRequest->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#37 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#38 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php(31): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#39 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): Illuminate\\Foundation\\Http\\Middleware\\TransformsRequest->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#40 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#41 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/ValidatePostSize.php(27): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#42 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): Illuminate\\Foundation\\Http\\Middleware\\ValidatePostSize->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#43 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#44 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/CheckForMaintenanceMode.php(62): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#45 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): Illuminate\\Foundation\\Http\\Middleware\\CheckForMaintenanceMode->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#46 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#47 /var/www/firefly.xxx.com/app/Http/Middleware/SecureHeaders.php(46): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#48 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): FireflyIII\\Http\\Middleware\\SecureHeaders->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#49 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#50 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(104): Illuminate\\Routing\\Pipeline->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#51 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(151): Illuminate\\Pipeline\\Pipeline->then(Object(Closure))
#52 /var/www/firefly.xxx.com/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(116): Illuminate\\Foundation\\Http\\Kernel->sendRequestThroughRouter(Object(Illuminate\\Http\\Request))
#53 /var/www/firefly.xxx.com/public/index.php(76): Illuminate\\Foundation\\Http\\Kernel->handle(Object(Illuminate\\Http\\Request))
#54 {main}
"}

My firefly debug page

Debug information generated at 2018-09-08 23:17:26 Asia/Singapore for Firefly III version **4.7.6.2**.

| Variable | Content |
| --- | --- |
| FF version | 4.7.6.2 |
| FF API version | 0.7 |
| App environment | local |
| App debug mode | false |
| App cache driver | file |
| App logging | notice, daily |
| PHP version | 7.2.7-0ubuntu0.18.04.2 |
| Display errors | Off |
| Session start | 2018-09-01 00:00:00 |
| Session end | 2018-09-30 23:59:59 |
| Session first | 2018-05-18 00:00:00 |
| Error reporting | ALL errors |
| Host | Linux |
| Interface | fpm-fcgi |
| UserID | 1 |
| Attempt at "en" | false |
| Attempt at "English" | false |
| Attempt at "en_US.utf8" | 'en_US.utf8' |
| Attempt at "en_US.UTF-8" | 'en_US.UTF-8' |
| DB drivers | mysql |
| Current driver | mysql |
| Using Sandstorm? | no |
| Is Sandstorm (.env) | false |
| Is Docker (.env) | false |
| bunq uses sandbox | false |
| Trusted proxies (.env) | ** |
| User agent | Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/68.0.3440.106 Safari/537.36 |
| Loaded extensions | Core, date, libxml, openssl, pcre, zlib, filter, hash, Reflection, SPL, sodium, session, standard, cgi-fcgi, mysqlnd, PDO, xml, bcmath, bz2, calendar, ctype, curl, dom, mbstring, fileinfo, ftp, gd, gettext, iconv, intl, json, exif, mysqli, pdo_mysql, Phar, posix, readline, shmop, SimpleXML, sockets, sysvmsg, sysvsem, sysvshm, tokenizer, wddx, xmlreader, xmlwriter, xsl, zip, Zend OPcache |
| Installed packages | bacon/bacon-qr-code@1.0.3, bunq/sdk_php@dev-master, davejamesmiller/laravel-breadcrumbs@5.1.0, defuse/php-encryption@v2.2.1, doctrine/cache@v1.8.0, doctrine/dbal@v2.8.0, doctrine/event-manager@v1.0.0, doctrine/inflector@v1.3.0, doctrine/lexer@v1.0.1, dragonmantank/cron-expression@v2.2.0, egulias/email-validator@2.1.5, erusev/parsedown@1.7.1, fideloper/proxy@4.0.0, firebase/php-jwt@v5.0.0, guzzlehttp/guzzle@6.3.3, guzzlehttp/promises@v1.3.1, guzzlehttp/psr7@1.4.2, laravel/framework@v5.6.38, laravel/passport@v5.0.3, laravelcollective/html@v5.6.10, lcobucci/jwt@3.2.4, league/commonmark@0.17.5, league/csv@9.1.4, league/event@2.1.2, league/flysystem@1.0.46, league/fractal@0.17.0, league/oauth2-server@6.1.1, monolog/monolog@1.23.0, nesbot/carbon@1.25.0, paragonie/constant_time_encoding@v2.2.2, paragonie/random_compat@v2.0.17, phpseclib/phpseclib@2.0.11, pragmarx/google2fa@v3.0.3, pragmarx/google2fa-laravel@v0.2.0, psr/container@1.0.0, psr/http-message@1.0.1, psr/log@1.0.2, psr/simple-cache@1.0.1, ramsey/uuid@3.8.0, rcrowe/twigbridge@v0.9.6, swiftmailer/swiftmailer@v6.1.2, symfony/console@v4.1.4, symfony/css-selector@v4.1.4, symfony/debug@v4.1.4, symfony/event-dispatcher@v4.1.4, symfony/finder@v4.1.4, symfony/http-foundation@v4.1.4, symfony/http-kernel@v4.1.4, symfony/polyfill-ctype@v1.9.0, symfony/polyfill-mbstring@v1.9.0, symfony/polyfill-php56@v1.9.0, symfony/polyfill-php72@v1.9.0, symfony/polyfill-util@v1.9.0, symfony/process@v4.1.4, symfony/psr-http-message-bridge@v1.1.0, symfony/routing@v4.1.4, symfony/translation@v4.1.4, symfony/var-dumper@v4.1.4, tijsverkoyen/css-to-inline-styles@2.2.1, twig/twig@v1.35.4, vlucas/phpdotenv@v2.5.1, zendframework/zend-diactoros@1.8.5,  |

[emansih]

Here's another related bug report.

When current_amount is more than target_amount, the following error is thrown.

{
"message": "The given data was invalid.",
"errors": {
"current_amount": [
"validation.lte.numeric"
]
}
}

The message isn't helpful since it doesn't tell me that the current amount is more than target amount

[JC5]

Yes. Make sure that current amount is 0 or filled in. And indeed, the current amount could never be more than the target amount. I’ll clear up the error messages.

[emansih]

With regards to the first error, it appears to be caused by database migration? I did run

php artisan cache:clear
php artisan migrate --seed
php artisan firefly:upgrade-database
php artisan firefly:verify
php artisan passport:install

when doing the upgrade though

[JC5]

Thanks for the extra info.

I've pushed a fix for both problems. It was a matter of input sanitation, and a missing translation. You can apply the fix yourself (see the link above my reply) or wait for the next release.

[emansih]

Hi, sorry for opening this again. I found another validation bug.

curl -X POST \
  'https://firefly.xxx.com/api/v1/piggy_banks?name=APiggyBank&account_id=2&target_amount=100&current_amount=50&start_date=2018-09-02&target_date=2018-09-01' \
  -H 'Accept: application/json' \
  -H 'Authorization: Bearer xxx' \
  -H 'Content-Type: application/vnd.api+json' \

Even though target date is earlier than the start date, the piggy bank is created successfully. The start_date and target_date in the json output is null though. I feel that validation should always be done via server side, for now I will validate via client side. Thanks!

[JC5]

No problem. That should be an easy fix and it should be done on the server like you say.

[JC5]

Closed pending the next release this weekend.