如何使用php pdo创建mysql用户

Question

I wnat to create mysql user accounts from a php code. my code is working but can't login to user accounts which create using that code. this is my php code

//include database connection function
include("dbcon.inc.php");

//prepare db connection
dbcon(1,"root");

//call db_user function to create new db user account
db_user(1,1,"insert","user123");

function db_user($user_admin,$user_active,$user_action,$user_name){
    global $conn;
    $sql_query="";
    $host_name="localhost";
    $staff_password="123";
    $database_name="smart_lib";

    if($user_action=="insert"){
        //create user account
        $sql_query="CREATE USER '$user_name'@'$host_name' IDENTIFIED WITH mysql_native_password AS '$staff_password';";
    }
    elseif($user_action=="update"){
        //update
        $sql_query="REVOKE ALL PRIVILEGES ON *.* FROM '$user_name'@'$host_name';";
    }

    //if user is active give privilages
    if($user_active==1){
        //if user is an admin give admin privilages
        if($user_admin==1){
            $sql_query.="GRANT ALL PRIVILEGES ON *.* TO '$user_name'@'$host_name' REQUIRE NONE WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;";
        }
        //if user not an admin give privilages without grant optin
        else{
            $sql_query.="GRANT SELECT, INSERT, UPDATE, DELETE ON `$database_name`.* TO '$user_name'@'$host_name'; FLUSH PRIVILEGES;";
        }
    }
    $sql = $conn->prepare($sql_query);
    $sql->execute();

    echo $sql_query."<br/>";
}

I get the query using echo $sql_query and execute it on php myadmin. it worked correctly.

This is my database connection.

function dbcon($user_type,$user_name){
    $Database = "smart_lib";
    $Hostname = "localhost";
    $Username = $user_name;
    $Password = "123";

    try{
        global $conn;
        $conn = new PDO("mysql:host=$Hostname;dbname=$Database", $Username,$Password);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $conn->exec ("SET NAMES utf8");
    }
    catch(PDOException $exp)
    {
        echo "Error! ".$exp->getMessage();
    }
}

Answer 1

These are correct sql statements - tested:

// Build another variable: 
$user = $user_name . '@' . $host_name;

// Sql statements:

$sql_query = "CREATE USER `" . $user . "` IDENTIFIED WITH mysql_native_password AS '" . $staff_password . "';";

$sql_query = "REVOKE ALL PRIVILEGES ON *.* FROM `" . $user . "`;";

$sql_query .= "GRANT ALL PRIVILEGES ON *.* TO `" . $user . "` REQUIRE NONE WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;";

$sql_query .= "GRANT SELECT, INSERT, UPDATE, DELETE ON `" . $database_name . "`.* TO `" . $user . "`; FLUSH PRIVILEGES;";

Notes:

The problem resided - at least in my tests - in this representation of the user name: '$user_name'@'$host_name'. My personal tests showed this:

• If you use 'abc'@'def', then the user abc and the host def is created.
• But if you use abc@def with backticks around, or 'abc@def', then the user abc@def is created, with the host %.

In general, use backticks when you pass special characters like "@".

I also recommend you to use exception handling on prepare() and execute() as well.

When you're using prepared statements then you should bind the input parameters, not pass them directly to the sql statements. For this task use the bindValue() or the bindParam() function.

And don't use global connection variable, but create a connection on the page which requires it and pass it as argument to each function.

Maybe this will be helpful:

Complete example of PDO w. prepared statements and exception handling (see especially "generalFunctions.php").

On development (not on production!) use error reporting and display:

error_reporting(E_ALL);
ini_set('display_errors', 1);

Good luck!