Creating a Page Activity/Resource directly in Database

Creating a Page Activity/Resource directly in Database

by Andrew Normore -
Number of replies: 8

Hi friends, I must create a page for a course with an API.

I must write directly to the database, or create a page some how.

I have the following:

INSERT mdl_page

INSERT mdl_course_module

INSERT mdl_course_sections

From what I see, this is all Moodle does when it creates a page through the create activity dialog. I have simply replicated the DB side of things. 

The error I get is 

Invalid course module ID

More information about this error

Debug info: 
Error code: invalidcoursemodule
Stack trace:
  • line 229 of \lib\modinfolib.php: moodle_exception thrown
  • line 2843 of \lib\moodlelib.php: call to course_modinfo->get_cm()
  • line 3229 of \lib\moodlelib.php: call to require_login()
  • line 50 of \mod\page\view.php: call to require_course_login()

Average of ratings: -
In reply to Andrew Normore

Re: Creating a Page Activity/Resource directly in Database

by Andrew Normore -

// 1 - INSERT mdl_page

$sql = "INSERT INTO mdl_page 

(`course`,

`name`, 

`intro`, 

`introformat`, 

`content`, 

`contentformat`, 

`legacyfiles`, 

`display`,

`displayoptions`,

`revision`,

`timemodified`

) 

VALUES 

(

'".addslashes ($_GET['TargetCourseID'])."',

'".addslashes ($_GET['PageName'])."', 

'".addslashes ($_GET['PageContentHTML'])."', 

1, 

'".addslashes ($_GET['PageContentHTML'])."', 

1, 

0, 

5,

'',

0,

'".time()."' )";

// a\:1\:{s\:10\:\"printintro\"\;s\:1\:\"1\"\;}

// Excute does NOT return an ID

$DB->execute($sql);

// Recover the Page ID, Execute doesnt return a value. Thanks Moodle.

$page_id = 0;

$pages = $DB->get_records_sql("SELECT id FROM mdl_page WHERE course = ".$_GET['TargetCourseID']." ORDER BY id DESC LIMIT 1");

foreach($pages as $page){

$page_id = $page->id;

}

// var_dump($page_id);

// 2 - INSERT mdl_course_module

// Before we can insert a module, we need to locate the section ID from mdl_course_sections based on user input

$section_id = 0;

$sections = $DB->get_records_sql("SELECT id, section FROM mdl_course_sections WHERE course=".$_GET['TargetCourseID']." AND section=".$_GET['LoadIntoSection']." ORDER BY id DESC LIMIT 1");

foreach($sections as $section){

$section_id = $section->id;

}

$sql = "INSERT INTO mdl_course_modules 

(`course`,

`module`, 

`instance`, 

`section`, 

`added`, 

`visible`, 

`visibleold`,

`showdescription`

) 

VALUES 

(

'".addslashes ($_GET['TargetCourseID'])."',

24, 

'".$page_id."', 

".$section_id.", 

'".time()."', 

'".addslashes ($_GET['PageIsVisible'])."',

'".addslashes ($_GET['PageIsVisible'])."',

'".addslashes ($_GET['PageIsVisible'])."')";

// a\:1\:{s\:10\:\"printintro\"\;s\:1\:\"1\"\;}

// Excute does NOT return an ID

$success = $DB->execute($sql);

// lastly we must give a Moodle Context to the new item

$module_id = 0;

$modules = $DB->get_records_sql("SELECT * FROM mdl_course_modules WHERE course=".addslashes($_GET['TargetCourseID'])." AND instance=".$page_id." ORDER BY id DESC LIMIT 1");

foreach($modules as $module){

$module_id = $module->id;

}

Average of ratings: -
In reply to Andrew Normore

Re: Creating a Page Activity/Resource directly in Database

by Paul Nicholls -

Firstly, if you're inserting records into the database directly, you should be using $DB->insert() rather than building and executing an SQL statement directly:
https://docs.moodle.org/dev/Data_manipulation_API#Inserting_Records

