SQL Server加密存储过程在CodeIgniter调用返回空结果问题排查

问题描述 #

在SQL Server中有一个加密的存储过程RptLRA_Gabungan，无修改权限。通过SQL Server Management Studio（SSMS）执行能正常返回结果，但使用CodeIgniter调用时返回空数组；同时新建的未加密存储过程在CodeIgniter中调用完全正常。需要排查问题原因，并找到无修改权限前提下的解决办法。

可能原因 #

1. 加密存储过程的元数据访问限制 #

SQL Server加密存储过程的定义会被加密，CodeIgniter的数据库驱动可能无法提前解析存储过程的返回结果集结构（如字段名、数据类型），导致无法正确将结果映射为数组。而SSMS无需提前解析元数据就能直接展示执行结果，因此不受影响。

2. 参数传递格式差异 #

原代码直接拼接exec语句作为字符串传入query()方法，可能存在参数格式（如日期字符串的处理、空字符串参数的传递逻辑）与SSMS执行时的差异，导致存储过程内部逻辑返回空结果。

3. CodeIgniter驱动兼容性问题 #

CodeIgniter自带的SQL Server驱动对加密存储过程的支持不足，比如无法正确处理加密存储过程返回的结果集，或是驱动在处理加密对象时存在内部逻辑缺陷。

无修改权限下的解决办法 #

方法1：使用OPENROWSET强制获取结果集 #

通过OPENROWSET调用加密存储过程，绕开元数据解析的限制。需确保当前数据库用户拥有ADMINISTER BULK OPERATIONS权限，且服务器配置允许使用OPENROWSET：

public function getData(){
    $this->db2 = $this->load->database('mssql', TRUE);
    // 替换为实际的服务器、数据库信息，若用SQL身份验证则修改连接字符串
    $sql = "SELECT * FROM OPENROWSET('SQLNCLI', 'Server=你的服务器名;Database=你的数据库名;Trusted_Connection=yes;', 'EXEC RptLRA_Gabungan @Tahun=''2022'', @Kd_Urusan='''', @Kd_Bidang='''', @Kd_Unit='''', @Kd_Sub='''', @Kd_Prog='''', @Id_Prog='''', @Kd_Keg='''', @level=''3'', @D1=''20220101'', @D2=''20221231''')";
    $result = $this->db2->query($sql);

    if(!$result){
        return $this->db2->last_query();
    }else{
        return $result->result_array();
    }
}

方法2：改用参数绑定传递参数 #

避免直接拼接SQL字符串，使用CodeIgniter的参数绑定功能，确保参数格式与SSMS执行时一致：

public function getData(){
    $this->db2 = $this->load->database('mssql', TRUE);
    $sql = "EXEC RptLRA_Gabungan @Tahun=?, @Kd_Urusan=?, @Kd_Bidang=?, @Kd_Unit=?, @Kd_Sub=?, @Kd_Prog=?, @Id_Prog=?, @Kd_Keg=?, @level=?, @D1=?, @D2=?";
    $params = [
        '2022', '', '', '', '', '', '', '', '3', '20220101', '20221231'
    ];
    $result = $this->db2->query($sql, $params);

    if(!$result){
        return $this->db2->last_query();
    }else{
        return $result->result_array();
    }
}

参数绑定能自动处理数据类型转换，避免字符串拼接导致的格式错误。

方法3：核对数据库用户权限 #

确认CodeIgniter使用的数据库用户，与SSMS执行时的用户拥有相同的权限——包括执行该加密存储过程、读取结果集的权限。部分情况下SSMS使用的是Windows身份验证，而CodeIgniter使用SQL身份验证，权限范围可能存在差异。

方法4：切换为PDO驱动 #

如果当前使用的是CodeIgniter自带的SQL Server驱动，可尝试切换为PDO驱动，其对加密存储过程的兼容性可能更好。修改数据库配置文件：

$db['mssql'] = array(
    'dsn'   => 'sqlsrv:Server=你的服务器名;Database=你的数据库名',
    'username' => '你的用户名',
    'password' => '你的密码',
    'dbdriver' => 'pdo',
    // 保留其他原有配置
);

原代码示例 #

public function getData(){
    $this->db2 = $this->load->database('mssql', TRUE);
    $result = $this->db2->query("exec RptLRA_Gabungan @Tahun='2022', @Kd_Urusan='', @Kd_Bidang = '', @Kd_Unit = '', @Kd_Sub = '', @Kd_Prog = '', @Id_Prog = '', @Kd_Keg = '', @level='3',  @D1 = '20220101', @D2 = '20221231'");

    if(!$result){
        return $this->db2->last_query();
    }else{
        return $result->result_array();
    }
}

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