PHP inserting incorrect password

Question

I'm trying to create a mysql user account at runtime in PHP7.0, granted access to a single database, also created at runtime. I'm currently using:

$this->mysqli = new mysqli('localhost', $admin_account, $password);

$setup = [
    /* create database */
    sprintf('CREATE DATABASE IF NOT EXISTS %s;', $dbName),
    /* grant admin */
    sprintf("GRANT ALL PRIVILEGES ON %s.* TO '%s'@'%s' WITH GRANT OPTION;", $dbName, $admin_account, $admins_remote_ip),
    /* add user */
    sprintf("CREATE USER '%s'@'localhost' IDENTIFIED BY '%s';", $dbUsername, $dbPassword),
    /* THIS WAS ADDED AS A FIX TO NO AVAIL */
    sprintf("UPDATE mysql.user SET password=PASSWORD('%s') WHERE user='%s'", $dbPassword, $dbUsername),
    /* grant retailer*/
    sprintf("GRANT ALL PRIVILEGES ON %s.* TO '%s'@'localhost';", $dbName, $dbUsername),
    /* flush */
    "FLUSH PRIVILEGES;",
];



foreach ($setup as $query) {
    if (false == $this->mysqli->query($query)) {
        $error = $this->mysqli->error;
        return $error;
    }
}

All of the statements execute without an error, everything appears to be where it should, however the non-admin user gets a mysql->error;

Connect failed: Access denied for user 'dbUsername'@'localhost' (using password: YES)

If I do this;

> mysql -u admin_account -p
update mysql.user set password=PASSWORD('dbPassword') where user='dbUsername';

Then the connection works and all is well. Please help.

*edit: I've removed the transactional behaviour, removing it from the live code didn't fix the issue. It appears that the password string is being altered somewhere between PHP and MySQL?

Answer 1

Mostly, transaction control commands are used with the DML commands such as - INSERT, UPDATE and DELETE. If you use them while creating tables or dropping them, the result may depends on the auto-commit mode. By default, mysql automatically commits the changes permanently to the database.

If you read the article "How Popular Databases Deal with DDL Commands in Transactions", you will notice that not all databases are able to rollback DDL changes. They do support DDL commands transactionality but not for all commands.

Also you will find in the MySQL Internals Manual that DDL statements and operations with nontransactional engines do not "register" in the transaction lists. Check wich engine you are using (InnoDB or MyISAM).

I have reproduced the example purely in sql and found that FLUSH must be the cause. This is confirmed in the manual. The FLUSH statement causes an implicit commit.

START TRANSACTION;
CREATE DATABASE IF NOT EXISTS StackOverflow;
GRANT ALL PRIVILEGES ON  StackOverflow.* TO 'admin'@'%' WITH GRANT OPTION;
CREATE USER 'customer'@'localhost' IDENTIFIED BY 'customer';
GRANT ALL PRIVILEGES ON StackOverflow.* TO 'customer'@'localhost';
COMMIT;
FLUSH PRIVILEGES;

In the table mysql.user the 'customer' has been created without rights. In the table mysql.db the 'customer' and 'admin' have been created with rights.