Create cpanel database through php script

Question

Im trying to automate the installation of some custom software using cpanel/whm and postwwwacct php script.This requires files to be copied to the users public_html folder then edit the config and set eh file permissions. So far so good, no issues. When trying to create the database im running into some problems.

    $db_create= $opts['user']. '_lol';  
    $db_host="immersion-networks.com";
    include("xmlapi.php");   
    $xmlapi = new xmlapi($db_host);    
    $xmlapi->password_auth("".$opts['user']."","".$opts['pass']."");    
    $xmlapi->set_debug(1);//output actions in the error log 1 for true and 0 false  
    $xmlapi->set_output('array');//set this for browser output  
    //create database    
    $createdb = $xmlapi->api1_query($opts['user'], "Mysql", "adddb", array($db_create));   
    //create user 
    $usr = $xmlapi->api1_query($opts['user'], "Mysql", "adduser", array($db_create, $opts['pass']));   
     //add user 
    $addusr = $xmlapi->api1_query($opts['user'], "Mysql", "adduserdb", array($db_create,$db_create, 'all')); 

Rest of the code runs ok but the db isnt being created nor are the users. Any ideas?

Underthrust

Answer 1

require("xmlapi.php"); // this can be downlaoded from https://github.com/CpanelInc/xmlapi-php/blob/master/xmlapi.php
$xmlapi = new xmlapi("your cpanel domain");   
$xmlapi->set_port( 2083 );   
$xmlapi->password_auth($opts['user'],$opts['pass']);    
$xmlapi->set_debug(0);//output actions in the error log 1 for true and 0 false 

$cpaneluser=$opts['user'];
$databasename="something";
$databaseuser="else";
$databasepass=$opts['pass'];

//create database    
$createdb = $xmlapi->api1_query($cpaneluser, "Mysql", "adddb", array($databasename));   
//create user 
$usr = $xmlapi->api1_query($cpaneluser, "Mysql", "adduser", array($databaseuser, $databasepass));   
//add user 
$addusr = $xmlapi->api1_query($cpaneluser, "Mysql", "adduserdb", array("".$cpaneluser."_".$databasename."", "".$cpaneluser."_".$databaseuser."", 'all'));

The above code worked for me! Need to make sure that you are using a cpanel user/pass not root and also that you are using port 2083

Underthrust

DB user wasn't granted permissions when I used this, maybe because I already included the cPanel username into $databasename and $databaseuser upon setting variables. To solve, replace last line by: $addusr = $xmlapi->api1_query($cpaneluser, 'Mysql', 'adduserdb', array('' . $databasename . '', '' . $databaseuser . '', 'all')); Basically removing $cpaneluser from the query. – Nutwood

This is an extraordinary post!! I ever seen in stackoverflow... Just Curious about how to delete the database in the same way. becoz in my codeigniter application by using dbforge i am not able to delete the databsae... – Ruinous

Answer 2

Without xmlapi

function createDb($cPanelUser,$cPanelPass,$dbName) {

    $buildRequest = "/frontend/x3/sql/addb.html?db=".$dbName;

    $openSocket = fsockopen('localhost',2082);
    if(!$openSocket) {
        return "Socket error";
        exit();
    }

    $authString = $cPanelUser . ":" . $cPanelPass;
    $authPass = base64_encode($authString);
    $buildHeaders  = "GET " . $buildRequest ."\r\n";
    $buildHeaders .= "HTTP/1.0\r\n";
    $buildHeaders .= "Host:localhost\r\n";
    $buildHeaders .= "Authorization: Basic " . $authPass . "\r\n";
    $buildHeaders .= "\r\n";

    fputs($openSocket, $buildHeaders);
    while(!feof($openSocket)) {
        fgets($openSocket,128);
    }
    fclose($openSocket);
}


