[higgledy]

Can I use the extraRest plugin to update the db directly using sql? I am getting back an error stating it must be a select statment.

[amosbatto]

In workflow/engine/plugins/extraRest/src/Services/Api/ExtraRest/Extra.php, you need to enable the postSql() function by removing the /* */ around it. Then, comment out the code that only allows SELECT statements like this:

Code: Select all

    /**
     * Execute an SQL SELECT query in the current workspace's workflow database. 
     * By default, the initial workspace is named "wf_workflow". The results
     * are returned in a numbered array starting from 1, just like executeQuery().
     * 
     * Note 1: For security reasons, this endpoint is commented out. If
     * you want to test it, then remove the comments and change the [AT] to @
     * It is strongly recommended to adapt this code to include the specific
     * SQL query that you need and only pass the specific parameters that
     * need to be changed to the endpoint. For security reasons, do not 
     * allow this endpoint to execute any SQL query. Its code is provided
     * to show you how to execute SQL queries in ProcessMaker, but it needs 
     * to be adapted for your specific purpose to make it safer. 
     * 
     * Note 2: Only SELECT statements in the current workspace's workflow
     * database are allowed. If thinking of modifying this endpoint to allow UPDATE, INSERT and DELETE
     * statements, then make sure to change the ProcessMaker configuration files. See:
     * http://wiki.processmaker.com/3.0/Consulting_the_ProcessMaker_databases#Protecting_PM_Core_Tables
     * 
     * 
     * @url POST /sql
     * @access protected
     * 
     * @param string $sql SQL SELECT statement to execute. {@from body}
     *   
     * @return array
     * 
     * @author Amos Batto <amos@processmaker.com>
     * @copyright Public Domain
     */ 
    public function postSql($sql) {
        try {
            $g = new \G();
            $g->loadClass("pmFunctions");
            
           // if (preg_match('/^\s*select\s/i', $sql) == 0) {
           //     throw new \Exception("SQL must be a SELECT statement.");
           // } 

            $aResult = executeQuery($sql);
            
            $aRows = array();
            foreach ($aResult as $aRow) {
                $aRows[] = $aRow;
            }
            return $aRows;
        } 
        catch (\Exception $e) {
            throw new RestException(Api::STAT_APP_EXCEPTION, $e->getMessage());
        }  
    }
    

You also need to change the configuration. See:
http://wiki.processmaker.com/3.0/Consul ... ore_Tables

As explained in the documentation, this endpoint is a big security hole that a hacker can use to execute any SQL command, so it is better to create a custom endpoint that only executes a single SQL statement that you want.

[higgledy]

Is the security threat why the postSql method was commented-out in the extraRest ver 1.6 plugin?

[amosbatto]

Is the security threat why the postSql method was commented-out in the extraRest ver 1.6 plugin?

Yes. Also in the above post, I commented out the code that only allows SELECT statements, so this is doubly dangerous, because a hacker could delete or rewrite all the content in the database. See the code example in the documentation to create a custom endpoint that only executes the SQL statement that you need, which is much safer.
(Of course, this isn't such a big issue if your server is configured to only allow https connections, so it would be hard for a hacker to get the username and password to do a login and he can't watch the traffic to figure out that you are using a REST endpoint that accepts any SQL command to the database.)