Hi. I am looking at your plugin on behalf of the Plugins Guardians. Thanks for dealing with the issues raised by the codechecker. The code looks very well written.

I note in your "define.class.php::define_after_data" function, you have a variable "$i" defined, that breaks from a loop if it reaches 12. I believe that is to limit the sample data displayed on the profile definition screen 12 examples. But, you never increment "$i", so the sample data displayed is always the entire data set. I think that is not what you meant.
Instead of that, I would recommend changing the "get_records_sql" call to include data limits. So use something like: "$rs = $DB->get_records_sql($sql, null, 0, 12);" instead. Then you can remove the use of "$i" completely.

The biggest concern I have is with performance and security. You have noted that this is "intended for developers and very advanced moodle users". I think you need to really point out that use of this can damage a site if improper SQL is entered as a field.

From a security standpoint, because you are using "get_records_sql" directly when the field is being defined, SQL such as "drop table" or "delete from" will be executed on the database if entered in the "SQL query" field. Doing so will generate an error, and not create the profile field, but only after the sql has been executed. This could allow a user with the ability to create profile fields to inadvertently or intentionally damage a database in a way not provided by any other mechanisms on the site. I wonder if there could be a way to check the SQL such that it can only contain "select" as a valid query?

From a performance standpoint, badly performing SQL could be entered that could severely impact the site if the profile fields are accessed.

I can see how the use of this field could be very powerful. I think it is just important to make sure users of the plugin know the risks involved with using it.

Hi Mike, thanks for your review.
You are right about security and performance, in fact, besides a notice in the readme about the risks of using this plugin, I have also provided a capability: profilefield/dynamicmultiselect:caneditsql that by default is not enabled. So an admin must enable the capability for a user/role before that role being able to modify the select query.
I have also provided a new version which fixes the bug of the number of records retrieved for the sample set. I have used the suggestion you provided to limit the number of rows returned by the query.

Thanks guys

Hi Antonello. The change looks good, although you might think of using less than 100 for the sample size. 100 returns screen scrolling necessary. But I imagine, in most cases, the field will be used for smaller data sets.
I think you cannot warn users enough. Having dealt with hundreds of Moodle admins, mistakes are made. I really think it would benefit potential users to say something like:
"This plugin allows execution of raw SQL. Please be aware that executing raw SQL that has been improperly written can irreparably damage you site and/or cause performance issues. Please ensure you are aware of the impact of your SQL before executing it."

Max sample reduced to 20, plugin description updated with suggested warning. Thanks for your help!

Hi Antonello
Thank for this plugin. I have created a multi select profile field in which our users can select multiple courses that they have completed. Individual users can thus login and update their profile with courses they had completed.
I need to create and bulk upload users prepopulated with the courses that they have completed. The bulk upload will however only allow one of the courses from my multi select field. How can I get it to upload users with more than one course that they have completed.

Hi Antonello
Issue: "error reading from database" occurs after deleting a user that made use of the created multi select field. The error occurs when want to add an activity to a course. This functionality is restored when I delete the created multi select profile field.

Hi folks, those issue should be fixed in the new version 1.1.0

Hi Antonello
Thank you for this excellent plugin and the update. I am able to select data from a single table with the query "SELECT id, idnumber, fullname data FROM {course} WHERE category = 2" Can i select data from more that one table e.g the user,course and course_completions tables. If yes, do you have a sample query?

Hi Saaid,
Of course you can. You will need to join the tables and extract the data you need. For example
SELECT cc.id, u.username data FROM mdl_course_completions cc, mdl_user u
where u.id = cc.userid
Please consider that the acutal query will depend on the info you need. So even though the above query is syntactically correct, it might not make sense in your context.

Thank you and much appreciation Antonello for this quick response, i will try and follow your syntax in my context.

thank you for this plugin but there is an issue appear on signup and multiselect appear empty
but when try add new user as admin it added well and i see select list without any errors

Warning: array_search() expects parameter 2 to be array, null given user/profile/field/dynamicmultiselect/field.class.php on line 113

Warning: array_search() expects parameter 2 to be array, null given in /user/profile/field/dynamicmultiselect/field.class.php on line 114

Hello
I can confirm that this plugin (and the simple drop down version) does not work on signup page: the list is not populated.
The "Warning: array_search() expects parameter 2 to be array, null given " is a consequence of the missing data because the plugin is trying to find a default value.

I've found an easy fix (since there is not user id during the signup process):
// Only if we actually need data.
if ($fieldid !== 0 && $userid !== 0) {
became
// Only if we actually need data.
if ($fieldid !== 0) {

Do yo see any issue with this "fix" Antonello ?

Thanks

Hi. You can update it for moodle 3.5. Because this plugin is very good.

Thank you