解剖SQLSERVER 第十五篇 SQLSERVER存储过程的源文本存放在哪里

解剖SQLSERVER 第十五篇 SQLSERVER存储过程的源文本存放在哪里？（译） http://improve.dk/where-does-sql-server-store-the-source-for-stored-procedures/ 目前我正在扩展OrcaMDF Studio的功能 不单只支持系统表，DMVs 和用户表 而且也要支持存储过程。那

解剖SQLSERVER 第十五篇  SQLSERVER存储过程的源文本存放在哪里？（译）

http://improve.dk/where-does-sql-server-store-the-source-for-stored-procedures/

目前我正在扩展OrcaMDF Studio的功能 不单只支持系统表，DMVs 和用户表 而且也要支持存储过程。那很容易，我们只需要查询sys.procedures --或者查询sys.sysschobjs，

因为当SQLSERVER没有在运行的时候我们是不能查询sys.procedures 的

然而，我不想只是列出存储过程名称，我也需要显示存储过程里面的源代码。这带来了新的任务--检索源代码。源代码存储在哪里？

我在Google上找不到任何有用的资料，所以我们只能依靠自己观察了！

 

我已经创建了一个新的空数据库 这个数据库有一个3MB的数据文件。在这个数据库里面，我已经创建了一个单独的存储过程就像这样：

<span>SET</span> ANSI_NULLS <span>ON</span>
<span>GO</span>
<span>SET</span> QUOTED_IDENTIFIER <span>ON</span>
<span>GO</span>
<span>--</span><span> =============================================</span><span>
--</span><span> Author:        </span><span>
--</span><span> Create date: </span><span>
--</span><span> Description:    </span><span>
--</span><span> =============================================</span>
<span>CREATE</span> <span>PROCEDURE</span><span> XYZ
    </span><span>AS</span>
<span>BEGIN</span>
    <span>--</span><span> SET NOCOUNT ON added to prevent extra result sets from</span>
    <span>--</span><span> interfering with SELECT statements.</span>
    <span>SET</span> NOCOUNT <span>ON</span><span>;

    </span><span>--</span><span> Insert statements for procedure here</span>
    <span>SELECT</span> <span>'</span><span>AABBCC</span><span>'</span> <span>AS</span><span> Output
</span><span>END</span>

 

现在，当我select * from sys.procedures的时候，我们可以看到存储过程的object ID 是2105058535

<span>select</span> <span>*</span> <span>from</span> sys.procedures

到目前为止一切顺利。然后我们可以检索存储过程的定义 使用查询sys.sql_modules 视图返回nvarchar(MAX)类型的定义文本

<span>select</span> <span>*</span> <span>from</span> sys.sql_modules <span>where</span> <span>object_id</span> <span>=</span> <span>2105058535</span>

 

上面就是XYZ存储过程的源代码！等下，我可以从sys.sysschobjs表里获取存储过程的object ID，我不需要访问
sys.sql_modules ，sys.sql_modules 只是一个视图而不是系统表。我们看一下sys.sql_modules 视图是如何获取定义的：

<span>select</span> object_definition(<span>object_id</span>(<span>'</span><span>sys.sql_modules</span><span>'</span>))

<span>SELECT</span>
    <span>object_id</span> <span>=</span><span> o.id,
    definition </span><span>=</span><span> Object_definition(o.id),
    uses_ansi_nulls </span><span>=</span> Sysconv(<span>bit</span>, o.status <span>&</span> <span>0x40000</span>), <span>--</span><span> OBJMOD_ANSINULLS</span>
    uses_quoted_identifier <span>=</span> sysconv(<span>bit</span>, o.status <span>&</span> <span>0x80000</span>),   <span>--</span><span> OBJMOD_QUOTEDIDENT</span>
    is_schema_bound <span>=</span> sysconv(<span>bit</span>, o.status <span>&</span> <span>0x20000</span>),    <span>--</span><span> OBJMOD_SCHEMABOUND</span>
    uses_database_collation <span>=</span> sysconv(<span>bit</span>, o.status <span>&</span> <span>0x100000</span>),  <span>--</span><span> OBJMOD_USESDBCOLL</span>
    is_recompiled <span>=</span> sysconv(<span>bit</span>, o.status <span>&</span> <span>0x400000</span>),     <span>--</span><span> OBJMOD_NOCACHE</span>
    null_on_null_input <span>=</span> sysconv(<span>bit</span>, o.status <span>&</span> <span>0x200000</span>),   <span>--</span><span> OBJMOD_NULLONNULL</span>
    execute_as_principal_id <span>=</span><span> x.indepid
