🚀 go-pugleaf

RetroBBS NetNews Server

Inspired by RockSolid Light RIP Retro Guy

Article View: cn.bbs.comp.database
Article #948

HOW TO: �������SQL Server��ݿ��SQLע�빥��

#948
From: Galaxy��VOLUNTEERS
Date: Wed, 03 Sep 2008 13:33
239 lines
11249 bytes
Q: ��α����ҵ�SQL Server��ݿ�����SQLע�빥����
A: 
SQLע����������Ĺ�������Internet��SQL Server��ݿ�ķ�������ʹ��������
����ǽ�����˷�����ֻҪӦ�ó������©������ô��Ȼ�����ܹ����Ŀ��ܡ�΢���Ѿ�ע
�⵽������ʹ��ASP��ASP.NET��վ�Ĺ����������������ơ��ⲻ���ᵼ����ݵ�ʧ��
�Ͷ�ʧ���ڽ��ڵĺܶస������ݿ��л��������߼������javascript���룬�⵼�·�
����վ�Ŀͻ�����Ҳ����˸�Ⱦ������

�����������ݿ��SQLע�빥�������µļ��ַ�����

1. ʹ��˫���
ʹ��˫��Ż��������滻����û�������ֵ�����ּ򵥵�Ԥ����ʩ�ܽ�Ϊ��Ч�ķ���
�൱���SQLע�빥��������ž��������ڽ���SQL���ʽ��ʹ��������ݻ�ò���Ҫ��
Ȩ�ޡ�
�������£�
string strSanitizedInput = strInput.Replace("'", "''");

ע�⣺���ס��ʹ���Ѿ��滻�˵���ţ�������Ҳ�п����ƹ����������ʩ��

2. ���⶯̬SQL
��̬SQL�Ƕ�̬��ѯ�ĺܺõĹ��ߣ�����Ҳ�ᱩ¶�����㡣�ܶ�����¿���ʹ������SQL
����洢��������涯̬SQL����Ȼ��������ڴ洢�������Ȼͨ���û�������������
̬SQL��ѯ����ôֻ���滻���Dz���ȫ�ġ�

�Ϻõ�ʹ�ò����SQL������£�
private void cmdLogin_Click(object sender, System.EventArgs e) {
    string strcode = ConfigurationSettings.AppSettings["CodeBad"];
    using (SqlConnection code = new SqlConnection(strcode))
    {
        SqlParameter prm;

        code.Open();

        string strQry = 
            "SELECT Count(*) FROM Users WHERE UserName=@username " +
            "AND Password=@password";
        int intRecs;

        SqlCommand cmd = new SqlCommand(strQry,code);
        cmd.CommandType= CommandType.Text;

        prm = new SqlParameter("@username",SqlDbType.VarChar,50);
        prm.Direction=ParameterDirection.Input;
        prm.Value = txtUser.Text;
        cmd.Parameters.Add(prm);

        prm = new SqlParameter("@password",SqlDbType.VarChar,50);
        prm.Direction=ParameterDirection.Input;
        prm.Value = txtPassword.Text;
        cmd.Parameters.Add(prm);            

        intRecs = (int) cmd.ExecuteScalar();

        if (intRecs>0) {
            FormsAuthentication.RedirectFromLoginPage(txtUser.Text, false);
        }
        else {
            lblMsg.Text = "Login attempt failed.";
        }
    }
}

ʹ��procVerifyUser�洢�������֤�û���һ�ָ�õķ�����
private void cmdLogin_Click(object sender, System.EventArgs e) {
    string strcode = 
        ConfigurationSettings.AppSettings["CodeBetter"];
    using (SqlConnection code = new SqlConnection(strcode))
    {
        SqlParameter prm;

        code.Open();

        string strAccessLevel;

        SqlCommand cmd = new SqlCommand("procVerifyUser", code);
        cmd.CommandType= CommandType.StoredProcedure;

        prm = new SqlParameter("@username",SqlDbType.VarChar,50);
        prm.Direction=ParameterDirection.Input;
        prm.Value = txtUser.Text;
        cmd.Parameters.Add(prm);

        prm = new SqlParameter("@password",SqlDbType.VarChar,50);
        prm.Direction=ParameterDirection.Input;
        prm.Value = txtPassword.Text;
        cmd.Parameters.Add(prm);            

        strAccessLevel = (string) cmd.ExecuteScalar();

        if (strAccessLevel.Length>0) {
            FormsAuthentication.RedirectFromLoginPage(txtUser.Text, false);
        }
        else {
            lblMsg.Text = "Login attempt failed.";
        }
    }
}

