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?

Have you checked whether mysqli returns any error messages? — Nico Haase
– Nico Haase, Commented Jan 25, 2019 at 15:08
What’s the UPDATE mysql.user … statement supposed to be good for? The one above that, CREATE USER … IDENTIFIED BY 'password' should have already set that password for the user when it was created …? — 04FS
– 04FS, Commented Jan 25, 2019 at 15:10
Just a heads up - transactions and DDL statements don't really work together. — Nigel Ren
– Nigel Ren, Commented Jan 25, 2019 at 15:11
Yes, I'm checking for errors, and the UPDATE mysql.user line was added to fix or further check for errors. — kdaykin
– kdaykin, Commented Jan 25, 2019 at 15:12
And what if your password has a single quote in it? Please for the love of security escape your input with mysqli_real_escape_string. Also note that you can perform the grant in a single query: GRANT ALL PRIVILEGES ON %s.* TO '%s'@'localhost' IDENTIFIED BY '%s' will create the user, grant access and set the password all in one go. — Geoffrey
– Geoffrey, Commented Jan 28, 2019 at 10:36

foxfabi · Accepted Answer · 2019-01-25 16:31:11Z

2

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.

answered Jan 25, 2019 at 16:31

foxfabi

1395 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kdaykin Over a year ago

Thanks for the reply, removing the transaction from the above code didn't fix the issue.

abestrad · Accepted Answer · 2019-01-28 10:29:22Z

0

In addition, it's true that most databases have restrictions. But for debugging purposes and learning, you might to enable general log in mysql (which will log ALL queries).

It will create big overhead and doesn't fit production requirements, but its fast and easy way to fix bugs in development environment.

Using @foxfabi DB example's name, got the following output from your queries:

2019-01-28T10:08:44.791816Z    55 Connect   root@localhost on  using Socket
2019-01-28T10:08:44.792076Z    55 Query CREATE DATABASE IF NOT EXISTS StackOverflow
2019-01-28T10:08:44.792556Z    55 Query GRANT ALL PRIVILEGES ON StackOverflow.* TO 'root'@'1.1.1.1' WITH GRANT OPTION
2019-01-28T10:08:44.792839Z    55 Quit

answered Jan 28, 2019 at 10:29

abestrad

9162 gold badges12 silver badges25 bronze badges

Collectives™ on Stack Overflow

PHP inserting incorrect password

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related