Problem with "?" in crits

ghristov

Dear epesi team,

I have a strange issue I encountered on both my installations running v. 1.6.2
When any crits for searching records contain the question mark character there is an error message coming up. I tested same with the EPESI live demo and all is fine there - just typed a string containing the ? in the search.

I traced the probable reason down to EPESI\libs\adodb\adodb.inc.php line 991 but will need your advice if you have already encountered same with any installation.

Full error message

Type: E_USER_ERROR (256)

Message: mysqlt error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Any reason for that' at line 1] in EXECUTE("SELECT * FROM manage_ship_tasks_data_1 AS r WHERE active=1 AND (false OR (r.f_ship = '14' AND r.f_ship IS NOT NULL)) AND (false OR (r.f_date = '2014-07-28' AND r.f_date IS NOT NULL)) AND (false OR (r.f_description = 'Hatch cover hydraulic motor No.1 was running approx. 200 hrs. more than motor No. 2' AND r.f_description IS NOT NULL)) AND (false OR (r.f_corrective_action = 'Any reason for that")

File: C:\EPESI\libs\adodb\adodb-errorhandler.inc.php

Line=77

error backtrace:

[PHP core called function]

    function called: handle_epesi_error(256, "mysqlt error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Any reason for that' at line 1] in EXECUTE(\"SELECT * FROM manage_ship_tasks_data_1 AS r WHERE active=1 AND (false OR (r.f_ship = '14' AND r.f_ship IS NOT NULL)) AND (false OR (r.f_date = '2014-07-28' AND r.f_date IS NOT NULL)) AND (false OR (r.f_description = 'Hatch cover hydraulic motor No.1 was running approx. 200 hrs. more than motor No. 2' AND r.f_description IS NOT NULL)) AND (false OR (r.f_corrective_action = 'Any reason for that\") ", "C:\EPESI\libs\adodb\adodb-errorhandler.inc.php", 77, Array ("mysqlt", "EXECUTE", 1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Any reason for that' at line 1", "SELECT * FROM manage_ship_tasks_data_1 AS r WHERE active=1 AND (false OR (r.f_ship = '14' AND r.f_ship IS NOT NULL)) AND (false OR (r.f_date = '2014-07-28' AND r.f_date IS NOT NULL)) AND (false OR (r.f_description = 'Hatch cover hydraulic motor No.1 was running approx. 200 hrs. more than motor No. 2' AND r.f_description IS NOT NULL)) AND (false OR (r.f_corrective_action = 'Any reason for that", false, Object (ADODB_mysqlt), "SELECT * FROM manage_ship_tasks_data_1 AS r WHERE active=1 AND (false OR (r.f_ship = '14' AND r.f_ship IS NOT NULL)) AND (false OR (r.f_date = '2014-07-28' AND r.f_date IS NOT NULL)) AND (false OR (r.f_description = 'Hatch cover hydraulic motor No.1 was running approx. 200 hrs. more than motor No. 2' AND r.f_description IS NOT NULL)) AND (false OR (r.f_corrective_action = 'Any reason for that", false, "mysqlt error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Any reason for that' at line 1] in EXECUTE(\"SELECT * FROM manage_ship_tasks_data_1 AS r WHERE active=1 AND (false OR (r.f_ship = '14' AND r.f_ship IS NOT NULL)) AND (false OR (r.f_date = '2014-07-28' AND r.f_date IS NOT NULL)) AND (false OR (r.f_description = 'Hatch cover hydraulic motor No.1 was running approx. 200 hrs. more than motor No. 2' AND r.f_description IS NOT NULL)) AND (false OR (r.f_corrective_action = 'Any reason for that\") "))

File: C:\EPESI\libs\adodb\adodb-errorhandler.inc.php

    line 77

    function called: trigger_error("mysqlt error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Any reason for that' at line 1] in EXECUTE(\"SELECT * FROM manage_ship_tasks_data_1 AS r WHERE active=1 AND (false OR (r.f_ship = '14' AND r.f_ship IS NOT NULL)) AND (false OR (r.f_date = '2014-07-28' AND r.f_date IS NOT NULL)) AND (false OR (r.f_description = 'Hatch cover hydraulic motor No.1 was running approx. 200 hrs. more than motor No. 2' AND r.f_description IS NOT NULL)) AND (false OR (r.f_corrective_action = 'Any reason for that\") ", 256)

File: C:\EPESI\libs\adodb\adodb.inc.php

    line 1071

    function called: ADODB_Error_Handler("mysqlt", "EXECUTE", 1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Any reason for that' at line 1", "SELECT * FROM manage_ship_tasks_data_1 AS r WHERE active=1 AND (false OR (r.f_ship = '14' AND r.f_ship IS NOT NULL)) AND (false OR (r.f_date = '2014-07-28' AND r.f_date IS NOT NULL)) AND (false OR (r.f_description = 'Hatch cover hydraulic motor No.1 was running approx. 200 hrs. more than motor No. 2' AND r.f_description IS NOT NULL)) AND (false OR (r.f_corrective_action = 'Any reason for that", false, "Object (ADODB_mysqlt)")

File: C:\EPESI\libs\adodb\adodb.inc.php

    line 1027

    function called: _Execute("SELECT * FROM manage_ship_tasks_data_1 AS r WHERE active=1 AND (false OR (r.f_ship = '14' AND r.f_ship IS NOT NULL)) AND (false OR (r.f_date = '2014-07-28' AND r.f_date IS NOT NULL)) AND (false OR (r.f_description = 'Hatch cover hydraulic motor No.1 was running approx. 200 hrs. more than motor No. 2' AND r.f_description IS NOT NULL)) AND (false OR (r.f_corrective_action = 'Any reason for that")

File: C:\EPESI\libs\adodb\drivers\adodb-mysql.inc.php

    line 591

    function called: Execute("SELECT * FROM manage_ship_tasks_data_1 AS r WHERE active=1 AND (false OR (r.f_ship = '14' AND r.f_ship IS NOT NULL)) AND (false OR (r.f_date = '2014-07-28' AND r.f_date IS NOT NULL)) AND (false OR (r.f_description = 'Hatch cover hydraulic motor No.1 was running approx. 200 hrs. more than motor No. 2' AND r.f_description IS NOT NULL)) AND (false OR (r.f_corrective_action = 'Any reason for that? Please let No. 2 running as well' AND r.f_corrective_action IS NOT NULL)) LIMIT 0,2147483647", Array ())

[PHP core called function]

    function called: SelectLimit("SELECT * FROM manage_ship_tasks_data_1 AS r WHERE active=1 AND (false OR (r.f_ship = '14' AND r.f_ship IS NOT NULL)) AND (false OR (r.f_date = '2014-07-28' AND r.f_date IS NOT NULL)) AND (false OR (r.f_description = 'Hatch cover hydraulic motor No.1 was running approx. 200 hrs. more than motor No. 2' AND r.f_description IS NOT NULL)) AND (false OR (r.f_corrective_action = 'Any reason for that? Please let No. 2 running as well' AND r.f_corrective_action IS NOT NULL))", -1, 0, Array ())

File: C:\EPESI\include\database.php

    line 454

    function called: call_user_func_array(Array (Object (ADODB_mysqlt), "SelectLimit"), Array ("SELECT * FROM manage_ship_tasks_data_1 AS r WHERE active=1 AND (false OR (r.f_ship = '14' AND r.f_ship IS NOT NULL)) AND (false OR (r.f_date = '2014-07-28' AND r.f_date IS NOT NULL)) AND (false OR (r.f_description = 'Hatch cover hydraulic motor No.1 was running approx. 200 hrs. more than motor No. 2' AND r.f_description IS NOT NULL)) AND (false OR (r.f_corrective_action = 'Any reason for that? Please let No. 2 running as well' AND r.f_corrective_action IS NOT NULL))", -1, 0, Array (#0)))

File: C:\EPESI\modules\Utils\RecordBrowser\RecordBrowserCommon_0.php

    line 1515

    function called: SelectLimit("SELECT * FROM manage_ship_tasks_data_1 AS r WHERE active=1 AND (false OR (r.f_ship = '14' AND r.f_ship IS NOT NULL)) AND (false OR (r.f_date = '2014-07-28' AND r.f_date IS NOT NULL)) AND (false OR (r.f_description = 'Hatch cover hydraulic motor No.1 was running approx. 200 hrs. more than motor No. 2' AND r.f_description IS NOT NULL)) AND (false OR (r.f_corrective_action = 'Any reason for that? Please let No. 2 running as well' AND r.f_corrective_action IS NOT NULL))", -1, 0, Array ())

File: C:\EPESI\modules\Custom\Ships\Inspections\TaskLog\TaskLogCommon_0.php

    line 458

    function called: get_records("manage_ship_tasks", Array (14, "2014-07-28", "Hatch cover hydraulic motor No.1 was running approx. 200 hrs. more than motor No. 2", "Any reason for that? Please let No. 2 running as well"), Array (), Array (), Array (), false)

File: C:\EPESI\modules\Custom\Ships\Inspections\TaskLog\Import\ImportCommon_0.php

    line 159

    function called: get_ship_tasks(Array (14, "2014-07-28", "Hatch cover hydraulic motor No.1 was running approx. 200 hrs. more than motor No. 2", "Any reason for that? Please let No. 2 running as well"))

File: C:\EPESI\modules\Custom\Ships\Inspections\TaskLog\Import\Import_0.php

    line 465

    function called: task_existing_id(14, Array (#16))

[PHP core called function]

    function called: upload_ship_tasks("data/Utils_FileUpload/tmp_1423497999.9921.xls", "BETIS - Tasklog_27.01.2015.xls", Array ("0", "1", "0", "auto", "auto", "libs_qf_9c836f54abda21532169a86a0901c58d", "Event.fire(document,'e:submit_form','libs_qf_9c836f54abda21532169a86a0901c58d');CodePress.update($('libs_qf_9c836f54abda21532169a86a0901c58d'));_chj($('libs_qf_9c836f54abda21532169a86a0901c58d').serialize()+'&__action_module__=%2FBase_Box%7C0%2FCustom_Ships_Inspections_TaskLog_Import%7Cmain_0%2FUtils_FileUpload%7Ccc','Processing file...','');", "104857600", "Upload"))

File: C:\EPESI\modules\Utils\FileUpload\FileUpload_0.php

    line 158

    function called: call_user_func_array(Array (Object (Custom_Ships_Inspections_TaskLog_Import), "upload_ship_tasks"), Array ("data/Utils_FileUpload/tmp_1423497999.9921.xls", "BETIS - Tasklog_27.01.2015.xls", Array (#9)))

[PHP core called function]

    function called: submit_parent(Array ("0", "1", "0", "auto", "auto", "libs_qf_9c836f54abda21532169a86a0901c58d", "Event.fire(document,'e:submit_form','libs_qf_9c836f54abda21532169a86a0901c58d');CodePress.update($('libs_qf_9c836f54abda21532169a86a0901c58d'));_chj($('libs_qf_9c836f54abda21532169a86a0901c58d').serialize()+'&__action_module__=%2FBase_Box%7C0%2FCustom_Ships_Inspections_TaskLog_Import%7Cmain_0%2FUtils_FileUpload%7Ccc','Processing file...','');", "104857600", "Upload"))

File: C:\EPESI\modules\Libs\QuickForm\3.2.11\HTML\QuickForm.php

    line 1670

    function called: call_user_func(Array (Object (Utils_FileUpload), "submit_parent"), Array ("0", "1", "0", "auto", "auto", "libs_qf_9c836f54abda21532169a86a0901c58d", "Event.fire(document,'e:submit_form','libs_qf_9c836f54abda21532169a86a0901c58d');CodePress.update($('libs_qf_9c836f54abda21532169a86a0901c58d'));_chj($('libs_qf_9c836f54abda21532169a86a0901c58d').serialize()+'&__action_module__=%2FBase_Box%7C0%2FCustom_Ships_Inspections_TaskLog_Import%7Cmain_0%2FUtils_FileUpload%7Ccc','Processing file...','');", "104857600", "Upload"))

[PHP core called function]

    function called: process(Array (Object (Utils_FileUpload), "submit_parent"))

File: C:\EPESI\modules\Libs\QuickForm\QuickForm_0.php

    line 82

    function called: call_user_func_array(Array (Object (HTML_QuickForm), "process"), Array (Array (#2)))

File: C:\EPESI\modules\Utils\FileUpload\FileUpload_0.php

    line 143

    function called: __call("process", Array (Array (#2)))

File: C:\EPESI\modules\Utils\FileUpload\FileUpload_0.php

    line 143

    function called: process(Array (Object (Utils_FileUpload), "submit_parent"))

[PHP core called function]

    function called: body(Array (Object (Custom_Ships_Inspections_TaskLog_Import), "upload_ship_tasks"))

File: C:\EPESI\include\module.php

    line 793

    function called: call_user_func_array(Array (Object (Utils_FileUpload), "body"), Array (Array (#2)))

File: C:\EPESI\include\module.php

    line 713

    function called: get_html_of_module(Object (Utils_FileUpload), Array (Array (#2)), null)

File: C:\EPESI\modules\Custom\Ships\Inspections\TaskLog\Import\Import_0.php

    line 110

    function called: display_module("Object (Utils_FileUpload)", Array (Array (#2)))

[PHP core called function]

    function called: tab_import()

File: C:\EPESI\modules\Utils\TabbedBrowser\TabbedBrowser_0.php

    line 115

    function called: call_user_func_array(Array (Object (Custom_Ships_Inspections_TaskLog_Import), "tab_import"), Array ())

File: C:\EPESI\modules\Utils\TabbedBrowser\TabbedBrowser_0.php

    line 81

    function called: display_contents(Array (0, Array (#2), Array (#0), false), 0)

File: C:\EPESI\modules\Custom\Ships\Inspections\TaskLog\Import\Import_0.php

    line 83

    function called: body()

[PHP core called function]

    function called: body()

File: C:\EPESI\include\module.php

    line 793

    function called: call_user_func_array(Array (Object (Custom_Ships_Inspections_TaskLog_Import), "body"), Array ())

File: C:\EPESI\include\module.php

    line 713

    function called: get_html_of_module(Object (Custom_Ships_Inspections_TaskLog_Import), null, null)

File: C:\EPESI\modules\Base\Box\Box_0.php

    line 151

    function called: display_module("Object (Custom_Ships_Inspections_TaskLog_Import)")

File: C:\EPESI\include\epesi.php

    line 241

    function called: body()

File: C:\EPESI\include\epesi.php

    line 286

    function called: go(Object (Base_Box))

File: C:\EPESI\process.php

    line 35

    function called: process("_qf__libs_qf_9c836f54abda21532169a86a0901c58d=&submited=0&required=1&cid=0&form_name=libs_qf_9c836f54abda21532169a86a0901c58d&submit_js=Event.fire(document%2C'e%3Asubmit_form'%2C'libs_qf_9c836f54abda21532169a86a0901c58d')%3BCodePress.update(%24('libs_qf_9c836f54abda21532169a86a0901c58d'))%3B_chj(%24('libs_qf_9c836f54abda21532169a86a0901c58d').serialize()%2B'%26__action_module__%3D%252FBase_Box%257C0%252FCustom_Ships_Inspections_TaskLog_Import%257Cmain_0%252FUtils_FileUpload%257Ccc'%2C'Processing%20file...'%2C'')%3B&MAX_FILE_SIZE=104857600&ship_imo=auto&tasklog_type=auto&button=Upload&__action_module__=%2FBase_Box%7C0%2FCustom_Ships_Inspections_TaskLog_Import%7Cmain_0%2FUtils_FileUpload%7Ccc", false)

ajb

Yes - we know about that.

The reason of that is the Utils_RecordBrowserCommon::build_query method. Instead of returning $args it puts values into a query string. Then adodb handles all question marks as params.
Maybe adodb's solution is not perfect, but we screwed it up. We have to change build_query method, but as you can see it's a real mess.

My idea is to rewrite crits mechanism to the objective way with a wrapper for the old, array crits. This array like crits are convenient in many cases, however they don't allow to use full logic.
This will also fix question mark issues.

Now you can do a quick fix for that: replace all question marks with underscore '_' and use ~ for like

array('test' => 'value?');

// change to

array('~test' => 'value_');

Of course it's not the same. But it should work for you right now. At least in your example.

Regards,
Adam

ghristov

Hi Adam,

Thanks for quick reply with full explanation. But how did you make it work on the live demo? 🙂

Anyway, I also had the idea to work on an upgrade to the crits routine that will be based on the string generated by a filter builder like http://mistic100.github.io/jQuery-QueryBuilder/. Then the full functionality of the crits can be used to build complex queries (using crits build with such string) and also an "advanced filtering" can be available which has better flexibility than the filtering now. This can be extended with saving of predefined filter settings which can be linked to the Utils/Tray module.
Have you considered that approach?
I would say plenty ideas from my side but not much time at the moment so I take it one at a time.

brgds,
George

ajb

Please tell me, which demo, and which search form 🙂
I've got the error on http://demo.epesibim.com/ -> Opened contacts -> used search for the recordset.

Nice query builder - I'll look at this right now.

ghristov

Hi Adam,

It is the same demo I used http://demo.epesibim.com, but I tried with the general search form (top right corner). It does not make trouble. The CRM->Contacts search on the demo does trigger error.
On my installation I get error even on the general search form.

My idea with the query builder string is to be build on top of the present crits routine. It is easy to detect which type of crits are used (old type or query-builder style) in the Utils_RecordBrowserCommon::build_query() and then build the query accordingly.

brgds,
George

ajb

For me it works in the quicksearch even now in the dev branch.

I'd like to do some Crits object and write some wrapper to build Crits object from array crits.
As you've written build_query is the right place to detect, but also we need to change merge_crits, as it's the place that causes some issues for complicated queries. Also crits_to_words, but it's only for UI. Maybe something more, but those are the places that I remember.

Regards,
Adam

employeeoftheyear

Hi,

I'd like to push up the "question mark" issue. Somehow one of our users tried to create or search for a company named "Neulußheim", which somehow got translated to "Neulu?heim". This results in non-userfriendly error messages and prevents the user from using EPESI, when e.g. used as filter arguments.

Steps to reproduce:
[list=1:2f6otyg9]
[*:2f6otyg9]Open EPESI and log in, for example: http://demo.epesibim.com/[/*:m:2f6otyg9]
[*:2f6otyg9]Launch Pad -> Companies[/*:m:2f6otyg9]
[*:2f6otyg9]Now search for example in "City" (table header) for "?"[/*:m:2f6otyg9]
[*:2f6otyg9]Error messages in Companies, Contacts, etc. (in our case EPESI was unusable until logout-login)[/*:m:2f6otyg9][/list:o:2f6otyg9]

Would be nice, if this issue could be fixed. 🙂

ajb

Ok, I'll try to do something with that, however searching for "Neulußheim" works for me.

employeeoftheyear

Yes, "ß" works fine for me, too. But somehow our user managed to convert the "ß" to "?". But I don't have any ways to reproduce that, because I don't know what exactly has been done prior to the support request. 🙁
So my naïve hope is, that this bug won't appear again, when the filter accept "?" everywhere. 🙂

Epesi ENS | Epesi Academy | epesi.cloud PaaS | GitHub

Help Me! | Download from SourceForge