function createUser($cPanelUser,$cPanelPass,$userName,$userPass) {

    $buildRequest = "/frontend/x3/sql/adduser.html?user=".$userName."&pass=".$userPass;

    $openSocket = fsockopen('localhost',2082);
    if(!$openSocket) {
        return "Socket error";
        exit();
    }

    $authString = $cPanelUser . ":" . $cPanelPass;
    $authPass = base64_encode($authString);
    $buildHeaders  = "GET " . $buildRequest ."\r\n";
    $buildHeaders .= "HTTP/1.0\r\n";
    $buildHeaders .= "Host:localhost\r\n";
    $buildHeaders .= "Authorization: Basic " . $authPass . "\r\n";
    $buildHeaders .= "\r\n";

    fputs($openSocket, $buildHeaders);
    while(!feof($openSocket)) {
        fgets($openSocket,128);
    }
    fclose($openSocket);
}

function addUserToDb($cPanelUser,$cPanelPass,$userName,$dbName,$privileges) {

    $buildRequest = "/frontend/x3/sql/addusertodb.html?user=".$userName."&db=".$dbName.$privileges;

    $openSocket = fsockopen('localhost',2082);
    if(!$openSocket) {
        return "Socket error";
        exit();
    }

    $authString = $cPanelUser . ":" . $cPanelPass;
    $authPass = base64_encode($authString);
    $buildHeaders  = "GET " . $buildRequest ."\r\n";
    $buildHeaders .= "HTTP/1.0\r\n";
    $buildHeaders .= "Host:localhost\r\n";
    $buildHeaders .= "Authorization: Basic " . $authPass . "\r\n";
    $buildHeaders .= "\r\n";

    fputs($openSocket, $buildHeaders);
    while(!feof($openSocket)) {
        fgets($openSocket,128);
    }
    fclose($openSocket);
}

//Create Db
createDb('CpanelUser','cPanelPass','dbName');

//Create User
createUser('cPanelUser','cPanelPass','dbUsername','dbUserPass');

//Add user to DB - ALL Privileges
addUserToDb('cPanelUsername','cPanelPass','dbUsername','dbName','&ALL=ALL');

//Add user to DB - SELECTED PRIVILEGES
addUserToDb('cPanelUsername','cPanelPass','dbUsername','dbName','&CREATE=CREATE&ALTER=ALTER');

Dogtooth

problem addUsetTODb : add the user, but doesn't add ALL privileges – Ornament

I solved this problem. Method: addUserToDb change the value variable from addusertodb.html?user=".$userName."&db=".$dbName.$privileges to addusertodb.html?user=".$userName."&db=".$dbName."&privileges=".$privileges and call method: addUserToDb('cPanelUsername','cPanelPass','dbUsername','dbName','CREATE,ALTER,DELETE,UPDATE'); – Caliper

If this answer doesn't work for you, try changing the x3 part of the $buildRequest string. (If you log into your cPanel, you can see from your actual cPanel URL in your browser address bar what your alternative to x3 should be.) – Simpleton

For the addUserToDb() function, I had to pass '&privileges=ALL' as the final parameter value. – Simpleton

Also note that the db name you pass to createDb() does not have the prefix, but the db name (and username) you pass to addUserToDb() does need the prefix. – Simpleton

Answer 3

I have done modification based on @kenvilar by putting prefix definition at fn: addUserToDb()

<?php

$database_name = "dbname"; //without prefix
$database_user = $database_name; //database name and database username are both similar, change the value if you want
$database_pass = "random_password";
$cpanel_username = "my_cpanel_username";
$cpanel_pass = "my_cpanel_password";
$cpanel_theme = "paper_lantern"; // change this to "x3" if you don't have paper_lantern yet

function createDb($cpanel_theme, $cPanelUser, $cPanelPass, $dbName)
{
    $buildRequest = "/frontend/" . $cpanel_theme . "/sql/addb.html?db=" . $dbName;

    $openSocket = fsockopen('localhost', 2082);
    if (!$openSocket) {
        return "Socket error";
        exit();
    }

    $authString = $cPanelUser . ":" . $cPanelPass;
    $authPass = base64_encode($authString);
    $buildHeaders = "GET " . $buildRequest . "\r\n";
    $buildHeaders .= "HTTP/1.0\r\n";
    $buildHeaders .= "Host:localhost\r\n";
    $buildHeaders .= "Authorization: Basic " . $authPass . "\r\n";
    $buildHeaders .= "\r\n";

    fputs($openSocket, $buildHeaders);
    while (!feof($openSocket)) {
        fgets($openSocket, 128);
    }
    fclose($openSocket);
}

