Creating a Database, User and Assignment of User

Jinkers

Registered
Apr 25, 2016
4
0
1
Australia
cPanel Access Level
Reseller Owner
Hi all,

I apologise if the answer is insanely simple, I've been doing things wrong, or if there is already an answer to this problem (that solves mine. I've seen others but none have yielded a working solution).

As the title suggests, I need to be able to programmatically create a database, a new user for this database and give them all permissions to it. I've tried just about any and all variations of code, API calls, API versions etc that I have been able to find over the last week, but I still have not achieved what I need. If someone could please point me in the right direction - that would be fantastic! Or if you know of a working script that would be even better, but I'll take what I can get.

Also, and I should have probably asked this first, does the API calls etc work on shared web hosting? I've never used a VPS before, and really would prefer to stay on shared hosting/reseller if possible.

Sorry if I missed any vital information. Again, any help would be great!

Kind regards,

Jinkers
 

DavidN.

Active Member
Mar 19, 2013
42
3
83
cPanel Access Level
DataCenter Provider
Hi Jinkers,

What you're asking for is totally possible, including in a shared environment with no root or reseller access. When you make your API call, you will just use basic HTTP authentication with your cPanel password. This section of our documentation explains how that works, with code examples in Perl and PHP:

Guide to API Authentication - Software Development Kit - cPanel Documentation

I know the API situation can be confusing, given the frequent overlap of functions. For a new project, you should use UAPI for tasks that a cPanel user would perform. For what you're describing, I think you'll just need these three calls:

https://documentation.cpanel.net/display/SDK/UAPI+Functions+-+Mysql::create_database
UAPI Functions - Mysql::create_user - Software Development Kit - cPanel Documentation
UAPI Functions - Mysql::set_privileges_on_database - Software Development Kit - cPanel Documentation

If you have any more questions, please feel free to ask. We're happy to help.

David Nielson
cPanel Developer
 

Jinkers

Registered
Apr 25, 2016
4
0
1
Australia
cPanel Access Level
Reseller Owner
Hi David.

Cheers for the reply! I remember getting the first HTTP Authentication working, but my problem became actually calling the functions using the UAPI (or any for that matter). I keep getting the error "PHP Fatal error: Class 'CPANEL' not found in /home/xxxx/public_html/index.php on line xx". I've tried changing the path back to the root directory, but still nothing is found (I don't see these in my File Manager either).

Again, thankyou!
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,904
2,218
463
I keep getting the error "PHP Fatal error: Class 'CPANEL' not found in /home/xxxx/public_html/index.php on line xx". I've tried changing the path back to the root directory, but still nothing is found (I don't see these in my File Manager either).
Hello :)

Ensure you have uploaded the xmlapi-php/xmlapi.php at master · CpanelInc/xmlapi-php · GitHub file to your public_html directory via FTP, SSH, or File Manager if that's where the script is utilized. Also, try using an example script for the PHP client class at:

xmlapi-php/Examples at master · CpanelInc/xmlapi-php · GitHub

Make sure you can get an example to work before proceeding with your custom script.

Thank you.
 

jleckie

Member
Feb 4, 2016
7
0
1
Midwest
cPanel Access Level
Root Administrator
Hi Jinkers,

I just updated a question I asked two months ago with my working solution that includes this functionality. The code is written in Perl and not PHP, but it should demonstrate to you the logic that you can follow and apply to your own code.

The response is awaiting moderator approval, once that has happened you will find it here:

Create database(LiveAPI) inside Perl Module Standardized Hook

Lines 158, 161 and 164 are the function calls that you will want to look into. An example API request to make a new MySQL database:

Code:
my $db_request = HTTP::Request->new(GET => "https://127.0.0.1:2087/json-api/cpanel?cpanel_jsonapi_user=$cpanel_username&cpanel_jsonapi_module=Mysql&cpanel_jsonapi_func=create_database&cpanel_jsonapi_apiversion=3&name=$db_name");
 

Jinkers

