Questions and discussion about developing processes and programming in PHP, JavaScript, web services & REST API.

Moderator: amosbatto

Forum rules: Please search to see if a question has already asked before creating a new topic. Please don't post the same question in multiple forums.
By 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.
By 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:
     * @url POST /sql
     * @access protected
     * @param string $sql SQL SELECT statement to execute. {@from body}
     * @return array
     * @author Amos Batto <[email protected]>
     * @copyright Public Domain
    public function postSql($sql) {
        try {
            $g = new \G();
           // 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: ... 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.
By amosbatto
higgledy wrote: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.)
Send for clarification

Cost: Will you be using PM entrerprise or open sou[…]

SMS Gateway

Hello natesh, I have a Get ready to use integrate[…]

delete information after CSV export

thanks for your attention, i have some change on y[…]

Thanx again amosbatto... I'll re-check file permis[…]