search

nilsteampassnet / TeamPass

Collaborative Passwords Manager
https://www.teampass.net
1.67k stars 545 forks source link

missing database schema updates #3310

Open BelgarathS opened 2 years ago

BelgarathS commented 2 years ago

Page on which it happened

pass/index.php?page=items# I can't edit any passwords

Steps to reproduce

  1. Install git version of the teampass
  2. run a couple of pulls
  3. wait for the project to change the database layout

Expected behaviour

Database layout should add missing fields or there should be a way to update them

Actual behaviour

System throws PHP errors about missing columns The popup message"At least one folder needs to be selected shows"

Server configuration

Operating system: Linux pass 5.15.0-46-generic #49-Ubuntu SMP Thu Aug 4 18:03:25 UTC 2022 x86_64

Web server: Apache/2.4.52 (Ubuntu)

Database: 5.5.5-10.6.7-MariaDB-2ubuntu1.1

PHP version: 8.1.2

Teampass version: 3.0.0.18

Teampass configuration file:

'max_latest_items' => '10',
'enable_favourites' => '1',
'show_last_items' => '1',
'enable_pf_feature' => '1',
'log_connections' => '1',
'log_accessed' => '1',
'time_format' => 'H:i:s',
'date_format' => 'd/m/Y',
'duplicate_folder' => '1',
'duplicate_item' => '1',
'number_of_used_pw' => '3',
'manager_edit' => '1',
'cpassman_dir' => '/var/www/html/pass',
'cpassman_url' => 'https://<anonym_url>/pass
'favicon' => 'https://<anonym_url>/pass/favicon.ico',
'activate_expiration' => '0',
'pw_life_duration' => '0',
'maintenance_mode' => '0',
'cpassman_version' => '3.0.0.18',
'ldap_mode' => '0',
'richtext' => '0',
'allow_print' => '0',
'show_description' => '1',
'anyone_can_modify' => '0',
'nb_bad_authentication' => '0',
'utf8_enabled' => '1',
'restricted_to' => '0',
'restricted_to_roles' => '0',
'custom_logo' => '',
'custom_login_text' => '',
'default_language' => 'english',
'send_stats' => '0',
'send_mail_on_user_login' => '0',
'nb_items_by_query' => 'auto',
'send_stats_time' => '1358502656',
'ldap_ssl' => '0',
'ldap_tls' => '0',
'enable_kb' => '0',
'copy_to_clipboard_small_icons' => '1',
'enable_user_can_create_folders' => '1',
'enable_send_email_on_user_login' => '0',
'menu_type' => 'context',
'enable_delete_after_consultation' => '0',
'path_to_upload_folder' => '/var/www/pass/upload',
'url_to_upload_folder' => 'https://<anonym_url>/pass/upload',
'enable_email_notification_on_item_shown' => '0',
'anyone_can_modify_bydefault' => '0',
'enable_personal_saltkey_cookie' => '1',
'personal_saltkey_cookie_duration' => '31',
'path_to_files_folder' => '/var/www/pass/files',
'url_to_files_folder' => 'https://<anonym_url>/pass/files',
'pwd_maximum_length' => '40',
'email_smtp_server' => '<removed>'
'email_smtp_auth' => 'on',
'email_auth_username' => '<removed>'
'email_auth_pwd' => '<removed>'
'email_post' => '25',
'email_from' => '<removed>'
'email_from' => '<removed>'
'delay_item_edition' => '0',
'allow_import' => '1',
'proxy_port' => '0',
'upload_maxfilesize' => '10mb',
'upload_docext' => 'doc,docx,dotx,xls,xlsx,xltx,rtf,csv,txt,pdf,ppt,pptx,pot,dotx,xltx',
'upload_imagesext' => 'jpg,jpeg,gif,png',
'upload_pkgext' => '7z,rar,tar,zip',
'upload_otherext' => 'sql,xml',
'upload_imageresize_options' => '1',
'upload_imageresize_width' => '800',
'upload_imageresize_height' => '600',
'upload_imageresize_quality' => '90',
'insert_manual_entry_item_history' => '0',
'enable_sts' => '0',
'ldap_elusers' => '0',
'email_port' => '25',
'can_create_root_folder' => '1',
'encryptClientServer' => '1',
'tree_counters' => '0',
'ldap_domain_controler' => 'root',
'use_md5_password_as_salt' => '0',
'item_duplicate_in_same_folder' => '0',
'ldap_type' => '0',
'ldap_suffix' => '0',
'ldap_domain_dn' => '0',
'ldap_user_attribute' => '0',
'roles_allowed_to_print' => '0',
'get_tp_info' => '1',
'ga_website_name' => 'TeamPass for ChangeMe',
'email_security' => '',
'enable_email_notification_on_user_pw_change' => '1',
'api' => '0',
'subfolder_rights_as_parent' => '1',
'show_only_accessible_folders' => '1',
'enable_suggestion' => '0',
'email_server_url' => '',
'otv_expiration_period' => '7',
'default_session_expiration_time' => '600',
'duo' => '0',
'google_authentication' => '0',
'enable_server_password_change' => '0',
'bck_script_path' => '/backups',
'bck_script_filename' => 'bck_cpassman',
'syslog_enable' => '0',
'syslog_host' => 'localhost',
'syslog_port' => '514',
'ldap_object_class' => '0',
'saltkey_ante_2127' => '',
'teampass_version' => '2.1.27',
'migration_to_2127' => 'done',
'manager_move_item' => '0',
'create_item_without_password' => '0',
'agses_authentication_enabled' => '0',
'timezone' => 'UTC',
'personal_saltkey_security_level' => '0',
'item_extra_fields' => '0',
'ldap_new_user_is_administrated_by' => '0',
'ldap_port' => '389',
'offline_key_level' => '0',
'enable_http_request_login' => '0',
'admin_2fa_required' => '1',
'otv_is_enabled' => '0',
'ldap_and_local_authentication' => '0',
'secure_display_image' => '1',
'upload_zero_byte_file' => '0',
'upload_all_extensions_file' => '0',
'files_with_defuse' => 'done',
'password_overview_delay' => '20',
'roles_allowed_to_print_select' => '',
'clipboard_life_duration' => '30',
'mfa_for_roles' => '',
'settings_offline_mode' => '0',
'settings_tree_counters' => '0',
'enable_massive_move_delete' => '0',
'email_debug_level' => '0',
'ga_reset_by_user' => '',
'onthefly-backup-key' => '',
'onthefly-restore-key' => '',
'ldap_user_dn_attribute' => '',
'ldap_dn_additional_user_dn' => '',
'ldap_user_object_filter' => '',
'ldap_bdn' => '',
'ldap_hosts' => '',
'ldap_password' => '',
'ldap_username' => '',
'api_token_duration' => '60',
'duo_ikey' => 'wifi_hai',