function createUser($cpanel_theme, $cPanelUser, $cPanelPass, $userName, $userPass)
{
    $buildRequest = "/frontend/" . $cpanel_theme . "/sql/adduser.html?user=" . $userName . "&pass=" . $userPass;

    $openSocket = fsockopen('localhost', 2082);
    if (!$openSocket) {
        return "Socket error";
        exit();
    }

    $authString = $cPanelUser . ":" . $cPanelPass;
    $authPass = base64_encode($authString);
    $buildHeaders = "GET " . $buildRequest . "\r\n";
    $buildHeaders .= "HTTP/1.0\r\n";
    $buildHeaders .= "Host:localhost\r\n";
    $buildHeaders .= "Authorization: Basic " . $authPass . "\r\n";
    $buildHeaders .= "\r\n";

    fputs($openSocket, $buildHeaders);
    while (!feof($openSocket)) {
        fgets($openSocket, 128);
    }
    fclose($openSocket);
}

function addUserToDb($cpanel_theme, $cPanelUser, $cPanelPass, $userName, $dbName, $privileges){
    
    /* Redefine prefix for user and dbname */
    $prefix = substr($cPanelUser,0,8);
    
    $buildRequest = "/frontend/" . $cpanel_theme . "/sql/addusertodb.html?user=" . $prefix . "_" . 
            $userName . "&db=" . $prefix . "_" . $dbName . "&privileges=" . $privileges;

    $openSocket = fsockopen('localhost', 2082);
    if (!$openSocket) {
        return "Socket error";
        exit();
    }

    $authString = $cPanelUser . ":" . $cPanelPass;
    $authPass = base64_encode($authString);
    $buildHeaders = "GET " . $buildRequest . "\r\n";
    $buildHeaders .= "HTTP/1.0\r\n";
    $buildHeaders .= "Host:localhost\r\n";
    $buildHeaders .= "Authorization: Basic " . $authPass . "\r\n";
    $buildHeaders .= "\r\n";

    fputs($openSocket, $buildHeaders);
    while (!feof($openSocket)) {
        fgets($openSocket, 128);
    }
    fclose($openSocket);
}

//Create Db
createDb($cpanel_theme, $cpanel_username, $cpanel_pass, $database_name);

//Create User
createUser($cpanel_theme, $cpanel_username, $cpanel_pass, $database_user, $database_pass);

//Add user to DB - ALL Privileges
addUserToDb($cpanel_theme, $cpanel_username, $cpanel_pass, $database_user, $database_name, 'ALL PRIVILEGES');

//Add user to DB - SELECTED PRIVILEGES
//addUserToDb($cpanel_theme, $cpanel_username, $cpanel_pass, $database_user, $database_name, 'DELETE,UPDATE,CREATE,ALTER');

?>

Spitfire

This worked for me. Just a note for future googlers to say that anyone having issues with creating a user, the password needs to also comply with the "password strength" value being 65 or more (like when creating from the ui). Testing it with "password" or "123456" would fail, however creating it with "Password123!£" worked in my tests. Thanks – Mortality

@Mortality you are correct! I copy the example code of Tung here but only db created but no db user. After I read your suggestion, I update the code $database_pass = random_bytes(16); and it worked! The random_bytes() is on PHP 7 and 8 only - php.net/manual/en/function.random-bytes.php – Vinous

Answer 4

This one worked for me. I've made some updates from @brunofitas answer

<?php

$database_name = "dbname"; //without prefix
$database_user = $database_name; //database name and database username are both similar, change the value if you want
$database_pass = "random_password";
$cpanel_username = "my_cpanel_username";
$cpanel_pass = "my_cpanel_password";
$cpanel_theme = "paper_lantern"; // change this to "x3" if you don't have paper_lantern yet