3. ��֤���е�����
��Զ��Ҫ�����û������롣
�����������ֻ�ܰ����֣��Ǿ���Ҫ��֤�û������ֵ�Ƿ�ֻ�����֡����������
���ַ���ô����Ҫ����Ƿ������?Χ֮����ַ����Ӧ�ó���Ӧ�ð�������
�ţ��Ⱥţ�ð�ţ����ź�SQL�ؼ��ֵļ�顣.NET Framework�ṩ���õ���������ʽ
���������̡������û�����ij���Ҳ��һ�ֺܺõķ�����
�����ʹ�����д������������ֵ����4-12λ�����֣���ĸ���»��ߣ�
[\d_a-zA-Z]{4,12}

4. ���Ȩ��ԭ��
��ԶҲ��Ҫ�ڴ������ù���Ա����Ȩ�޵��ʻ���������ݿ⡣
Ӧ�ó���ʹ�õ��ʻ�Ӧ��ֻӵ��Ӧ�ó�����Ҫ����ͼ����Ȩ�ޡ���Ҳ�ܽ�����������
�ɵ��ƻ����͵�����޶ȡ�Ӧ�ó�������sa���������Ա�ʺ�������ݿ⣬����Ӧ��
��ʹ�õ��ʺ��ܷ��ʵķ�Χ��

�����ʹ��ϵͳ����ԱȨ�޵Ĵ������£�
<add key="CodeBad"
value="server=localhost;uid=sa;pwd=;database=northwind;" />

�����Ƶ���ȷ�������£�
<add key="CodeGood"
value="server=localhost;uid=NWindReader;pwd=utbbeesozg4d;
database=northwind;" />

5. ��ȫ�洢������Ϣ
��Ҫ�����Ĵ洢������Ϣ��
�Ϻõ������ʹ�ü��ܻ�ɢ�����롣ɢ������ϼ��������ȫ��������Ϊ���Dz�
�ܱ����ܡ��㻹������ɢ�м���֮ǰ���һЩ���ֵ�����Ӱ�ȫ�ԡ�
�õĴ������£�
private void cmdLogin_Click(object sender, System.EventArgs e) {
    try {
        // Grab the encrypted connection string and decrypt it
        string strcode = SecureConnection.GetcodeString("CodeBest");

        // Establish connection to database
        using (SqlConnection code = new SqlConnection(strcode))
        {
            SqlParameter prm;

            code.Open();

            // Execute sproc to retrieved hashed password for this user
            string strHashedDbPwd;

            SqlCommand cmd = new SqlCommand("procGetHashedPassword", code);
            cmd.CommandType = CommandType.StoredProcedure;
            prm = new SqlParameter("@username", SqlDbType.VarChar,50);
            prm.Direction = ParameterDirection.Input;
            prm.Value = txtUser.Text;
            cmd.Parameters.Add(prm);

            strHashedDbPwd = (string) cmd.ExecuteScalar();

            if (strHashedDbPwd.Length>0) {
                // Verify that hashed user-entered password is the same
                // as the hashed password from the database
                if (SaltedHash.ValidatePassword(txtPassword.Text, 
                    strHashedDbPwd)) {
                    FormsAuthentication.RedirectFromLoginPage(
                        txtUser.Text, false);
                }
                else {
                    lblMsg.Text = "Login attempt failed.";
                }
            }
            else {
                lblMsg.Text = "Login attempt failed.";
            }
        }
    }
    catch {
        lblMsg.Text = "Login attempt failed.";
    }
}