</span><span>FROM</span><span>
    sys.sysschobjs o
</span><span>LEFT</span> <span>JOIN</span><span>
    sys.syssingleobjrefs x </span><span>ON</span> x.depid <span>=</span> o.id <span>AND</span> x.class <span>=</span> <span>22</span> <span>AND</span> x.depsubid <span>=</span> <span>0</span> <span>--</span><span> SRC_OBJEXECASOWNER</span>
<span>WHERE</span><span>
    o.pclass </span><span></span> <span>100</span> <span>AND</span><span>
    (
        (o.type </span><span>=</span> <span>'</span><span>TR</span><span>'</span> <span>AND</span> has_access(<span>'</span><span>TR</span><span>'</span>, o.id, o.pid, o.nsclass) <span>=</span> <span>1</span>) <span>OR</span><span>
        (type </span><span>IN</span> (<span>'</span><span>P</span><span>'</span>,<span>'</span><span>V</span><span>'</span>,<span>'</span><span>FN</span><span>'</span>,<span>'</span><span>IF</span><span>'</span>,<span>'</span><span>TF</span><span>'</span>,<span>'</span><span>RF</span><span>'</span>,<span>'</span><span>IS</span><span>'</span>) <span>AND</span> has_access(<span>'</span><span>CO</span><span>'</span>, o.id) <span>=</span> <span>1</span>) <span>OR</span><span>
        (type </span><span>IN</span> (<span>'</span><span>R</span><span>'</span>,<span>'</span><span>D</span><span>'</span>) <span>AND</span> o.pid <span>=</span> <span>0</span><span>)
    )</span>

大家如果使用sqlprompt的话也可以直接显示定义而不需要执行object_definition函数

 

可以看到sys.sql_modules 视图也是使用系统函数object_definition 来获取代码
不幸的是,下面的代码无法工作

<span>select</span> object_definition(<span>object_id</span>(<span>'</span><span>object_definition</span><span>'</span>))

 

我碰巧记得有一个废弃的视图可以代替sys.sql_modules，sys.syscomments 视图
我们看一下获取到的代码

<span>select</span> object_definition(<span>object_id</span>(<span>'</span><span>sys.syscomments</span><span>'</span>))

<span>SELECT</span><span>
    o.id </span><span>AS</span><span> id,  
    </span><span>convert</span>(<span>smallint</span>, <span>case</span> <span>when</span> o.type <span>in</span> (<span>'</span><span>P</span><span>'</span>, <span>'</span><span>RF</span><span>'</span>) <span>then</span> <span>1</span> <span>else</span> <span>0</span> <span>end</span>) <span>AS</span> <span>number</span><span>,  
    s.colid,
    s.status,  
    </span><span>convert</span>(<span>varbinary</span>(<span>8000</span>), s.<span>text</span>) <span>AS</span><span> ctext,  
    </span><span>convert</span>(<span>smallint</span>, <span>2</span> <span>+</span> <span>4</span> <span>*</span> (s.status <span>&</span> <span>1</span>)) <span>AS</span><span> texttype,  
    </span><span>convert</span>(<span>smallint</span>, <span>0</span>) <span>AS</span><span> language,  
    sysconv(</span><span>bit</span>, s.status <span>&</span> <span>1</span>) <span>AS</span><span> encrypted,  
    sysconv(</span><span>bit</span>, <span>0</span>) <span>AS</span><span> compressed,  
    s.</span><span>text</span>  
<span>FROM</span><span>
    sys.sysschobjs o
</span><span>CROSS</span><span> APPLY
    </span><span>OpenRowset</span>(<span>TABLE</span> SQLSRC, o.id, <span>0</span><span>) s  