function createDb($cpanel_theme, $cPanelUser, $cPanelPass, $dbName)
{
    $buildRequest = "/frontend/" . $cpanel_theme . "/sql/addb.html?db=" . $dbName;

    $openSocket = fsockopen('localhost', 2082);
    if (!$openSocket) {
        return "Socket error";
        exit();
    }

    $authString = $cPanelUser . ":" . $cPanelPass;
    $authPass = base64_encode($authString);
    $buildHeaders = "GET " . $buildRequest . "\r\n";
    $buildHeaders .= "HTTP/1.0\r\n";
    $buildHeaders .= "Host:localhost\r\n";
    $buildHeaders .= "Authorization: Basic " . $authPass . "\r\n";
    $buildHeaders .= "\r\n";

    fputs($openSocket, $buildHeaders);
    while (!feof($openSocket)) {
        fgets($openSocket, 128);
    }
    fclose($openSocket);
}

function createUser($cpanel_theme, $cPanelUser, $cPanelPass, $userName, $userPass)
{
    $buildRequest = "/frontend/" . $cpanel_theme . "/sql/adduser.html?user=" . $userName . "&pass=" . $userPass;

    $openSocket = fsockopen('localhost', 2082);
    if (!$openSocket) {
        return "Socket error";
        exit();
    }

    $authString = $cPanelUser . ":" . $cPanelPass;
    $authPass = base64_encode($authString);
    $buildHeaders = "GET " . $buildRequest . "\r\n";
    $buildHeaders .= "HTTP/1.0\r\n";
    $buildHeaders .= "Host:localhost\r\n";
    $buildHeaders .= "Authorization: Basic " . $authPass . "\r\n";
    $buildHeaders .= "\r\n";

    fputs($openSocket, $buildHeaders);
    while (!feof($openSocket)) {
        fgets($openSocket, 128);
    }
    fclose($openSocket);
}

function addUserToDb($cpanel_theme, $cPanelUser, $cPanelPass, $userName, $dbName, $privileges)
{
    $buildRequest = "/frontend/" . $cpanel_theme . "/sql/addusertodb.html?user=" . $cPanelUser . "_" . $userName . "&db=" . $cPanelUser . "_" . $dbName . "&privileges=" . $privileges;

    $openSocket = fsockopen('localhost', 2082);
    if (!$openSocket) {
        return "Socket error";
        exit();
    }

    $authString = $cPanelUser . ":" . $cPanelPass;
    $authPass = base64_encode($authString);
    $buildHeaders = "GET " . $buildRequest . "\r\n";
    $buildHeaders .= "HTTP/1.0\r\n";
    $buildHeaders .= "Host:localhost\r\n";
    $buildHeaders .= "Authorization: Basic " . $authPass . "\r\n";
    $buildHeaders .= "\r\n";

    fputs($openSocket, $buildHeaders);
    while (!feof($openSocket)) {
        fgets($openSocket, 128);
    }
    fclose($openSocket);
}

//Create Db
createDb($cpanel_theme, $cpanel_username, $cpanel_pass, $database_name);

//Create User
createUser($cpanel_theme, $cpanel_username, $cpanel_pass, $database_user, $database_pass);

//Add user to DB - ALL Privileges
addUserToDb($cpanel_theme, $cpanel_username, $cpanel_pass, $database_user, $database_name, 'ALL PRIVILEGES');

//Add user to DB - SELECTED PRIVILEGES
//addUserToDb($cpanel_theme, $cpanel_username, $cpanel_pass, $database_user, $database_name, 'DELETE,UPDATE,CREATE,ALTER');

Luanneluanni

I tried the above code but user is not creating and adding user to db is not working – Serapis

Answer 5

We can do this using cpanel API, as the above old codes are not working currently with new versions of cpanel. Although the question was asked earlier but i am pasting this for helping others.

include "cpaneluapi.class.php"; //include the class file from here https://github.com/N1ghteyes/cpanel-UAPI-php-class/blob/master/cpaneluapi.class.php
$uapi = new cpanelAPI('cpanelusername', 'cpanelapssword', 'cpanel.domain.com'); 
//instantiate the object

$database = 'dbname';
$databaseuser = 'dbuser';
$databasepass = 'databasepass';