Updated from an older Teampass or fresh install:

Client configuration

Browser: - Chrome - Version 105.0.5195.102 (Official Build) (64-bit) Operating system: - bits ubuntu 22.04

Logs

Web server error log

Constant FILTER_SANITIZE_STRING is deprecated - /var/www/html/pass/sources/main.queries.php (118)
[Fri Sep 09 23:35:29.206097 2022] [php:error] [pid 92476] [client 192.168.231.201:33010] PHP Fatal error:  Uncaught mysqli_sql_exception: Unknown column 'categories' in 'field list' in /var/www/html/pass/includes/libraries/Database/Meekrodb/db.class.php:682\nStack trace:\n#0 /var/www/html/pass/includes/libraries/Database/Meekrodb/db.class.php(682): mysqli->query()\n#1 /var/www/html/pass/includes/libraries/Database/Meekrodb/db.class.php(637): MeekroDB->queryHelper()\n#2 /var/www/html/pass/includes/libraries/Database/Meekrodb/db.class.php(638): MeekroDB->prependCall()\n#3 /var/www/html/pass/includes/libraries/Database/Meekrodb/db.class.php(319): MeekroDB->query()\n#4 /var/www/html/pass/includes/libraries/Database/Meekrodb/db.class.php(83): MeekroDB->update()\n#5 /var/www/html/pass/sources/main.functions.php(3718): DB::update()\n#6 /var/www/html/pass/sources/folders.queries.php(483): handleFoldersCategories()\n#7 {main}\n  thrown in /var/www/html/pass/includes/libraries/Database/Meekrodb/db.class.php on line 682, referer: https://banda.pl/pass/index.php?page=folders