Not only will it return the ID of the inserted record, but it internally uses query parameters to handle the data safely.  If you're writing other queries, which also need to include user-specified or dynamic values, always use query parameters (either named or positional - see https://docs.moodle.org/dev/Data_manipulation_API#Main_info) in order to protect against SQL injection attacks.  Your code above currently performs no sanitisation whatsoever on the GET parameter you're using in the $DB->get_records_sql calls - so SQL can readily be injected via the TargetCourseID and LoadIntoSection GET parameters, without even having to work around the use of addslashes() (which also fails to completely protect against injection).


That said, directly inserting into the database is probably not the best approach.  If you're able to provide us with a bit more context about what you're trying to achieve and why, I'm sure somebody will be able to help you figure out a better approach.

Average of ratings: Useful (1)
In reply to Paul Nicholls

Re: Creating a Page Activity/Resource directly in Database

by Andrew Normore -

I understand the issues concerning $DB->execute. However this is of no concern what so ever. 

My first objective is to achieve proof of concept. The server is secured via IP firewall, and the API data is checked at the first end. This is simply the receiver.

I am trying to create a page, within a course, automatically. That is, with out having to to use /modedit.php to create a new page.


Average of ratings: -
In reply to Andrew Normore

Re: Creating a Page Activity/Resource directly in Database

by James McLean -

Even though it's a proof of concept - what I see is just... scary. Moodle Database API's provide the ability to very easily use parameterised SQL queries - please please please (for you own sake!) use them! Never ever trust data from any other system (even if you assume it IS trusted) - especially when it's so trivial to make it safe.

However, I believe a better approach would be to use the Webservice API. The Page module doesn't appear to support remote creation at this point; however as you're already coding I would suggest that adding this ability to the Page module would be a much nicer and safer way to achieve what it appears you're trying to do. Other modules such as Assign do already have API calls for external module creation so you could use these for pointers.


Average of ratings: Useful (1)
In reply to James McLean

Re: Creating a Page Activity/Resource directly in Database

by Andrew Normore -

I'm not asking for a better approach, or better security. I have a specific requirement and I'm trying to get it done ASAP. Best practices aside. When my organization deems that this prototype is a functional requirement, I'll clean it up. Don't worry about it, and please stop commenting on it. I know. Believe me, I know.

Thanks for the suggestion to the Assign module, I'll have a look and see if I can solve the missing piece and throw an update here.

Cheers,

Average of ratings: -
In reply to Andrew Normore

Re: Creating a Page Activity/Resource directly in Database

by James McLean -

If you absolutely must do as you're saying, I'd call add_course_module() in course/lib.php followed by page_add_instance() in mod/page/lib.php - provide it with the object of data you want to create (look inside the function page_add_instance() to see the it requires), and it will handle the insert record and course module record creation. This is the function that modedit.php will call to actually create the module correctly.

Calling this function will be orders of magnitude safer than performing the database calls manually, and it will do everything as Moodle expects.

That's the best approach to doing it quick and dirty - it also side-steps many of the issues mentioned above.

Average of ratings: Useful (1)
In reply to James McLean

Re: Creating a Page Activity/Resource directly in Database

by Andrew Normore -
I found the problem! 


object(course_modinfo) does not have the new module assigned to it! How do I fix this? I have set the database items, but for what ever reason it's not inserting the data to this object, which I guess the course is built out of. 

I'm missing ONE step here, any ideas?

Average of ratings: -
In reply to James McLean

Re: Creating a Page Activity/Resource directly in Database

by Andrew Normore -

GOT IT!

I was missing Inserting values from mdl_course_modules into mdl_course_sections  as a sequence!

Once this was done, I was able to call rebuild_course_cache($courseid,true); to rebuild the course. This now correctly grabs the new module in sequence!

I dug through the code to this function get_array_of_activities() in /course/lib.php

I see here it grabbed a "RAW" list of modules, and my inserted data was present. Great, but why not working?

Because the build loop is using foreach ($sequence as $seq) {

So, if you didn't add your custom module to a sequence, it simply gets missed! Moodle is looking for that sequence number.

Fantastic. Thanks every one for help on this issue. It would be easy to take this information and create an API that pushes remote content in to moodle page activities. ROCK ON!

Average of ratings: -