site-install fails for database user passwords containing special characters

[wdouglascampbell]

Describe the bug
Installation fails when the database user password specified in the --db-url parameter of drush site-install contains either of the following characters: ', \ .

To Reproduce
Use the following:

drush site-install standard \
  --sites-subdir="www.example.com" \
  --db-url="mysql://test_db_user:Aa~%60%21%40%23%24%25%5e%26%2a%28%29_-%2b%3d%7b%5b%7d%5d%7c%5c%3a%3b%22%27%3c%2c%3e.%20%3f%2fAa~%60%21%40%23%24%25%5e%26%2a%28%29_-%2b%3d%7b%5b%7d%5d%7c%5c%3a%3b%22%27%3c%2c%3e.%20%3f%2f@localhost/test_drupal_db" \
  --db-su="root" \
  --db-su-pw="password" \
  --account-name="admin" \
  --account-pass="password" \
  --site-name="Test Site" \
  --site-mail="postmaster@example.com" \
  --yes

Expected behavior
Installation would be successful and database user password would work. Password used is Aa~`!@#$%^&*()_-+={[}]|\:;"'<,>. ?/Aa~`!@#$%^&*()_-+={[}]|\:;"'<,>. ?/

Actual behavior
The following error occurs:

 [warning] Failed to drop or create the database. Do it yourself before installing. ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '<,>. ?/Aa~`!@#$%^&*()_-+={[}]|\:;"'<,>. ?/'; GRANT ALL PRIVILEGES ON test_dru...' at line 1

In this particular case it is getting tripped up on the first single quote. However, I also discovered when I attempted to remove the single quotes that while things would proceed further they would end with the following error:

In install.core.inc line 969:

  Resolve all issues below to continue the installation. For help configuring your database server, see the <a href="https://www.drupal.org/docs/installing-drupal">installation handbook<
  /a>, or contact your hosting provider.<div class="item-list"><ul><li>Failed to connect to your database server. The server reports the following message: <em class="placeholder">SQLSTA
  TE[HY000] [1045] Access denied for user &#039;test_db_user&#039;@&#039;localhost&#039; (using password: YES)</em>.<ul><li>Is the database server running?</li><li>Does the database exis
  t or does the database user have sufficient privileges to create the database?</li><li>Have you entered the correct database name?</li><li>Have you entered the correct username and pas
  sword?</li><li>Have you entered the correct database hostname and port number?</li></ul></li></ul></div>

Workaround

Fix the code. Prior to updating the db user password, the password needs to have all \ and ' quoted.

replace the section of lines in src/Sql/SqlMysql.php createdbSql() function

            // For MariaDB, ALTER USER was introduced in version 10.2. Support
            // for 10.1 ended in October 2020.
            $sql[] = sprintf("ALTER USER %s IDENTIFIED BY '%s';", $user, $dbSpec['password']);
            $sql[] = sprintf('GRANT ALL PRIVILEGES ON %s.* TO %s;', $dbname, $user);
            $sql[] = 'FLUSH PRIVILEGES;';

with

            // For MariaDB, ALTER USER was introduced in version 10.2. Support
            // for 10.1 ended in October 2020.
            $sql[] = sprintf("ALTER USER %s IDENTIFIED BY '%s';", $user, str_replace(["\\", "'"], ["\\\\", "\\'"], $dbSpec['password']));
            $sql[] = sprintf('GRANT ALL PRIVILEGES ON %s.* TO %s;', $dbname, $user);
            $sql[] = 'FLUSH PRIVILEGES;';

System Configuration

Q	A
Drush version?	11.5
Drupal version?	10.7
PHP version	8.1.17
OS?	Linux