[Fri Sep 09 23:46:06.834992 2022] [php:error] [pid 88577] [client 192.168.231.201:33092] PHP Fatal error:  Uncaught mysqli_sql_exception: Unknown column 'visible_folders' in 'field list' in /var/www/html/pass/includes/libraries/Database/Meekrodb/db.class.php:682\nStack trace:\n#0 /var/www/html/pass/includes/libraries/Database/Meekrodb/db.class.php(682): mysqli->query()\n#1 /var/www/html/pass/includes/libraries/Database/Meekrodb/db.class.php(637): MeekroDB->queryHelper()\n#2 /var/www/html/pass/includes/libraries/Database/Meekrodb/db.class.php(638): MeekroDB->prependCall()\n#3 /var/www/html/pass/includes/libraries/Database/Meekrodb/db.class.php(753): MeekroDB->query()\n#4 /var/www/html/pass/includes/libraries/Database/Meekrodb/db.class.php(66): MeekroDB->queryFirstRow()\n#5 /var/www/html/pass/sources/main.functions.php(3595): DB::queryFirstRow()\n#6 /var/www/html/pass/sources/items.queries.php(6052): loadFoldersListByCache()\n#7 {main}\n  thrown in /var/www/html/pass/includes/libraries/Database/Meekrodb/db.class.php on line 682, referer: https://banda.pl/pass/index.php?page=items

Teampass 10 last system errors

 * 10/06/2022 13:36:03 - Query: INSERT INTO `teampass_items` (`label`) VALUES ('duplicate')<br />Error: Field 'restricted_to' doesn't have a default value<br />@ /pass/sources/items.queries.php * 16/03/2022 19:54:39 - Query: INSERT INTO `teampass_items` (`label`) VALUES ('duplicate')<br />Error: Field 'restricted_to' doesn't have a default value<br />@ /pass/sources/items.queries.php * 01/10/2021 12:47:04 - Query: INSERT INTO `teampass_items` (`label`) VALUES ('duplicate')<br />Error: Field 'restricted_to' doesn't have a default value<br />@ /pass/sources/items.queries.php * 
 * 20/04/2018 20:15:49 - Query: INSERT INTO `teampass_items` (`label`) VALUES ('duplicate')<br />Error: Field 'restricted_to' doesn't have a default value<br />@  * 24/02/2018 16:29:05 - Query: INSERT INTO `teampass_items` (`label`) VALUES ('duplicate')<br />Error: Field 'restricted_to' doesn't have a default value<br />@

Log from the web-browser developer console (CTRL + SHIFT + i)

csrfprotector.js:1 Failed to load resource: the server responded with a status of 404 (Not Found) /pass/sources/items.queries.php:1 Failed to load resource: the server responded with a status of 500 (Internal Server Error) /pass/sources/items.queries.php:1 Failed to load resource: the server responded with a status of 500 (Internal Server Error)

Insert the log here and especially the answer of the query that failed.
nilsteampassnet commented 2 years ago

Thank you @BelgarathS for reporting.

But regarding the message you get, it indicates that you haven't selected any folder in which to store your new item. A few commits before, this could occurred in specific condition but it should have been fixed.

The error you get in log are not related to the step you

BelgarathS commented 2 years ago

Hi, thank you for responding, when I load the page i got the following: the /pass/sources/items.queries.php fails with error 500 and I get the following in error_log: [Sat Sep 10 19:26:05.408053 2022] [php:error] [pid 95789] [client 192.168.231.201:33288] PHP Fatal error: Uncaught mysqli_sql_exception: Unknown column 'visible_folders' in 'field list' in /var/www/html/pass/includes/libraries/Database/Meekrodb/db.class.php:682\nStack trace:\n#0 /var/www/html/pass/includes/libraries/Database/Meekrodb/db.class.php(682): mysqli->query()\n#1 /var/www/html/pass/includes/libraries/Database/Meekrodb/db.class.php(637): MeekroDB->queryHelper()\n#2 /var/www/html/pass/includes/libraries/Database/Meekrodb/db.class.php(638): MeekroDB->prependCall()\n#3 /var/www/html/pass/includes/libraries/Database/Meekrodb/db.class.php(753): MeekroDB->query()\n#4 /var/www/html/pass/includes/libraries/Database/Meekrodb/db.class.php(66): MeekroDB->queryFirstRow()\n#5 /var/www/html/pass/sources/main.functions.php(3595): DB::queryFirstRow()\n#6 /var/www/html/pass/sources/items.queries.php(6052): loadFoldersListByCache()\n#7 {main}\n thrown in /var/www/html/pass/includes/libraries/Database/Meekrodb/db.class.php on line 682, referer: https://banda.pl/pass/index.php?page=items

