search

Dolibarr / dolibarr

Dolibarr ERP CRM is a modern software package to manage your company or foundation's activity (contacts, suppliers, invoices, orders, stocks, agenda, accounting, ...). it's an open source Web application (written in PHP) designed for businesses of any sizes, foundations and freelancers.
https://www.dolibarr.org
GNU General Public License v3.0
5.46k stars 2.79k forks source link

Malformed SQL request in bankentries.php #8514

Closed papoteur-mga closed 6 years ago

papoteur-mga commented 6 years ago

Bug

the page bankentries.php?action=reconcile report an syntax error in SQL request

Environment

Dolibarr: 6.0.1 Niveau de fonctionnalités: 2 PHP: 5.6.33 Server: Apache/2.4.27 (Mageia) PHP/5.6.33 mod_perl/2.0.10 Perl/v5.22.3 OS: Linux localhost 4.14.20-desktop-1.mga6 #1 SMP Sun Feb 18 01:22:02 UTC 2018 x86_64 UserAgent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Firefox/52.0

Url sollicitée: /dolibarr/htdocs/compta/bank/bankentries.php?action=reconcile&search_conciliated=0&contextpage=banktransactionlist-4&ref=3887&account=4 Referer: http://localhost/dolibarr/htdocs/compta/bank/bankentries.php?page=1&contextpage=banktransactionlist-4&ref=3887&account=4&sortfield=b.datev,%20b.dateo,%20b.rowid&sortorder=ASC Gestionnaire de menu: eldy_menu.php

Type gestionnaire de base de données: mysqli

Expected and actual behavior

No error is expected. What is to note is that the request has : WHERE fk_account= without value after that. Extract from the log file when the error occurs : 2018-04-05 11:17:02 DEBUG 127.0.0.1 sql=SELECT MIN(te.ref) FROM llx_bank_account as te WHERE te.ref > '3887' 2018-04-05 11:17:02 DEBUG 127.0.0.1 sql=SELECT b.rowid, b.dateo as do, b.datev as dv, b.amount, b.label, b.rappro as conciliated, b.num_releve, b.num_chq, b.fk_account, b.fk_type, ba.rowid as bankid, ba.ref as bankref, bu.url_id, s.nom, s.name_alias, s.client, s.fournisseur, s.code_client, s.code_fournisseur, s.code_compta, s.code_compta_fournisseur FROM llx_bank_account as ba, llx_bank as b LEFT JOIN llx_bank_url as bu ON bu.fk_bank = b.rowid AND type = 'company' LEFT JOIN llx_societe as s ON bu.url_id = s.rowid WHERE b.fk_account = ba.rowid AND ba.entity IN (1) AND b.fk_account = 4 AND b.rappro = 0 ORDER BY b.datev ASC, b.dateo, b.rowid 2018-04-05 11:17:02 DEBUG 127.0.0.1 compta/bank/bankentries.php 2018-04-05 11:17:02 DEBUG 127.0.0.1 sql=SELECT b.rowid, b.dateo as do, b.datev as dv, b.amount, b.label, b.rappro as conciliated, b.num_releve, b.num_chq, b.fk_account, b.fk_type, ba.rowid as bankid, ba.ref as bankref, bu.url_id, s.nom, s.name_alias, s.client, s.fournisseur, s.code_client, s.code_fournisseur, s.code_compta, s.code_compta_fournisseur FROM llx_bank_account as ba, llx_bank as b LEFT JOIN llx_bank_url as bu ON bu.fk_bank = b.rowid AND type = 'company' LEFT JOIN llx_societe as s ON bu.url_id = s.rowid WHERE b.fk_account = ba.rowid AND ba.entity IN (1) AND b.fk_account = 4 AND b.rappro = 0 ORDER BY b.datev ASC, b.dateo, b.rowid LIMIT 26 2018-04-05 11:17:02 WARNING 127.0.0.1 Deprecated use of GETPOST, called with 1st param = bid and 2nd param not defined, when calling page /dolibarr/htdocs/compta/bank/bankentries.php 2018-04-05 11:17:02 WARNING 127.0.0.1 Deprecated use of GETPOST, called with 1st param = num_releve and 2nd param not defined, when calling page /dolibarr/htdocs/compta/bank/bankentries.php 2018-04-05 11:17:02 DEBUG 127.0.0.1 sql=SELECT DISTINCT num_releve FROM llx_bank WHERE fk_account= AND num_releve IS NOT NULL ORDER BY num_releve DESC LIMIT 16 2018-04-05 11:17:02 ERR 127.0.0.1 DoliDBMysqli::query SQL Error message: DB_ERROR_SYNTAX You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND num_releve IS NOT NULL ORDER BY num_releve DESC LIMIT 16' at line 1 2018-04-05 11:17:02 ERR 127.0.0.1 Error url=/dolibarr/htdocs/compta/bank/bankentries.php?action=reconcile&search_conciliated=0&contextpage=banktransactionlist-4&ref=3887&account=4, query_string=action=reconcile&search_conciliated=0&contextpage=banktransactionlist-4&ref=3887&account=4, sql=SELECT DISTINCT num_releve FROM llx_bank WHERE fk_account= AND num_releve IS NOT NULL ORDER BY num_releve DESC LIMIT 16, db_error=You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND num_releve IS NOT NULL ORDER BY num_releve DESC LIMIT 16' at line 1

Steps to reproduce the behavior

Select a bank account Ask for "Rapprochement" Get the error

atm-maxime commented 6 years ago

Works for me in 6.0.7. Ok for you ?

papoteur-mga commented 6 years ago

I'm doing a testing phase with 7.0.1. I have duplicated the database. I get the same error. Thus, I'm not very OK :/

papoteur-mga commented 6 years ago

With further tests, the procedure is: Select a bank account Go to the page 2, the last one. Ask for "Rapprochement" Get the error I don't get the error from the first page.