Registered
Apr 25, 2016
4
0
1
Australia
cPanel Access Level
Reseller Owner
Thanks for the response guys. However, neither have helped! I've added the file to the public_html folder where I'm executing my script, referenced it and I get the following error still!

"PHP Fatal error: Class 'CPANEL' not found in /home/xxxxx/public_html/index.php on line xx"

Any other suggestions?

Thankyou!
 

Jinkers

Registered
Apr 25, 2016
4
0
1
Australia
cPanel Access Level
Reseller Owner
Success! Thankyou all for your help. I saw the major floor in what I was doing and had myself seriously confused. I now have it all working perfectly. Thank you all again!
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,904
2,218
463
New Success! Thankyou all for your help. I saw the major floor in what I was doing and had myself seriously confused. I now have it all working perfectly. Thank you all again!
I'm happy to see you were able to address the issue. Would you mind sharing the solution in-case another person makes the same mistake and finds this thread?

Thank you.
 

Ariee

Registered
Apr 30, 2016
3
0
1
Indonesia
cPanel Access Level
Root Administrator
Hi, I am using php to request the same function like the example from jleckie.
this is the code:
$action = "/json-api/cpanel?" .
"cpanel_jsonapi_user=" . $user .
"&cpanel_jsonapi_module=" . "Mysql" .
"&cpanel_jsonapi_func=" . "create_database" .
"&cpanel_jsonapi_apiversion=" . "3" .
"&name=" . $userdb;

But I got an error message like this:

{"apiversion":3,"func":"create_database","result":{"messages":null,"errors":["(XID vbt8ed) (XID 38uyy9) This value may not be empty."],"status":0,"metadata":{},"data":null},"module":"Mysql"}

Anyone know what is wrong with my code?

Best Regards,

Ariee
 
Last edited by a moderator:

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,904
2,218
463
Hello,

Could you let us know the full script you are using in CODE tags, ensuring to use fake authentication details?

Thank you.
 

Ariee

Registered
Apr 30, 2016
3
0
1
Indonesia
cPanel Access Level
Root Administrator
Hi,

This is my complete code. I got the same result when I try using uapi command from command line as root user.
Code:
uapi  --user=cpuser Mysql create_database name=cpuser_db
Code:
<?php

function remote($url, $xml = 0, $term = false, $returnErrors = true, $post = null) {
    $whmusername = "rootuser";
    $whmpassword = "rootpass";
    $data['host'] = "servername";
    $ch = curl_init();
    $fullUrl = "https://" . $data['host'] . ":2087" . $url;
    curl_setopt($ch, CURLOPT_HTTPAUTH, CURLAUTH_ANY);
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
    if ($post != null) {
        curl_setopt($ch, CURLOPT_POST, true);
        curl_setopt($ch, CURLOPT_POSTFIELDS, $post);
    }
    curl_setopt($ch, CURLOPT_URL, $fullUrl);
    curl_setopt($ch, CURLOPT_HEADER, false);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    $header[0] = "Authorization: Basic " . base64_encode($whmusername.":".$whmpassword) . "\n\r";
    curl_setopt($ch, CURLOPT_HTTPHEADER, $header);    // set the username and password
    $data = curl_exec($ch);
    if ($data === false) {
        if ($returnErrors) {
            return curl_error($ch);
        }
        global $main;
        $main->error(array("WHM Connection Error" => curl_error($ch)));
        return false;
    }
    curl_close($ch);
    if (stripos($data, "Content-type: text/html") !== false) {
        if ($returnErrors) {
            return "WHM returned HTML. Is it unlicensed?";
        }
        global $main;
        $main->error(array("WHM Error" => "WHM returned HTML. Is it unlicensed?"));
        return false;
    }
//END
    if ($term == true) {
        return true;
    } elseif (strstr($data, "SSL encryption is required")) {
        if ($returnErrors) {
            return "THT must connect via SSL!";
        }
        global $main;
        $main->error(array("WHM Error" => "THT must connect via SSL!"));
        return false;
    } elseif (!$xml) {
//        $xml = new SimpleXMLElement($data);
        return $data;
    } else {
        return $data;
    }
    return $xml;
}

