Java SQL Prepared Statement更新语句执行失败求助排查

我遇到了无法运行以下代码的问题，尝试多次修改仍始终报错，烦请帮忙排查问题所在。

TestCommand类代码 #

public class TestCommand implements ServerCommand{

    @Override
    public void performcommand(SlashCommandInteractionEvent event, Member m, MessageChannelUnion channel,
            VoiceChannel ec, VoiceChannel pub, TextChannel log, Guild guild) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
                
        String sql = ("UPDATE member_ships SET?= ? WHERE UUID= ?"); 
        
        try {
            connection = MySQL.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,"Cyclone");
            preparedStatement.setInt(2,9);
            preparedStatement.setLong(3,316931131188576123l);
            preparedStatement.executeUpdate();
        }   catch (SQLException e) {
            e.printStackTrace();
        } finally {
            MySQL.closePreparedStatement(preparedStatement);
            MySQL.closeConnection(connection);
        }
    }
}

MySQL连接类代码 #

public class MySQL {

    public static Connection getConnection() throws SQLException {
        Connection connection = DriverManager.getConnection("jdbc:mysql://*****","***","****");
        connection.setAutoCommit(true);
        System.out.println("Database connecion successful"); //TODO Remove 
        return connection;
    }
}

错误栈信息 #

java.sql.SQLSyntaxErrorException: 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 ''Cyclone'= 9 WHERE UUID= 316931131188576123' at line 1
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1061)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1009)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1320)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:994)

核心问题 #

PreparedStatement的?占位符只能用来替换SQL中的值，不能替换列名、表名这类数据库对象名称。你的代码中把列名Cyclone通过占位符传入，JDBC会自动给它加上单引号，最终生成的非法SQL为：

UPDATE member_ships SET 'Cyclone'= 9 WHERE UUID= 316931131188576123

列名被单引号包裹后不符合SQL语法，因此抛出语法错误。

修复方案 #

如果需要动态指定列名，直接在SQL字符串中拼接（必须做合法性校验避免SQL注入），修改后的代码如下：

public class TestCommand implements ServerCommand{

    @Override
    public void performcommand(SlashCommandInteractionEvent event, Member m, MessageChannelUnion channel,
            VoiceChannel ec, VoiceChannel pub, TextChannel log, Guild guild) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        
        // 定义要更新的列名
        String columnName = "Cyclone";
        // 校验列名合法性，限制只能使用预设的列名列表
        if (!Arrays.asList("Cyclone", "其他允许的列名").contains(columnName)) {
            // 非法列名，直接终止操作
            return;
        }
        
        // 拼接合法的SQL语句
        String sql = String.format("UPDATE member_ships SET %s= ? WHERE UUID= ?", columnName); 
        
        try {
            connection = MySQL.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,9);
            preparedStatement.setLong(2,316931131188576123L);
            preparedStatement.executeUpdate();
        }   catch (SQLException e) {
            e.printStackTrace();
        } finally {
            MySQL.closePreparedStatement(preparedStatement);
            MySQL.closeConnection(connection);
        }
    }
}

注意事项 #

• 动态拼接列名时，必须校验列名是否属于预先定义的合法列表，绝对不能直接拼接用户输入的内容，防止SQL注入攻击。
• 只有SQL中的值才能使用PreparedStatement的占位符，这是JDBC占位符的核心规则。

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