拍拍尘土

http://blog.vckbase.com/wangjun/archive/2005/03/16/3645.html

SQL 注入攻击是比较常见的方式：
例如这么一张表格： CREATE TABLE [users] (
        [userid] [varchar] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
        [password] [varchar] (8) COLLATE Chinese_PRC_CI_AS NOT NULL ,
) ON [PRIMARY]
GO
假如代码是这样的：
<%
userid = TRIM(Request("uid"))

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "PROVIDER=SQLOLEDB;UID=test;PWD=test;DATABASE=test"

Set RS = Server.CreateObject("ADODB.Recordset")
RS.Open "SELECT * FROM users WHERE userid='"&userid&"'", Conn

while not RS.EOF
        Response.Write RS("userid")&","&RS("password")&"<br>"
        RS.Movenext
wend

RS.Close
Conn.Close

Set RS = nothing
Set Conn = nothing
%>
那么通过
http://127.0.0.1:81/test.asp?uid=guest&#39; or '1'='1这种方式调用将显示users表里所有用户。

防范


ASP版本


(1) 过滤所有非法字符
例如：字符串过滤"'","--",";" 等字符，数字型变量使用CLng, CInt 进行一次强制转换。

(2)用Command对象
例如：
<%
userid = TRIM(Request("uid"))

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "PROVIDER=SQLOLEDB;UID=test;PWD=test;DATABASE=test"

Set Cmd = Server.CreateObject("ADODB.Command")

Cmd.ActiveConnection = Conn
Cmd.CommandType = adCmdText
Cmd.CommandText = "SELECT * FROM users WHERE userid = ?"
Cmd.Parameters.Refresh
Cmd("Param1") = userid

Set RS = Cmd.Execute

while not RS.EOF
        Response.Write RS("userid")&","&RS("password")&"<br>"
        RS.Movenext
wend

RS.Close
Conn.Close

Set RS = nothing
Set Cmd = nothing
Set Conn = nothing
%>
但对于类似包含子查询的复杂语句，如：

Cmd.CommandText = "SELECT * FROM users WHERE userid=? OR userid IN (SELECT userid FROM customer WHERE username=?)"
Cmd.Parameters.Refresh
Cmd("Param1") = userid
Cmd("Param2") = username
这种方式执行时会显示如下错误信息：

无法从带子选择的查询 SQL 语句中获得参数信息。在准备命令前，请设置参数信息。

需要采用如下方式：
Cmd.CommandText = "SELECT * FROM users WHERE userid=? OR userid IN (SELECT userid FROM customer WHERE username=?)"
Cmd.Parameters.Append(Cmd.CreateParameter("userid", adVarChar, adParamInput, 16, userid))
Cmd.Parameters.Append(Cmd.CreateParameter("username", adVarChar, adParamInput, 16, username))

(3) 采用存储过程
例如，建立存储过程GetUsers：
CREATE PROCEDURE GetUsers
(
        @userid varchar(16),
        @username varchar(16)
)

AS

SET NOCOUNT ON

/**//* 选择返回的记录集 */

SELECT * FROM users WHERE userid=@userid
                   OR userid IN (SELECT userid FROM userdetails WHERE username=@username)

GO
调用代码如下：
<%
userid = TRIM(Request("uid"))
username = TRIM(Request("username"))

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "PROVIDER=SQLOLEDB;UID=test;PWD=test;DATABASE=test"

Set Cmd = Server.CreateObject("ADODB.Command")

Cmd.ActiveConnection = Conn
Cmd.CommandType = adCmdStoredProc
Cmd.CommandText = "GetUsers"
Cmd.Parameters.Append(Cmd.CreateParameter("userid", adVarChar, adParamInput, 16, userid))
Cmd.Parameters.Append(Cmd.CreateParameter("username", adVarChar, adParamInput, 16, username))

Set RS = Cmd.Execute

while not RS.EOF
        Response.Write RS("userid")&","&RS("password")&"<br>"
        RS.Movenext
wend

RS.Close
Conn.Close

Set RS = nothing
Set Cmd = nothing
Set Conn = nothing
%>

ASP.NET版本


SqlConnection Conn = new SqlConnection("SERVER=127.0.0.1;UID=sa;PWD=123456;DATABASE=mydb");
SqlCommand Cmd = new SqlCommand();

try
{
        Conn.Open();
        Cmd.Connection = Conn;
        Cmd.CommandType = CommandType.Text;
        Cmd.CommandText = "SELECT * FROM users WHERE userid = @userid";

        Cmd.Parameters.Add("@userid", SqlDbType.VarChar, 16).Value = "vckbase";

        SqlDataReader myReader;
        myReader = Cmd.ExecuteReader();
       
        if(myReader.Read())
        {
               Console.Write(myReader["username"].ToString());
        }
        else
               Console.Write("读取失败");

        myReader.Close();
}
catch(SqlException SqlEx)
{
}

finally
{
        Conn.Close();
}


posted on 2005-03-16 22:40 王骏