</span><span>WHERE</span><span>
    o.nsclass </span><span>=</span> <span>0</span> <span>AND</span><span>
    o.pclass </span><span>=</span> <span>1</span> <span>AND</span><span>
    o.type </span><span>IN</span> (<span>'</span><span>C</span><span>'</span>,<span>'</span><span>D</span><span>'</span>,<span>'</span><span>P</span><span>'</span>,<span>'</span><span>R</span><span>'</span>,<span>'</span><span>V</span><span>'</span>,<span>'</span><span>X</span><span>'</span>,<span>'</span><span>FN</span><span>'</span>,<span>'</span><span>IF</span><span>'</span>,<span>'</span><span>TF</span><span>'</span>,<span>'</span><span>RF</span><span>'</span>,<span>'</span><span>IS</span><span>'</span>,<span>'</span><span>TR</span><span>'</span>) <span>AND</span><span>
    has_access(</span><span>'</span><span>CO</span><span>'</span>, o.id) <span>=</span> <span>1</span>  

<span>UNION</span> <span>ALL</span>  

<span>SELECT</span><span>
    c.</span><span>object_id</span> <span>AS</span><span> id,  
    </span><span>convert</span>(<span>smallint</span>, c.column_id) <span>AS</span> <span>number</span><span>,  
    s.colid,
    s.status,  
    </span><span>convert</span>(<span>varbinary</span>(<span>8000</span>), s.<span>text</span>) <span>AS</span><span> ctext,  
    </span><span>convert</span>(<span>smallint</span>, <span>2</span> <span>+</span> <span>4</span> <span>*</span> (s.status <span>&</span> <span>1</span>)) <span>AS</span><span> texttype,  
    </span><span>convert</span>(<span>smallint</span>, <span>0</span>) <span>AS</span><span> language,  
    sysconv(</span><span>bit</span>, s.status <span>&</span> <span>1</span>) <span>AS</span><span> encrypted,  
    sysconv(</span><span>bit</span>, <span>0</span>) <span>AS</span><span> compressed,  
    s.</span><span>text</span>  
<span>FROM</span><span>
    sys.computed_columns c
</span><span>CROSS</span><span> APPLY
    </span><span>OpenRowset</span>(<span>TABLE</span> SQLSRC, c.<span>object_id</span><span>, c.column_id) s  

</span><span>UNION</span> <span>ALL</span>  

<span>SELECT</span><span>
    p.</span><span>object_id</span> <span>AS</span><span> id,  
    </span><span>convert</span>(<span>smallint</span>, p.procedure_number) <span>AS</span> <span>number</span><span>,  
    s.colid,
    s.status,  
    </span><span>convert</span>(<span>varbinary</span>(<span>8000</span>), s.<span>text</span>) <span>AS</span><span> ctext,  
    </span><span>convert</span>(<span>smallint</span>, <span>2</span> <span>+</span> <span>4</span> <span>*</span> (s.status <span>&</span> <span>1</span>)) <span>AS</span><span> texttype,  
    </span><span>convert</span>(<span>smallint</span>, <span>0</span>) <span>AS</span><span> language,  
    sysconv(</span><span>bit</span>, s.status <span>&</span> <span>1</span>) <span>AS</span><span> encrypted,  
    sysconv(</span><span>bit</span>, <span>0</span>) <span>AS</span><span> compressed,  
    s.</span><span>text</span>  
<span>FROM</span><span>
    sys.numbered_procedures p
</span><span>CROSS</span><span> APPLY
    </span><span>OpenRowset</span>(<span>TABLE</span> SQLSRC, p.<span>object_id</span><span>, p.procedure_number) s  

</span><span>UNION</span> <span>ALL</span>  

<span>SELECT</span><span>
    o.id </span><span>AS</span><span> id,  
    </span><span>convert</span>(<span>smallint</span>, <span>case</span> <span>when</span> o.type <span>in</span> (<span>'</span><span>P</span><span>'</span>, <span>'</span><span>RF</span><span>'</span>) <span>then</span> <span>1</span> <span>else</span> <span>0</span> <span>end</span>) <span>AS</span> <span>number</span><span>,  
    s.colid,
    s.status,  
    </span><span>convert</span>(<span>varbinary</span>(<span>8000</span>), s.<span>text</span>) <span>AS</span><span> ctext,  
    </span><span>convert</span>(<span>smallint</span>, <span>2</span>) <span>AS</span><span> texttype,  
    </span><span>convert</span>(<span>smallint</span>, <span>0</span>) <span>AS</span><span> language,  
    sysconv(</span><span>bit</span>, <span>0</span>) <span>AS</span><span> encrypted,  
    sysconv(</span><span>bit</span>, <span>0</span>) <span>AS</span><span> compressed,  
    s.</span><span>text</span>  
<span>FROM</span><span>
    sys.sysobjrdb o
