使用PHP shell_exec执行MySQL命令时出现1064语法错误求助

在开发PHP脚本时，通过shell_exec执行MySQL命令创建数据库、用户并授权，持续触发ERROR 1064语法错误，相关代码及错误日志如下：

代码片段 #

$databaseName = sprintf("%s_%s", substr($this->user, 0, 8), $installationData['worldId']);
// Create database with $databaseName $dbPassword
echo shell_exec("mysql -u root -p'testkmc123' -e \"CREATE DATABASE IF NOT EXISTS `$databaseName`\"");
echo shell_exec("mysql -u root -p'testkmc123' -e \"CREATE USER {$databaseName} IDENTIFIED BY '{$dbPassword}'\"");
echo shell_exec("mysql -u root -p'testkmc123' -e \"GRANT ALL PRIVILEGES ON {$databaseName}.* TO {$databaseName}\"");

错误日志 #

mysql: [Warning] Using a password on the command line interface can be insecure.
Jul 29 17:04:44 alma-vps.local runTasks.php[109697]: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to us>
Jul 29 17:04:44 alma-vps.local runTasks.php[109698]: mysql: [Warning] Using a password on the command line interface can be insecure.
Jul 29 17:04:44 alma-vps.local runTasks.php[109698]: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to us>
Jul 29 17:04:44 alma-vps.local runTasks.php[109699]: mysql: [Warning] Using a password on the command line interface can be insecure.
Jul 29 17:04:44 alma-vps.local runTasks.php[109699]: ERROR 1064 

问题原因与修复方案 #

1. 创建数据库命令的问题 #

原命令中未转义的反引号`$databaseName`会被Shell解析为命令替换，导致$databaseName被当作Shell命令执行，而非作为数据库名传递给MySQL。

修复：转义反引号，或改用单引号包裹SQL语句避免Shell解析特殊字符：

// 方案1：转义反引号
echo shell_exec("mysql -u root -p'testkmc123' -e \"CREATE DATABASE IF NOT EXISTS \`$databaseName\`\"");
// 方案2：用单引号包裹SQL
echo shell_exec("mysql -u root -p'testkmc123' -e 'CREATE DATABASE IF NOT EXISTS `$databaseName`'");

2. 创建用户命令的问题 #

MySQL的CREATE USER语法要求用户名必须用单引号包裹，且需指定用户主机（如localhost），原命令未满足这两个要求，触发语法错误。

修复：给用户名添加单引号和主机标识：

echo shell_exec("mysql -u root -p'testkmc123' -e \"CREATE USER '{$databaseName}'@'localhost' IDENTIFIED BY '{$dbPassword}'\"");

3. 授权命令的问题 #

GRANT语句中的用户同样需要单引号和主机标识，否则MySQL无法正确识别用户身份，导致语法错误。

修复：补充用户的单引号和主机：

echo shell_exec("mysql -u root -p'testkmc123' -e \"GRANT ALL PRIVILEGES ON {$databaseName}.* TO '{$databaseName}'@'localhost'\"");

内容的提问来源于stack exchange，提问作者Majed Faraj