Rules Engine: Query Fail - But not on all

infrastructure

(Sherwin Gaddis) #1
 [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.


(ViSolve) #2

@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


(Sherwin Gaddis) #3

@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.


(Brady Miller) #4

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


(ViSolve) #5

@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


(R Magauran) #6

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.


(Sherwin Gaddis) #7

How far along are you in the process?


(R Magauran) #8

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?


(Stephen Waite) #9

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


(R Magauran) #10

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


(Stephen Waite) #11

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


(Sherwin Gaddis) #12

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


(R Magauran) #13

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.


(Stephen Waite) #14

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