Automation causes SQL syntax errors when invalid operations are present

[nuno-silva]

Describe the bug

Adding a new "Graph Create Criteria" to an Automation Graph Rule causes an SQL syntax error when and Operation is missing on an item.

To Reproduce

Steps to reproduce the behavior:

1. Go to Automation -> Graph Rules

2. Add a new Rule:

• Name: something
• Data Query: Net-SNMP - Get Monitored Partitions
• Graph Type: Disk Space
• Enable Rule: checked

3. add two new Graph Creation Criteria, without "Operation", e.g. for field "dskPath", Operator "does not containt", pattern "bla". See screenshots if needed.

4. Save

5. Open log; see SQL errors

Expected behaviour

UI should notify user that the Graph Creation Criteria are invalid

Screenshots

Note that item#6 has no Operation, which causes the SQL syntax error.

Log entires

2021/02/10 19:18:23 - CMDPHP SQL Backtrace: (/automation_graph_rules.php[79]:automation_graph_rules_edit(), /automation_graph_rules.php[650]:display_new_graphs(), /lib/api_automation.php[866]:db_fetch_assoc(), /lib/database.php[476]:db_fetch_assoc_prepared(), /lib/database.php[490]:db_execute_prepared())
2021/02/10 19:18:23 - CMDPHP ERROR: A DB Row Failed!, 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 'a.`dskPath` NOT LIKE '%/home/welcomeab%') LIMIT 0,50' at line 1
2021/02/10 19:18:23 - CMDPHP SQL Backtrace: (/automation_graph_rules.php[79]:automation_graph_rules_edit(), /automation_graph_rules.php[650]:display_new_graphs(), /lib/api_automation.php[858]:db_fetch_assoc(), /lib/database.php[476]:db_fetch_assoc_prepared(), /lib/database.php[490]:db_execute_prepared())
2021/02/10 19:18:23 - CMDPHP ERROR: A DB Row Failed!, 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 'a.`dskPath` NOT LIKE '%/home/welcomeab%')' at line 1

Additional context

Cacti version: 1.2.16

[TheWitness]

Yea, this is something that needs more attention. It's clear that you have a bug in your syntax. That should be masked for the most part.

[TheWitness]

Have an update to lib/api_automation.php and if the problem is now masked, please close.

[nuno-silva]

hey @TheWitness , thanks for the patch.

It suppresses one of the errors, but there is still another error, e.g:

• without the patch:

2021/02/17 21:35:56 - CMDPHP SQL Backtrace: (/automation_graph_rules.php[79]:automation_graph_rules_edit(), /automation_graph_rules.php[650]:display_new_graphs(), /lib/api_automation.php[866]:db_fetch_assoc(), /lib/database.php[476]:db_fetch_assoc_prepared(), /lib/database.php[490]:db_execute_prepared())
2021/02/17 21:35:56 - CMDPHP ERROR: A DB Row Failed!, 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 'a.`diskIODevice` LIKE '%s%') LIMIT 0,50' at line 1
2021/02/17 21:35:56 - CMDPHP SQL Backtrace: (/automation_graph_rules.php[79]:automation_graph_rules_edit(), /automation_graph_rules.php[650]:display_new_graphs(), /lib/api_automation.php[858]:db_fetch_assoc(), /lib/database.php[476]:db_fetch_assoc_prepared(), /lib/database.php[490]:db_execute_prepared())
2021/02/17 21:35:56 - CMDPHP ERROR: A DB Row Failed!, 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 'a.`diskIODevice` LIKE '%s%')' at line 1

• with the patch:

2021/02/17 21:32:22 - CMDPHP SQL Backtrace: (/automation_graph_rules.php[79]:automation_graph_rules_edit(), /automation_graph_rules.php[650]:display_new_graphs(), /lib/api_automation.php[858]:db_fetch_assoc(), /lib/database.php[476]:db_fetch_assoc_prepared(), /lib/database.php[490]:db_execute_prepared())
2021/02/17 21:32:22 - CMDPHP ERROR: A DB Row Failed!, 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 'a.`diskIODevice` LIKE '%s%')' at line 1

---

This is what I used to reproduce it this time (just clicking Save triggers the errors in the log):

[TheWitness]

Oh, did not notice that.

[TheWitness]

Check again. Fixed.

[nuno-silva]

Nice. Thanks!