And you're right I don't select any folder but my folder field is empty: image

I assumed this is because of the error quoted above i.e not having a field called "visible_folders".

Please let me know if I can do anything more to help here

nilsteampassnet commented 2 years ago

Please use latest commit and run the upgrade process

BelgarathS commented 2 years ago

I'm running 3.0.0.17-77-gea87ad2a (which seems to be the latest at this stage)

When trying to run upgrade: /install/upgrade.php

I get the following error: [Sun Sep 11 13:43:21.771687 2022] [php:error] [pid 98075] [client 192.168.231.201:33418] PHP Fatal error: Uncaught mysqli_sql_exception: Incorrect table definition; there can be only one auto column and it must be defined as a key in /var/www/html/pass/install/upgrade_run_3.0.0.php:873\nStack trace:\n#0 /var/www/html/pass/install/upgrade_run_3.0.0.php(873): mysqli_query()\n#1 {main}\n thrown in/var/www/html/pass/install/upgrade_run_3.0.0.php on line 873, referer: https://banda.pl/pass/install/upgrade.php

the page hangs at the following: image

Antiaris74 commented 2 years ago

Can confirm having this issue on a last master commit (b55dbf9) with PHP 7.4.3, Chrome 105.0.5195.102. Page /index.php?page=items# after pressing button "New item" on a existing group After fresh install tried to add password for just created folder, but got popup error "At least one folder needs to be selected". Folder field at Details tab are empty, so it's not possible to choose any, even if I try to create password in a folder with children folders.

I edited page HTML and created selected option tag in #form-item-folder select with folder ID that I need, so form verification was able to be passed, password was created and this issue are not reproducing anymore, because now Folder field shows all available folders

jangregor commented 2 years ago

The issue may be with the cache_tree table.

Scenario: 1., A new folder is created 2., User's role is allowed write access to the folder

Expected result: User should see the folder in details view when creating new item.

Actual result: Use does not see the folder in the details view, so cannot create items in that folder. Logoff and logon of the user does not refresh this.

Workaround: Run in MySQL (keep in mind the teampass_ prefix may be different for you depending in what you specified during install): delete from teampass_cache_tree; Or run "Reload cache table" action .

Fix: Automatically "Reload cache table" when a role is assigned access to a folder for all affected users. Or not use cache at all :) .

BelgarathS commented 2 years ago

after running the command above the tree is not loaded with the following error message: [Sat Sep 17 22:59:27.838901 2022] [php:error] [pid 168601] [client 192.168.231.201:34662] PHP Fatal error: Uncaught mysqli_sql_exception: Unknown column 'visible_folders' in 'field list' in /var/www/html/pass/includes/libraries/Database/Meekrodb/db.class.php:682\nStack trace:\n#0 /var/www/html/pass/includes/libraries/Database/Meekrodb/db.class.php(682): mysqli->query()\n#1 /var/www/html/pass/includes/libraries/Database/Meekrodb/db.class.php(637): MeekroDB->queryHelper()\n#2 /var/www/html/pass/includes/libraries/Database/Meekrodb/db.class.php(638): MeekroDB->prependCall()\n#3 /var/www/html/pass/includes/libraries/Database/Meekrodb/db.class.php(361): MeekroDB->query()\n#4 /var/www/html/pass/includes/libraries/Database/Meekrodb/db.class.php(365): MeekroDB->insertOrReplace()\n#5 /var/www/html/pass/includes/libraries/Database/Meekrodb/db.class.php(79): MeekroDB->insert()\n#6 /var/www/html/pass/sources/main.functions.php(3513): DB::insert()\n#7 /var/www/html/pass/sources/tree.php(223): cacheTreeUserHandler()\n#8 {main}\n thrown in /var/www/html/pass/includes/libraries/Database/Meekrodb/db.class.php on line 682

BelgarathS commented 2 years ago

running :

ALTER TABLE teampass_cache_tree ADD COLUMN visible_folders INT(1);

fixes the issue, but that probably breaks the installation for further upgrades.