Bug in Wordpress plugin Ultimate Member

[JohnWDawson]

Bug found in the Ultimate Member plugin for Wordpress. Reported to Ultimate Member, but their team showed that bug not manifest in a native MySQL host, hence possibly related to use of SqlServer / ProjectNami. Bug visible in this screenshot; evidently cannot see page definitions:

[patrickebates]

Do you have the PHP error log and/or the PN translation log file available?

[JohnWDawson]

Debug switched on, output here:
Debug.txt

[JohnWDawson]

Sorry, what's the PN translation log?

[JohnWDawson]

Can offer a little more; tried the (apparently) offending query in the debug log (above); seems to fail similarly when tried in SQL Server Management log, but is OK if double quotes are replaced by single quotes; related to SET QUOTED_IDENTIFIER { ON | OFF }? Could it be this simple?

[JohnWDawson]

And finally; tested in SQL Server Management Studio

[JohnWDawson]

And with it off:

[patrickebates]

There are several things in SQL Server which break if this is left off, per
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-quoted-identifier-transact-sql?view=sql-server-2017

I examined the translations, and seems that we have tightly controlled when double-quotes are replaced with single-quotes in order to prevent damaging post content. At the moment, I'm not settled on how we would catch this instance without causing more problems.

There is a simple fix for it by editing a line in the plugin itself. /includes/core/class-query.php, line 97. Simply swap all single-quotes and double-quotes on that line and it should work properly.

[patrickebates]

Looking through the rest of that same file, the bigger question is why they aren't using WP_Query or get_posts like they do elsewhere. This query which is giving you trouble seems to be the exception to their coding standards.

[JohnWDawson]

If single quotes are used for literals in SQL, which is the preferred practice, then it doesn't matter about QUOTED_IDENTIFIER, so this seems to be the way to go, so line 97 in class-query.php goes
from: 'SELECT * FROM '.$wpdb->posts.' WHERE post_type = "page" AND post_status = "publish" '
to: 'SELECT * FROM '.$wpdb->posts.' WHERE post_type = 'page' AND post_status = 'publish' '
so the SQL looks like: SELECT * FROM wp_posts WHERE post_type = 'page' and post_status = 'publish'
which works ...
Thanks for help; will pass onto UM