</span><span>CROSS</span><span> APPLY
    </span><span>OpenRowset</span>(<span>TABLE</span> SQLSRC, o.id, <span>0</span><span>) s  
</span><span>WHERE</span>
    <span>db_id</span>() <span>=</span> <span>1</span> <span>AND</span><span> 
    o.type </span><span>IN</span> (<span>'</span><span>P</span><span>'</span>,<span>'</span><span>V</span><span>'</span>,<span>'</span><span>X</span><span>'</span>,<span>'</span><span>FN</span><span>'</span>,<span>'</span><span>IF</span><span>'</span>,<span>'</span><span>TF</span><span>'</span>)

 

很令人失望，他不使用object_definition, 而是使用另一个内部函数格式是OpenRowset(TABLE SQLSRC, o.id, 0)。我不会轻易放弃 --我对 OpenRowset(TABLE RSCPROP)函数进行逆向

 

让我们使用不同的方法去解决这个问题。在SQLSERVER里面任何东西的存储都使用8KB页面的固定格式。当存储过程不是加密的，他们一定以明文存储在数据库的某个地方--只是我们不知道在哪个地方。

我们分离数据库并使用hex编辑器进行破解（我推荐使用HxD这个hex编辑器）

HxD hex编辑器下载：

http://files.cnblogs.com/lyhabc/HxDhex%E7%BC%96%E8%BE%91%E5%99%A8.rar

 

我们为了要找到存储过程的位置，我在存储过程里故意使用“SELECT ‘AABBCC’ 这个字符串
以便于我们能够容易的找到存储过程的所在位置：

 

我们找到了：

好了，我们现在代码是存储在数据库里面。数据存储在偏移位置为0x00101AF0 的数据文件里。十进制值是01055472。我们知道数据页面是8KB，我们可以计算代码所在的页面编号

01055472 / 8192 = 128

现在我们知道代码存储在页面号128页上 --我们重新附加数据库，使用DBCC PAGE看一下页面内容：

<span>--</span><span>只显示数据页面头</span>
<span>DBCC</span> TRACEON (<span>3604</span><span>)
</span><span>GO</span>
<span>DBCC</span> PAGE(Test2, <span>1</span>, <span>128</span>, <span>0</span><span>)
</span><span>GO</span>

 

注意，对于DBCC PAGE 命令我使用了页面样式0作为执行。在这里我只想查看数据页面头--那里会有一些有趣的东西

 

正如所料，这是一个正常的数据页面，m_type 字段显示的值为1（type id为1表示这是数据库内部的数据页面）
更有趣的是，我们可以看到页面属于object ID 60！我们看一下object ID 60是什么对象：

<span>select</span> <span>*</span> <span>from</span> sys.sysobjects <span>where</span> id <span>=</span> <span>60</span>

让我们看看sys.sysobjvalues的内容。注意，当你查询sys.sysobjvalues视图的时候，需要使用DAC连接，可以看到他实际上是一个内部的系统表：

<span>select</span> <span>*</span> <span>from</span> sys.sysobjvalues

这里显示的很多内容我们都不需要关心，不过我们需要尝试过滤出我们的存储过程object ID为2105058535的信息：

<span>select</span> <span>*</span> <span>from</span> sys.sysobjvalues where objid = 2105058535

 

我想知道imageval 列包含了什么内容，如果我没有记错 0x2D2D 在ASCII里面应该是“-”
这提醒了我 XYZ这个存储过程刚开始的时候 ，我们尝试将这列的值转换为我们可读的形式

<span>select</span> <span>convert</span>(<span>varchar</span>(<span>max</span>), imageval) <span>from</span> sys.sysobjvalues <span>where</span> objid <span>=</span> <span>2105058535</span>

 

 

亲爱的读者，这就是XYZ存储过程的源代码，他存储在sys.sysobjvalues系统表中。
作为最后一个例子，下面是不依靠object_definition（）函数和sys.sql_modules视图从而检索出用户存储过程的源代码列表

<span>select</span><span>
    p.name,
    </span><span>cast</span>(v.imageval <span>as</span> <span>varchar</span>(<span>MAX</span><span>))
</span><span>from</span><span>
    sys.procedures p
</span><span>inner</span> <span>join</span><span>
    sys.sysobjvalues v </span><span>on</span> p.<span>object_id</span> <span>=</span> v.objid

 

 

第十五篇完