Trying to save new Email Bounce Handling Account and get a Database failure message.

SuiteCRM Forum - English Language SuiteCRM General Discussion
1

System Info
Windows Server 2019 build 1809
IIS 10.0.17763.1
PHP 7.2.7 x64
SuiteCRM Version 7.11.0
Sugar Version 6.5.25 (Build 344)

This is a brand new installation non-container installation using IIS. When I try to save a Bounce Handling Account I am creating I get the following error - “Database failure. Please refer to suitecrm.log for details.”

I checked the php.log file but nothing is logged there when I recreate the issue. I then checked the SuiteCRM.log file. In the SuiteCRM.log I am seeing random N’s logged in the query string in front of the Name and several ID values which would ovbiously cause the syntax error that the odbc driver is returning,

UPDATE folders SET name = N’Bounce Account’, parent_folder = ‘’, dynamic_query = ‘’, assign_to_id = N’98c175af-45b6-9636-4846-5c535e494380’, modified_by = N’98c175af-45b6-9636-4846-5c535e494380’ WHERE id = N’82492116-9f31-a392-cf90-5c86d625d15d’

[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ‘`’.

So thinking there was some syntax issue in the code I checked out the source code where the query was being generated, SugarFolders.php just above line # 1267, but everything seems fine. Anyone else ever experienced this?

SugarFolders.php Code Snippit:

$query = "UPDATE folders SET " .
"name = " . $this->db->quoted($this->name) . ", " .
"parent_folder = " . $this->db->quoted($this->parent_folder) . ", " .
"dynamic_query = " . $this->db->quoted($this->dynamic_query) . ", " .
"assign_to_id = " . $this->db->quoted($this->assign_to_id) . ", " .
"modified_by = " . $this->db->quoted($this->currentUser->id) . " " .
"WHERE id = " . $this->db->quoted($this->id);

SuiteCRM.log:

Mon Mar 11 15:41:40 2019 [1280][98c175af-45b6-9636-4846-5c535e494380][FATAL] Query Failed:UPDATE folders SET name = N’Bounce Account’, parent_folder = ‘’, dynamic_query = ‘’, assign_to_id = N’98c175af-45b6-9636-4846-5c535e494380’, modified_by = N’98c175af-45b6-9636-4846-5c535e494380’ WHERE id = N’82492116-9f31-a392-cf90-5c86d625d15d’::: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ‘'. Mon Mar 11 15:41:40 2019 [1280][98c175af-45b6-9636-4846-5c535e494380][FATAL] Exception handling in C:\inetpub\SuiteCRM\include\MVC\Controller\SugarController.php:402 Mon Mar 11 15:41:40 2019 [1280][98c175af-45b6-9636-4846-5c535e494380][FATAL] Exception in Controller: Database failure. Please refer to suitecrm.log for details. Mon Mar 11 15:41:40 2019 [1280][98c175af-45b6-9636-4846-5c535e494380][FATAL] backtrace: #0 C:\inetpub\SuiteCRM\include\database\DBManager.php(353): sugar_die('Database failur...') #1 C:\inetpub\SuiteCRM\include\database\DBManager.php(328): DBManager->registerError(' Query Failed:U...', ' Query Failed:U...', true) #2 C:\inetpub\SuiteCRM\include\database\SqlsrvManager.php(235): DBManager->checkError(' Query Failed:U...', true) #3 C:\inetpub\SuiteCRM\include\SugarFolders\SugarFolders.php(1267): SqlsrvManager->query('UPDATEfolders…’, true)
#4 C:\inetpub\SuiteCRM\modules\InboundEmail\InboundEmail.php(7669): SugarFolder->save(false)
#5 C:\inetpub\SuiteCRM\modules\InboundEmail\Save.php(180): InboundEmail->createAutoImportSugarFolder()
#6 C:\inetpub\SuiteCRM\include\MVC\View\SugarView.php(832): include_once(‘C:\inetpub\Suit…’)
#7 C:\inetpub\SuiteCRM\include\MVC\View\views\view.classic.php(75): SugarView->includeClassicFile(‘modules/Inbound…’)
#8 C:\inetpub\SuiteCRM\include\MVC\View\SugarView.php(226): ViewClassic->display()
#9 C:\inetpub\SuiteCRM\include\MVC\Controller\SugarController.php(432): SugarView->process()
#10 C:\inetpub\SuiteCRM\include\MVC\Controller\SugarController.php(375): SugarController->processView()
#11 C:\inetpub\SuiteCRM\include\MVC\SugarApplication.php(109): SugarController->execute()
#12 C:\inetpub\SuiteCRM\index.php(52): SugarApplication->execute()
#13 {main}

2

After digging a little more I found that it wasn’t the “N” causing the error and that the MSSQL subclass of the DBManager class actually has a function called _appendN() that adds that in front of the value. Then I realized that I totally missed the fact that there were backticks (`) that I initially mistook for apostrophes(’) separating the table name and field names in the query string. I removed those on all the queries in the Sugarfolders.save() function and it now works. Upon further inspection of the Sugarfolder class file, it looks like there are a lot of other queries that do the same thing with the backticks and I will need to clean out the backticks for those queries as well.

Old and Broken query string:

        $query = "UPDATE `folders` SET " .
            "`name` = " . $this->db->quoted($this->name) . ", " .
            "`parent_folder` = " . $this->db->quoted($this->parent_folder) . ", " .
            "`dynamic_query` = " . $this->db->quoted($this->dynamic_query) . ", " .
            "`assign_to_id` = " . $this->db->quoted($this->assign_to_id) . ", " .
            "`modified_by` = " . $this->db->quoted($this->currentUser->id) . " " .
            "WHERE `id` = " . $this->db->quoted($this->id);

New and working query string:

        $query = "UPDATE folders SET " .
            "name = " . $this->db->quoted($this->name) . ", " .
            "parent_folder = " . $this->db->quoted($this->parent_folder) . ", " .
            "dynamic_query = " . $this->db->quoted($this->dynamic_query) . ", " .
            "assign_to_id = " . $this->db->quoted($this->assign_to_id) . ", " .
            "modified_by = " . $this->db->quoted($this->currentUser->id) . " " .
            "WHERE id = " . $this->db->quoted($this->id);