MDX CA 2100安全问题 [英] MDX CA 2100 Security Issue

问题描述

我已经编写了MDX查询并将其分配给Adomd Connection对象.当我运行代码分析时，它为我提供了CA2100 Review SQL查询中的安全漏洞错误，我们无法直接向连接对象提供查询.它说我们应该将其嵌入到存储过程中，或者使用参数化查询.但就我而言，此查询没有任何参数.敬请帮助，我如何清除此CA 2100安全错误. PFB代码.预先感谢.

I have written MDX query and assigned it to Adomd Connection object. When i Run Code Analysis, it gives me CA2100 Review SQL queries for security vulnerabilities error we can't directly supply query to connection objects. It Says either we should embed it in Stored Procedure or use Parametrized query. But in my case, there are no parameters for this query. So kindly help, how can I remove this CA 2100 Security error. PFB the code. Thanks in advance.

            conn.Open();
        // Adomd Connection Object
        var adomdCommand = new AdomdCommand()
        {
            Connection = conn,
            CommandType = CommandType.Text,
            CommandText = mdxQuery
        };
        //Execute command to return cell set..
        CellSet csResult = adomdCommand.ExecuteCellSet();

        conn.Close();

推荐答案

您可以在执行AS查询执行的SQL Server中创建存储过程.假设sp是预先编译的，那是安全的.您应该执行以下操作:

You can create stored procedure in SQL Server that performs execution of AS query. Assuming sp's are pre-compiled, that's secure. You should do the following:

• 将AS服务器作为链接服务器添加到SQL数据库服务器(通过SSMS中的服务器对象)

• 创建存储过程.总体方案是(sp body):

• Add your AS server as linked server to SQL Database server (via Server Objects in SSMS)

• Create stored procedure. The general scheme is (sp body):

declare @tsqlquery varchar(1000)
declare @mdxquery varchar(2000)

set @tsqlquery = 'SELECT
                "[DimA].[A].[A name].[MEMBER_CAPTION]" as dimensionName, 
                convert(float, "[Measures].[X]") AS measureValue
              FROM OPENQUERY(<**YOUR LINKED SERVER**>,'

 set @mdxquery = '''**YOUR MDX QUERY**'  + '''' + ')'

 EXEC(@tsqlquery + @mdxquery)

您还可以创建参数化存储过程，以根据参数修改MDX查询文本.只要您使用存储过程，就可以安全.例如，我们的报表服务器查询仅使用SP，而不使用直接AS查询.

You can also create parametized store procedure that modifies MDX query text based on parameters. As long as you are using Stored Procedures, you are safe. For example, our report server queries only use SP's, not direct AS queries.

PS.如果您的AS服务器已定义角色安全性，则应为数据库启用ImpersonateCurrentUser，然后您的角色将起作用.

PS. If your AS server has role security defined, you should enable ImpersonateCurrentUser for the database, then your role will work.

这篇关于MDX CA 2100安全问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！