When using automation, numeric values may be treated as strings #4744
Description
Hey,
I recently discovered a bug in the automation rules and I'm surprised noone mentioned this yet.
The operators in the automatic graph creation criteria, e.g. 'greater than', 'less than or equal' etc. are always performing alphabetical instead of numerical comparison due to forced escaping in the sql query.
This might be desireable in some cases but it caused me some issues, as it the following example statements evaluate to true:
'200' < '3'
'50' >= '1000000'
Steps to reproduce the behavior:
- Create a graph rule, add graph creation criteria
- Set operator to 'is less than or equal'
- Select a field with a known value, that value might be 900
- Set 'Matching Pattern' to 1000
- See that it is not finding your device entry with value 900
I suggest this fix:
Original file: api/api_automation.php, function build_rule_item_filter, line ~1721-1724:
$sql_filter .= ' ' . $automation_op_array['op'][$automation_rule_item['operator']] . ' ';
if ($automation_op_array['binary'][$automation_rule_item['operator']]) {
$sql_filter .= (db_qstr($automation_op_array['pre'][$automation_rule_item['operator']] . $automation_rule_item['pattern'] . $automation_op_array['post'][$automation_rule_item['operator']]));
}
To disable escaping numerical values:
$sql_filter .= ' ' . $automation_op_array['op'][$automation_rule_item['operator']] . ' ';
if ($automation_op_array['binary'][$automation_rule_item['operator']]) {
// Fix:
$query_value = $automation_op_array['pre'][$automation_rule_item['operator']] . $automation_rule_item['pattern'] . $automation_op_array['post'][$automation_rule_item['operator']];
// Apply unescaped query to numeric values and numeric comparison operators
if( is_numeric($query_value) && $automation_rule_item['operator'] >= AUTOMATION_OP_LT && $automation_rule_item['operator'] <= AUTOMATION_OP_GE )
$sql_filter .= $query_value;
else
$sql_filter .= db_qstr($query_value);
}
If the matching sequence is not numeric, the old alphabetical style comparison will still be used.