���ܵ������ַ���Web.Config�д洢���£�
<add key="CodeBest"
value="AQAAANCMnd8BFdERjHoAwE/
Cl+sBAAAAcWMZ8XhPz0O8jHcS1539LAQAAAACAAAAAAADZgAAqAAAABAAAABdodw0YhWfcC6+
UjUUOiMwAAAAAASAAACgAAAAEAAAALPzjTRnAPt7/W8v38ikHL5IAAAAzctRyEcHxWkzxeqbq/
V9ogaSqS4UxvKC9zmrXUoJ9mwrNZ/
XZ9LgbfcDXIIAXm2DLRCGRHMtrZrp9yledz0n9kgP3b3s+
X8wFAAAANmLu0UfOJdTc4WjlQQgmZElY7Z8"
/>

6. �ʵ��ش����쳣
�������еĴ����м����쳣���?�������е��쳣Ӧ���ṩ����������Ϣ��
����û�д�����쳣��Ӧ��ȷ������ʹ�����߻�����õ���Ϣ������ͨ������Web.Confi
g�е�debugѡ�������CustomError��ģʽΪ"RemoteOnly"��
����
<compilation defaultLanguage="c#"
    debug="false"
/>

<customErrors mode="RemoteOnly" 
/>

����ߣ�
΢��ҲΪ����Ա�ṩ�˼�������ߣ����ڼ�⣬������ʶ����ܵĴ����㡣

��� - HP Scrawlr
http://www.communities.hp.com/securitysoftware/blogs/spilabs/archive/2008/06/
23/finding-sql-injection-with-scrawlr.aspx

���� �C UrlScan 3.0�� Beta
http://learn.iis.net/page.aspx/473/using-urlscan

ʶ�� �C Microsoft Source Code Analyzer for SQL Injection
http://support.microsoft.com/kb/954476

����ϸ��SQL Server���ŷ���ʵ����Ϣ�ɲο���
"SQL Server 2005 Security Best Practices - Operational and Administrative 
Tasks"
http://www.microsoft.com/technet/prodtechnol/sql/2005/sql2005secbestpract.msp
x 

"How To - Protect from Injection Attacks in ASP.NET"
http://msdn.microsoft.com/en-us/library/bb355989.aspx

"How To - Protect from SQL Injection in ASP.NET"
http://msdn.microsoft.com/en-us/library/ms998271.aspx

"How To - Protect from Cross-Site Scripting in ASP.NET"
http://msdn.microsoft.com/en-us/library/ms998274.aspx

"Design Guidelines"
http://msdn.microsoft.com/en-us/library/aa302420.aspx

"Arch/Design Inspection"
http://msdn.microsoft.com/en-us/library/aa302421.aspx

"Microsoft Security Advisory"
http://www.microsoft.com/technet/security/advisory/954462.mspx 
--
I thought what I'd do was, I'd pretend I was one of those deaf-mutes
Or should I?

You know what I��d like to be? I mean if I had my goddam choice, I��d just be
the catcher in the rye and all.

�� ��Դ:����ˮ˼Դ bbs.sjtu.edu.cn��[FROM: 207.46.55.28]
Technical Details
Message-ID: <4bhSWi$aqb@bbs.sjtu.edu.cn>
Path: arc1-ca.pugleaf.net!g2news2.google.com!news2.google.com!border1.nntp.dca.giganews.com!nntp.giganews.com!news.neu.edu.cn!news.shec6.edu.cn!SJTU

Show Full Headers
Complete Article Headers
X-Google-Thread: 101948,39109f073e839410,start
X-Google-Attributes: gid101948,domainid0,public,usenet
X-Google-Language: Chinese,GB
Path: arc1-ca.pugleaf.net!g2news2.google.com!news2.google.com!border1.nntp.dca.giganews.com!nntp.giganews.com!news.neu.edu.cn!news.shec6.edu.cn!SJTU
From: Dem.bbs@bbs.sjtu.edu.cn (Galaxy��VOLUNTEERS)
Newsgroups: cn.bbs.comp.database
Subject: HOW TO: �������SQL Server��ݿ��SQLע�빥��
Date: 03 Sep 2008 13:33:53 GMT
Organization: ��ˮ˼Դ
Message-ID: <4bhSWi$aqb@bbs.sjtu.edu.cn>
Mime-Version: 1.0
Content-Type: text/plain; charset="gb2312"
Content-Transfer-Encoding: 8bit
X-Filename: database/M.1220448812.A
Lines: 238