Rules Engine: Query Fail - But not on all

 [Fri Nov 29 16:19:07.199763 2019] [php7:notice] [pid 48822] [client 10.0.9.181:50244] SQL Error with statement:query failed: SELECT PASSWORD(CONCAT( id, include_flag, required_flag, method, method_detail, value )) AS guid, rule_filter.*\n       FROM rule_filter WHERE id = ?--Your password does not satisfy the current policy requirements==>/var/www/html/interface/super/rules/library/RuleManager.php at 198:sqlStatement, referer: http://10.0.1.227/interface/super/rules/index.php?action=browse!list
 [Fri Nov 29 16:19:18.727337 2019] [php7:notice] [pid 48817] [client 10.0.9.181:50273] SQL Error with statement:query failed: SELECT PASSWORD(CONCAT( id, group_id, include_flag, required_flag, method, value, rule_target.interval )) AS guid, rule_target.*\n       FROM rule_target WHERE id = ?--Your password does not satisfy the current policy requirements==>/var/www/html/interface/super/rules/library/RuleManager.php at 211:sqlStatement, referer: http://10.0.1.227/interface/super/rules/index.php?action=browse!list

5.0.1(7)
PHP 7.2
MySQL 5.7.28
Ubuntu 18.04

I get a Query Error when accessing any of the clinical rules. Or trying to save a new rule.
I am unable to locate the statement “Your password does not satisfy the current policy” anywhere in the codebase. I think that is being injected by PHP.

image

I have checked the permissions on the folders.
I have accessed all the tables from the database directly.
The rules still work in the patient’s chart.
I have checked my php.ini file for changes. None that I could find.
I checked the database password and it is like this ZhwcsB8uwZ38bQ3@.
So, I can’t figure out where this minimum requirement statement is coming from.

Any suggestions would be greatly appreciated.

