User profile fields: Dynamic Multi Select

profilefield_dynamicmultiselect
Maintained by Picture of Antonello MoroAntonello Moro
Dynamic Multi Select user profile field for Moodle. Now users can create user multi select fields whose values are retrieved from the moodle DB.
31 sites
12 downloads
9 fans
Current versions available: 2
Dynamic multi-select user profile field for Moodle. Now users can create user multi-select fields whose values are retrieved from the moodle DB. Basically, the user can set an SQL query as value definition of the field. Please note that the query must return two fields: id and data. Please note that this is an advanced plugin, mainly intended for developers and very advanced moodle users. You must be confident with Moodle DB and SQL language to use this plugin properly.  In fact, this plugin allows execution of raw SQL. Please be aware that executing raw SQL that has been improperly written can irreparably damage your site and/or cause performance issues. Please ensure you are aware of the impact of your SQL before executing it.

A possible use case is when one needs to link a user profile field to values that change in time because they are stored in a Moodle table and are updated by users and/or by external services.

Screenshots

Screenshot #0
Screenshot #1

Contributors

Picture of Antonello Moro
Antonello Moro (Lead maintainer)
Please login to view contributors details and/or to contact them

Comments RSS

Show comments
  • Picture of Antonello Moro
    Thu, Jun 9, 2016, 6:02 AM
    added new version with moodle phpdocs errors fixed
  • Picture of Mike Churchward
    Thu, Jun 9, 2016, 10:12 PM
    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.
  • Picture of Antonello Moro
    Fri, Jun 10, 2016, 7:25 AM
    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.
  • Picture of David Mudrák
    Fri, Jun 10, 2016, 1:54 PM

    Thanks Antonello for sharing the plugin, and thanks Mike for detailed peer-review. I like the idea of the need to have a capability explicitly granted. Alternatively, the feature could be made available for site admins only. As long as users are aware of the risks, I think this is good to go. You are cleared to land, welcome to the Plugins directory!

  • Picture of Antonello Moro
    Fri, Jun 10, 2016, 5:07 PM
    Thanks guys
  • Picture of Mike Churchward
    Fri, Jun 10, 2016, 9:06 PM
    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."
  • Picture of Antonello Moro
    Fri, Jun 10, 2016, 9:24 PM
    Max sample reduced to 20, plugin description updated with suggested warning. Thanks for your help!
  • Picture of Saaid Agherdien
    Thu, Dec 29, 2016, 1:00 AM
    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.
  • Picture of Saaid Agherdien
    Sat, Dec 31, 2016, 7:01 PM
    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.
  • Picture of Antonello Moro
    Sun, Feb 12, 2017, 11:25 PM
    Hi folks, those issue should be fixed in the new version 1.1.0
  • Picture of Saaid Agherdien
    Wed, Feb 22, 2017, 5:12 PM
    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?
  • Picture of Antonello Moro
    Wed, Feb 22, 2017, 5:24 PM
    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.
  • Picture of Saaid Agherdien
    Wed, Feb 22, 2017, 5:42 PM
    Thank you and much appreciation Antonello for this quick response, i will try and follow your syntax in my context.
  • Picture of sara rabie
    Tue, Apr 11, 2017, 6:50 PM
    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
  • Picture of Gaël Chardon
    Fri, Apr 28, 2017, 9:33 PM
    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
1 2
Please login to post comments