function createdb($user, $server, $reason = false) {
    $action = "/json-api/cpanel?" .
//        "api.version=" . "1" . "" .
        "cpanel_jsonapi_user=" . $user .
        "&cpanel_jsonapi_module=" . "Mysql" .
        "&cpanel_jsonapi_func=" . "create_database" .
        "&cpanel_jsonapi_apiversion=" . "3" .
        "&name=" . $user . "_test";
    $command = remote($action);
    print_r($command);
}

createdb("cpuser", "");
?>
 

Ariee

Registered
Apr 30, 2016
3
0
1
Indonesia
cPanel Access Level
Root Administrator
Hello,

Sorry, my bad.
The error came out because I use suspended account.
The error message was not clear so I can not figure it out what was wrong. I hope cpanel developer can add a better error information.


Best Regards,

Ariee

Hi,

This is my complete code. I got the same result when I try using uapi command from command line as root user.
Code:
uapi  --user=cpuser Mysql create_database name=cpuser_db
Code:
<?php

function remote($url, $xml = 0, $term = false, $returnErrors = true, $post = null) {
    $whmusername = "rootuser";
    $whmpassword = "rootpass";
    $data['host'] = "servername";
    $ch = curl_init();
    $fullUrl = "https://" . $data['host'] . ":2087" . $url;
    curl_setopt($ch, CURLOPT_HTTPAUTH, CURLAUTH_ANY);
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
    if ($post != null) {
        curl_setopt($ch, CURLOPT_POST, true);
        curl_setopt($ch, CURLOPT_POSTFIELDS, $post);
    }
    curl_setopt($ch, CURLOPT_URL, $fullUrl);
    curl_setopt($ch, CURLOPT_HEADER, false);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    $header[0] = "Authorization: Basic " . base64_encode($whmusername.":".$whmpassword) . "\n\r";
    curl_setopt($ch, CURLOPT_HTTPHEADER, $header);    // set the username and password
    $data = curl_exec($ch);
    if ($data === false) {
        if ($returnErrors) {
            return curl_error($ch);
        }
        global $main;
        $main->error(array("WHM Connection Error" => curl_error($ch)));
        return false;
    }
    curl_close($ch);
    if (stripos($data, "Content-type: text/html") !== false) {
        if ($returnErrors) {
            return "WHM returned HTML. Is it unlicensed?";
        }
        global $main;
        $main->error(array("WHM Error" => "WHM returned HTML. Is it unlicensed?"));
        return false;
    }
//END
    if ($term == true) {
        return true;
    } elseif (strstr($data, "SSL encryption is required")) {
        if ($returnErrors) {
            return "THT must connect via SSL!";
        }
        global $main;
        $main->error(array("WHM Error" => "THT must connect via SSL!"));
        return false;
    } elseif (!$xml) {
//        $xml = new SimpleXMLElement($data);
        return $data;
    } else {
        return $data;
    }
    return $xml;
}

function createdb($user, $server, $reason = false) {
    $action = "/json-api/cpanel?" .
//        "api.version=" . "1" . "" .
        "cpanel_jsonapi_user=" . $user .
        "&cpanel_jsonapi_module=" . "Mysql" .
        "&cpanel_jsonapi_func=" . "create_database" .
        "&cpanel_jsonapi_apiversion=" . "3" .
        "&name=" . $user . "_test";
    $command = remote($action);
    print_r($command);
}

createdb("cpuser", "");
?>
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,904
2,218
463
The error came out because I use suspended account.
The error message was not clear so I can not figure it out what was wrong. I hope cpanel developer can add a better error information.
I'm happy to see you were able to determine the cause of the problem. I've opened CPANEL-6362 to address the issue with the unhelpful error reporting for suspended accounts when using this UAPI function. I'll update this thread with more information on the case as it becomes available.

Thank you.
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,904
2,218
463
Hello,

Good news! cPanel version 60 will include a resolution associated with CPANEL-6362 that ensures a more helpful error message is provided for suspended accounts when using this UAPI function.

Thank you.