MySQL GRANT语句变量替换异常及语法错误排查求助

问题描述 #

• 执行SQL语句 GRANT ALL PRIVILEGES ON name_$DBname.* TO 'name_$DBname'@'localhost' 时，报错：

  Access denied for user 'name'@'localhost' to database 'name_posmechanics'
  可见$DBname在数据库名中正常替换，但用户名部分仅显示name，变量未生效。

• 修改语句为 "GRANT ALL PRIVILEGES ON name_$DBname.* TO '. 'name_$DBname'@'localhost'. " 后，变量能正确显示为'name_posmechanics'@'localhost'，但出现语法错误：

  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'name_posmechanics'@'localhost'.' at line 1

完整代码如下：

add_action( 'plugins_loaded', array( 'pos_URL__db', 'init' ));   //This will trigger everytime the page is refreshed but only apply if all conditions are met
class pos_URL__db {

public static function init() {
        $POS__sys_db = __CLASS__;
        new $POS__sys_db;
    }
    
public function __construct()
{
add_action('init', array($this, 'pos__URL_DB'));
}

public function pos__URL_DB()
{

//--------to access the order objects----start-----
$orders = wc_get_orders(array(
    'customer_id' => get_current_user_id(),
    'return' => 'ids',
));
//add the id of the custom meta field that lies in the order
$meta_data = array();
foreach ($orders as $order_id) {
  $meta_data[$order_id] = get_post_meta($order_id, '_engx_text_field_id', true); //get the field value entered by the vendor on checkout
}
//--------to access the order objects-----end-----
        
        
if( isset($meta_data[$order_id]) ) { //is the custom field set

    //dependencies
    $servername = "localhost"; // always the same "localhost"
    //$username   = $meta_data[$order_id];  //get name of field unique to each user saved from checkout process
    $username   = "name";  //get name of field unique to each user saved from checkout process
    //$password   = "testing22"; //passcode for database
    $password   = 'TestIngTT'; //passcode for database
    $DBname     = $meta_data[$order_id]; //get name of field unique to each user saved from checkout process

    // Create connection
    $conn = mysqli_connect($servername, $username, $password);
    // Check connection
    if (!$conn) {
      die("Connection failed: " . mysqli_connect_error());
    }
    

    //mysql_connect('localhost','user',password);
    //mysql_query("CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';");
    //mysql_query("GRANT ALL ON db1.* TO 'username'@'localhost'");
    
    // Create User
    //$sql = "CREATE DATABASE  . $DBname . ";  //Name of NEW Database replace "myDB" with the POS-URL custom field meta value from checkout
    //$sql = "CREATE DATABASE testDB";  //Name of NEW Database replace "myDB" with the POS-URL custom field meta value from checkout
    
    $sqlcommands = array(
                "CREATE DATABASE name_$DBname",
                "CREATE USER 'name_$DBname'@'localhost' IDENTIFIED BY 'TestIngTT!'",
               //"GRANT ALL PRIVILEGES ON * . * TO name_$DBname",
                "GRANT ALL PRIVILEGES ON name_$DBname.* TO 'garagepo_$DBname'@'localhost'",
               //"GRANT ALL ON db1.* TO 'username'@'localhost'"
            );
        
        foreach ($sqlcommands as $sql)
    {
        // Create database
        if (mysqli_query($conn, $sql)) {
          echo "Database created successfully";  //comment when not needed
        } else {
          echo "Error creating database: " . mysqli_error($conn);  //comment when not needed
        } 
    }

    
    mysqli_close($conn);
    
    }
}//end pos__URL_DB

    

}//end Class pos_URL__db

解决方案 #

1. 变量替换不一致的原因 #

问题出在PHP对双引号字符串的解析规则：

• 数据库名部分name_$DBname.*没有被单引号包裹，PHP能直接识别$DBname为变量并完成替换。
• 用户名部分'name_$DBname'是被单引号包裹的内容（SQL语法要求用户名用单引号括起），如果PHP无法明确识别变量边界，就会导致$DBname未被解析；若代码中实际用单引号包裹整个SQL语句，变量根本不会被解析（单引号字符串内PHP不处理变量）。

2. 语法修正与正确写法 #

以下两种写法都能保证变量正确解析且SQL语法合法：

写法一：字符串拼接

把单引号拆分，用.连接变量与字符串：

"GRANT ALL PRIVILEGES ON name_$DBname.* TO 'name_" . $DBname . "'@'localhost'"

写法二：用花括号明确变量边界（推荐）

在双引号字符串中用{$变量名}明确变量范围，避免解析歧义：

"GRANT ALL PRIVILEGES ON name_{$DBname}.* TO 'name_{$DBname}'@'localhost'"

你之前修改的语句添加了多余的.和空格，导致SQL出现无效语法（比如TO . 'name_posmechanics'@'localhost'.），这就是语法错误的根源，去掉多余的拼接符号即可。

3. 额外提示 #

• 确保执行CREATE USER和GRANT的MySQL用户具备对应权限（如CREATE USER、GRANT OPTION），否则会出现权限报错。
• $DBname来自用户输入的自定义字段，建议先做过滤（比如限制仅含字母、数字、下划线），避免SQL注入风险。

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