/**
 * Mysql - Create a database and user, then assign the user to that database.
 * For a full list of functions available for the Mysql module, see: 
    https://documentation.cpanel.net/display/SDK/Mysql
 * Mysql requires cPanel 11.44 +
  */


  $uapi->uapi->Mysql->create_database(array('name' => $database)); //Create the 
  database
  $uapi->uapi->Mysql->create_user(array('name' => $databaseuser, 'password' => 
  $databasepass)); //create a user for the new database
  //After you create the user, you must use the set_privileges_on_database 
  //function call to grant access to the

  //add the user, set all privileges - add specific privileges by comma 
  //separation. e.g. 'DELETE,UPDATE,CREATE,ALTER' 
  $uapi->uapi->Mysql->set_privileges_on_database(array('user' => $databaseuser, 
  'database' => $database, 'privileges' => 'ALL'));

Serapis

Answer 6

cPanel does not allow you to create databases directly from MySQL/PhpMyAdmin on cPanel webhosting. You would have to login to cPanel and use its interface to create database each time you need a new one. cPanel Database Creator will make this process much easier. In order to create a database on your hosting server you just need to run this script from browser, shell, or cron job, passing database name as parameter.

Before using this script you will need to update it with cPanel username, password, and host name (domain or IP).

Usage: cpanel_create_db.php?db=database-name&user=username&pass=password where database-name should be replaced with a database name to be created on your hosting server. Database Creator will also create a database user and assign it to the database with ALL permissions. Script will not create database user if you omit the user parameter. In this case usage would be cpanel_create_db.php?db=database-name

Note: if script does not work try running it via cURL. This requires cURL installation on the server. Default cURL path is set to /usr/bin/curl. Feel free to change it in the script code if cURL path is different on your hosting server.

try this

<?php

###############################################################
# cPanel Database Creator 1.2
###############################################################
# Visit http://www.zubrag.com/scripts/ for updates
############################################################### 

// cPanel username (you use to login to cPanel)
$cpanel_user = "user";

// cPanel password (you use to login to cPanel)
$cpanel_password = "password";

// cPanel domain (example: mysite.com)
$cpanel_host = "host";

// cPanel theme/skin, usually "x"
// Check http://www.zubrag.com/articles/determine-cpanel-skin.php
// to know it for sure
$cpanel_skin = "x";

// Script will add user to database if these values are not empty
// User wil have ALL permissions
$db_username = '';
$db_userpass = '';

// Update this only if you are experienced user or if script does not work
// Path to cURL on your server. Usually /usr/bin/curl
$curl_path = "";

//////////////////////////////////////
/* Code below should not be changed */
//////////////////////////////////////

function execCommand($command) {
  global $curl_path;

  if (!empty($curl_path)) {
    return exec("$curl_path '$command'");
  }
  else {
    return file_get_contents($command);
  }
}

if(isset($_GET['db']) && !empty($_GET['db'])) {
  // escape db name
  $db_name = escapeshellarg($_GET['db']);

  // will return empty string on success, error message on error
  $result = execCommand("http://$cpanel_user:$cpanel_password@$cpanel_host:2082/frontend/$cpanel_skin/sql/adddb.html?db=$db_name");

  if(isset($_GET['user']) && !empty($_GET['user'])) {
    $db_username = $_GET['user'];
    $db_userpass = $_GET['pass'];
  }

  if (!empty($db_username)) {
    // create user
    $result .= execCommand("http://$cpanel_user:$cpanel_password@$cpanel_host:2082/frontend/$cpanel_skin/sql/adduser.html?user={$db_username}&pass={$db_userpass}");
    // assign user to database
    $result .= execCommand("http://$cpanel_user:$cpanel_password@$cpanel_host:2082/frontend/$cpanel_skin/sql/addusertodb.html?user={$cpanel_user}_{$db_username}&db={$cpanel_user}_{$db_name}&ALL=ALL");
  }

  // output result
  echo $result;
}
else {
  echo "Usage: cpanel_create_db.php?db=databasename&user=username&pass=password";
}

?>

Photocopier

Doesnt work for me, im using this in my postwwwacct script. I have even tried manually entering the link replacing my details. Still does not create the db. – Underthrust

see this url #3005966 – Photocopier

http://$cpanel_user:$cpanel_password@$cpanel_host:2082/frontend/$cpanel_skin/sql/adddb.html?db=$db_name takes me to the cpanel login page after i replace my login details,hostname,skin,database name it does however work if i am already logged into cpanel – Underthrust