UPDATE
After doing some searching for SELECT PASSWORD(CONCAT. I found https://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html#function_password.
The SELECT PASSWORD is a MySQL function. I can’t find guid anywhere in the database.

I forgot to mention that this install was previously on a Windows 2008R2 server and we moved it to Linux 6 months ago.

@juggernautsei,

It is a mysql exception.

The rule which you created in openemr is quite weak,it requires a strong values.

In general, OpenEMR rules creation will undergo the password policy checks.

Please let us know for further clarifications.

Thanks,
ViSolve-121

@visolveemr
Yes, I need more clarification. I am looking at the password policy in MySQL and it looks like this.

image

I checked another system and the password policy is exactly the same. What I am thinking is that in the beginning the password policy may have been lower with the guid was originally stored in the system. And now when the code generates the guid. The guid does not meet the current minimum requirements. So, I would need to lower the requirements. Which was not the case. I removed the requirement for a special character. The query error still occurs.

Thank you!

UPDATE
I can create a new rule partially. Meaning, the system will store the creation of the rule but I can’t add Criteria to the entry. The query error is displayed.

Hi,

Related issue just posted here:
Query Error: Your password does not satisfy the current policy requirements · Issue #2827 · openemr/openemr · GitHub

It seems really odd that we are using the mysql PASSWORD call in those queries?? Are they needed?

-brady

@juggernautsei,
We also tried with multiple ways until the password policy is set to LOW, the query error occurs at simple rules criteria creation.

Since the MySQL Password function is used in the queries for the Rule Creation, it occurs. Refer the following file,

FILE: interface/super/rules/library/RuleManager.php

Thanks,
ViSolve-122

I am in the process of rewriting the Rules engine with a bootstrap interface. The queries you are discussing use the PASSWORD function to create a guid when editing and it is not needed. When done I will put up a PR. This will need to be massaged for CSRF functionality by Brady, ui/PSR-4 issues by Robert and left-to-right by Matrix. The goal is to make the Rules engine more understandable and easy to navigate. There will be a few additional features like messaging a provider when a criterion is met for a given rule but all the current functionality will remain. Looking forward to help bringing this to fruition.

3 Likes

How far along are you in the process?

The CDR engine is in need of a lot of work. I have it working now without errors but it needs a “facelift”. That is taking the most time. Secondly, it needs better terminology, workflows and descriptions so new Rules can be easily written and implemented. What I can do is put up a branch as I work and have Brady mirror it on one of the development branches so you can see what’s what.

From a development standpoint, thinking mobile et al, what is the current thinking re: help files? Is it better to embed them or do like I see lately, utilizing a pop-up help page listing everything there? Or link out to a separate page, maybe the Wiki?

hi @rmagauran, guessing pop-up help pages, there’s also a lot of work currently ongoing to migrate to BS4

Is anyone doing this work already on the CDR engine? No sense in duplicating it…

doubt it, thinking they’re just going to replace any bs3 components with bs4 so your work would be very valuable and the merge can be dealt with when they’re done

@rmagauran
could you post a branch? That would help since you have it working now.

I will post the branch shortly. I had to learn BS4 and the special MVC structure to this part of the code, to get this out to where others could chip in… So it is not actually working yet. Understanding the What-Where-When-Why for the CDR engine is complex and putting that into a logical, easy to understand UI is a fun :thinking:challenge. I know my coding and UI skills are childlike but I keep trying to learn new things - one of the most valuable parts of the open source world for me. It is going to look different but I hope that this very powerful CDR engine code, added so many years ago, will become easy to use even by clinicians like me! Thank you for your patience, and kindness. 90% there…

I have a couple of questions about intervals that I need to clarify in my head before integrating this into the interface.

I think I understand that Clinical Warnings apply to Active and Passive Alerts, but I need to understand exactly how. Let’s look at a Rule that is checked only as an Active Alert with Intervals set to:

Clinical Warning: 2 Weeks, Past due: 1 Months

  1. When this Active alert/Rule is reached/matched, a pop-up will happen. When
    does the pop-up happen - I presume on the first day it matches right? Does anything show up in the CR Widget on the demographics page? Does it pop-up anytime the file is opened between today and 2 weeks from today? What happens after 2 weeks? After 1 month, it stops Popping up if the Action is not executed/performed? If this Rule is not marked also as a Passive Alert (where it would be shown as DUE when overdue), that’s it?

  2. When a Rule is checked as Active Alert and Passive Alert, it pops up as above and shows up in the CR Widget. When past due @ 1 month, it is displayed as Past Due. What happens in the CR Widget between 2 weeks and 1 month?

  3. Patient Reminders have similar intervals but I presume they behave differently. When due, one message is supposed to be triggered and when late, if the Action has not been reached, a second message using a different template would be sent.

hi @rmagauran, think it’s

  1. until it’s satisfied it will appear as due in the clinical reminders widget although it looks like there is a bug currently so that only when it’s also set as a passive reminder will it display there (edited), issue and proposed fix session_write_close, fragments and bad session data · Issue #2834 · openemr/openemr · GitHub
  2. think it displays due soon
  3. not sure but the patient reminders would have to be managed under Administration->Patient Reminders

Although CDR does the job well (essential for MU certification), the terminology is confusing. Add the inherent complexity and it becomes harder to decipher/debug.

If you are working on this code, it would be great help if users get an explanation - e.g. rather than getting alert/display and acting on it.

XYZ is past due

When staff calls patients to follow up, it is useful for staff to have the complete context like

XYZ should be checked every 90 days. Last check was on m/d/yy (nn days ago).

We ended up writing rule specific reports to generate action list.

The first iteration of the Clinical Reminders is up as a working branch at

https://github.com/ophthal/openemr/tree/5.0.3_CR

I use different terminology because I was very confused… The fog is lifting…
It is BS4 based and I am following along the discussions on the developer side on BS4.

Just a start, lots of polishing ahead. Help appreciated…

I am buried in the newRx stuff for now. I will aid as soon as I can get this Rx launched.

Hi. I posted this analysis on a different thread. Posting here as well since this seems to be where reminder developers look

I’ve been trying to figure out the rule processing behavior for a long time, and it has not made sense to me. Finally did a lot of debugging and looking at the code in library\clinical_rules.php and found the source of the confusion.

The code has a bug (and probably has for a very long time)

I’ve only examined the case when a target of type “custom” is being used.

I’m looking at version 5.0.2 patch 4

In test_rules_clinic(), the logic tests a rule target against 3 target dates, in this order:
– today + the warning interval
– today
– today - the past due interval

The logic examines the interval: target date less target interval -> target date, checking the rule_patient_data table to see if a row in the table exists for the patient that matches this interval, category/item pair, and frequency (as defined by the rule target)

So, for example, if the target specifies
– reminder, prostate cancer screening, completed=yes, frequency > 0, interval 12 months
then a db query is done looking for at least one row with the interval described above and the specified category/item combination and completed status

If the target criteria is found in the db, the rule status is set to “not due”, and processing stops.
If the target criteria is not satisfied, the status is set to “soon_due”, “due”, or “past_due” depending whether the first, second, or third target date is being examined, and then the next target date is tried (if there is another target date to try)

To me this seems wrong!

To me, the logic should be:
– if, for the first target date, the target criteria is satisfied, then status = “not due”
– else, if, for the second target date, the target criteria is satisfied, then status = “soon_due”
– else, if, for the third target date, the target criteria is satisfied, then status = “due”
– else, status = “past_due”

Example: suppose the warning interval is 1 month, the past due interval is 3 months, and the target interval is 12 months. This should mean:
– the target action should be performed every 12 months
– if today, the last time the action was performed was within the past 11 months, the rule is “not due”
– if it was last done between 11 and 12 months ago, the rule is “due soon”
– if it was last done between 12 and 15 months ago, the rule is “due”
– if it was last done more than 15 months ago, or never done, the rule is “past due”

I modified clinical_rules.php to match my desired logic, replacing

$action_plus[‘due_status’] = “not_due”;

with

if ($dateCounter == 1)
$action_plus[‘due_status’] = “not_due”;
elseif ($dateCounter == 2)
$action_plus[‘due_status’] = “soon_due”;
else // $dateCounter == 3
$action_plus[‘due_status’] = “due”;

This change is made below this code, which exists in two places:
// send to reminder results
if ($mode == “reminders-all”) {
// place the completed actions into the reminder return array
$actionArray = resolve_action_sql($rowRule[‘id’], ‘1’);
foreach ($actionArray as $action) {

with this change, the reminder status is calculated according to my expectation described above.

Without this change, by hard coding the status to “not due” whenever rule target criteria is satisfied, the rule always shows as either “not due” (if the target is satisfied any time between target_date3 less target interval -> target_date1) or “past due” (if satisfied prior to target_date3). It never gets the “due” or “soon_due” status.

Not sure how to get this reviewed or part of an official release (I’m new to this community)

Hope this helps someone

thank you very much @hanksterr7, please see the contributing guide to see how easy it is to check out the code, alter it, test it, and submit a pull request

we’ll be here to help you on your way :slight_smile:

Ok, I’ll look into contributing the change, according to the guide. Thanks