Editor-PHP-2.0.8 SearchBuilderOptions.php does not quote column names in GROUP BY

Editor-PHP-2.0.8 SearchBuilderOptions.php does not quote column names in GROUP BY

fieldsresearchfieldsresearch Posts: 3Questions: 2Answers: 0
edited June 2022 in Bug reports

Error messages shown:

{"fieldErrors":[],"error":"An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'tagged.nurse' in 'group statement'","data":[],"ipOpts":[],"cancelled":[],"debug":[{"query":"SELECT `responses`.`id` as 'responses.id', `responses`.`record_last_updated_by_survey` as 'responses.record_last_updated_by_survey', `responses`.`sample_mo` as 'responses.sample_mo', `patients`.`unit` as 'patients.unit', `responses`.`sample_yr` as 'responses.sample_yr', `responses`.`survey_version` as 'responses.survey_version', `tagged`.`nurse-courtesy-respect` as 'tagged.nurse-courtesy-respect' FROM `DATABASE`.`RESPONSE_TABLE` responses LEFT JOIN `DATABASE`.`PATIENTS_TABLE` patients ON `patients`.`id` = `responses`.`patients_uploaded_id` LEFT JOIN `DATABASE`.`TAGGED_VIEW` tagged ON `responses`.`id` = `tagged`.`id` WHERE `responses`.`agency_num` = :where_0 ","bindings":[{"name":":where_0","value":"010118","type":null}]},{"query":"SELECT `patients`.`unit` as 'value', `patients`.`unit` as 'label' FROM `DATABASE`.`RESPONSE_TABLE` responses LEFT JOIN `DATABASE`.`PATIENTS_TABLE` patients ON `patients`.`id` = `responses`.`patients_uploaded_id` LEFT JOIN `DATABASE`.`TAGGED_VIEW` tagged ON `responses`.`id` = `tagged`.`id` GROUP BY patients.unit","bindings":[]},{"query":"SELECT `responses`.`sample_yr` as 'value', `responses`.`sample_yr` as 'label' FROM `DATABASE`.`RESPONSE_TABLE` responses LEFT JOIN `DATABASE`.`PATIENTS_TABLE` patients ON `patients`.`id` = `responses`.`patients_uploaded_id` LEFT JOIN `DATABASE`.`TAGGED_VIEW` tagged ON `responses`.`id` = `tagged`.`id` GROUP BY responses.sample_yr","bindings":[]},{"query":"SELECT `responses`.`survey_version` as 'value', `responses`.`survey_version` as 'label' FROM `DATABASE`.`RESPONSE_TABLE` responses LEFT JOIN `DATABASE`.`PATIENTS_TABLE` patients ON `patients`.`id` = `responses`.`patients_uploaded_id` LEFT JOIN `DATABASE`.`TAGGED_VIEW` tagged ON `responses`.`id` = `tagged`.`id` GROUP BY responses.survey_version","bindings":[]},{"query":"SELECT `tagged`.`nurse-courtesy-respect` as 'value', `tagged`.`nurse-courtesy-respect` as 'label' FROM `DATABASE`.`RESPONSE_TABLE` responses LEFT JOIN `DATABASE`.`PATIENTS_TABLE` patients ON `patients`.`id` = `responses`.`patients_uploaded_id` LEFT JOIN `DATABASE`.`TAGGED_VIEW` tagged ON `responses`.`id` = `tagged`.`id` GROUP BY tagged.nurse-courtesy-respect","bindings":[]}]}

Description of problem:
It appears that you don't escape the column name in the GROUP BY statement the same way as you do in other places.

The column was defined as

$fields[] = Field::inst("tagged.nurse-courtesy-respect")
    ->setFormatter( Format::ifEmpty(null) )
    ->searchBuilderOptions(
        SearchBuilderOptions::inst()
    );

$editor->field($fields);

Not accounting for this can cause problems depending on how people name their columns.

Answers

Sign In or Register to comment.