数据库结构检查 表名、表字段、触发器 检查 输出日志

/// <summary>
    /// 数据库安全（此类用于检测数据库稳定运行的安全监测）
    /// </summary>
    public class DatabaseSecurity
    {

        /// <summary>
        /// 结构检测
        /// </summary>
        public static void StructDetection()
        {
            //检测步骤
            //1.表总数检测 检测缺少什么表
            //2.检测每个表是否缺少那些字段
            var FeedbackTableNames = GlobalVariable.DatabaseFile.SelectMySQL("select * from information_schema.tables WHERE table_schema ='phi'; ");
            if (!FeedbackTableNames.status)
            {
                //检测失败
            }
            /*--------------------------------------------------------------------------------------------------------------------------------------------*/
            /*--------------------------------------------------------------------------------------------------------------------------------------------*/
            /*--------------------------------------------------------------------------------------------------------------------------------------------*/
            //数据库现在的表
            List<string> TableNames = FeedbackTableNames.result.AsEnumerable().Select<DataRow, string>(x => x["TABLE_NAME"].ToString()).ToList<string>();
            //官方发布的数据库表表
            List<string> TableNamesOld = Resources.DBSafe_TableNames.Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries).ToList();

#if DEBUG
            File.WriteAllLines(@"d:\\DBSafe_TableNames.txt", TableNames.ToArray());
#endif
            //检测表是否缺失
            List<string> TableNamesLost = TableNamesOld.Except(TableNames).ToList();
            if (TableNamesLost.Count > 0)
            {
                LogHelper.Instance.Error("缺失表" + string.Join(",", TableNamesLost.ToArray()));
                //System.Windows.MessageBox.Show("");
            }

            /*--------------------------------------------------------------------------------------------------------------------------------------------*/
            /*--------------------------------------------------------------------------------------------------------------------------------------------*/
            /*--------------------------------------------------------------------------------------------------------------------------------------------*/

            //对每一个表 结构名称进行遍历 检测表结构是否不匹配

            var FeedbackTableStructss = GlobalVariable.DatabaseFile.SelectMySQL("select * from information_schema.columns where table_schema = 'phi' ; ");
            if (!FeedbackTableStructss.status)
            {
                //检测失败
            }
            //数据库每个个表现在的结构
            List<Table_Struct> TableStructs = new List<Table_Struct>();
            TableStructs = FeedbackTableStructss.result.TableToList<Table_Struct>();
#if DEBUG
            File.WriteAllText(@"d:\\DBSafe_Structs.txt", JsonConvert.SerializeObject(TableStructs));
#endif
            //官方发布的数据库表结构
            List<Table_Struct> TableStructsOld = JsonConvert.DeserializeObject<List<Table_Struct>>(Resources.DBSafe_Structs);

            //检测表结构是否完整
            foreach (var itemTableName in TableNames)
            {

                var TableNow = TableStructs.Where(x => x.TABLE_NAME == itemTableName).ToList();
                var TableOld = TableStructsOld.Where(x => x.TABLE_NAME == itemTableName).ToList();
                //检测表结构是否完整
                List<Table_Struct> TableStructsLost = TableOld.Except(TableNow).ToList();
                if (TableStructsLost.Count > 0)
                {
                    LogHelper.Instance.Error(itemTableName + "_结构不一致，请检查以下字段_" + string.Join(",", TableStructsLost.Select(x => x.COLUMN_NAME).ToArray()));
                    LogHelper.Instance.Error(JsonConvert.SerializeObject(TableStructsLost));
                    //System.Windows.MessageBox.Show("");
                }
            }

            /*--------------------------------------------------------------------------------------------------------------------------------------------*/
            /*--------------------------------------------------------------------------------------------------------------------------------------------*/
            /*--------------------------------------------------------------------------------------------------------------------------------------------*/

            var FeedbackTableTriggers = GlobalVariable.DatabaseFile.SelectMySQL("SELECT * FROM information_schema.`TRIGGERS` WHERE TRIGGER_SCHEMA='phi';");
            if (!FeedbackTableTriggers.status)
            {
                //检测失败
            }
            //数据库每个个表现在的触发器
            List<Table_Trigger> TableTrigger = new List<Table_Trigger>();
            TableTrigger = FeedbackTableTriggers.result.TableToList<Table_Trigger>();
#if DEBUG
            File.WriteAllText(@"d:\\DBSafe_Triggers.txt", JsonConvert.SerializeObject(TableTrigger));
#endif
            //官方发布的数据库触发器
            List<Table_Trigger> TableTriggerOld = JsonConvert.DeserializeObject<List<Table_Trigger>>(Resources.DBSafe_Triggers);

            //检测触发器是否完整
            foreach (var itemTableName in TableNames)
            {
                var TableNow = TableTrigger.Where(x => x.EVENT_OBJECT_TABLE == itemTableName).ToList();
                var TableOld = TableTriggerOld.Where(x => x.EVENT_OBJECT_TABLE == itemTableName).ToList();
                //检测触发器构是否完整
                List<Table_Trigger> TableTriggersLost = TableOld.Except(TableNow).ToList();
                //多出的触发器
                List<Table_Trigger> TableTriggersMore = TableNow.Except(TableOld).ToList();
                foreach (var item in TableTriggersMore)
                {
                    var feedbackDrop = GlobalVariable.DatabaseFile.UpdateMySQL(item.GetMysqlDropTriggerString());
                    if (feedbackDrop.status)
                    {
                        LogHelper.Instance.Error("删除触发器_" + item.TRIGGER_NAME + JsonConvert.SerializeObject(item));
                    }
                }
                if (TableTriggersLost.Count > 0)
                {
                    LogHelper.Instance.Error(itemTableName + "_触发器不一致，请检查以下触发器_" + string.Join(",", TableTriggersLost.Select(x => x.TRIGGER_NAME).ToArray()));
                    LogHelper.Instance.Error(JsonConvert.SerializeObject(TableTriggersLost));
                    //System.Windows.MessageBox.Show("");
                }
            }

            //测试添加触发器
            //var test1 = TableTrigger.FirstOrDefault();
            //var feedback = GlobalVariable.DatabaseFile.InsertMySQL(test1.GetMysqlCreateTriggerString());
        }

        public class Table_Struct
        {
            public string TABLE_SCHEMA { get; set; }
            public string TABLE_NAME { get; set; }
            public string COLUMN_NAME { get; set; }
            public string DATA_TYPE { get; set; }
            public string IS_NULLABLE { get; set; }
            public string COLUMN_TYPE { get; set; }
            public string COLUMN_COMMENT { get; set; }

            public override bool Equals(object obj)
            {
                var temp = obj as Table_Struct;
                return this.TABLE_SCHEMA == temp.TABLE_SCHEMA && this.TABLE_NAME == temp.TABLE_NAME && this.COLUMN_NAME == temp.COLUMN_NAME && this.COLUMN_TYPE == temp.COLUMN_TYPE;
            }

            public override int GetHashCode()
            {
                return (this.TABLE_SCHEMA + this.TABLE_NAME + this.COLUMN_NAME + this.COLUMN_TYPE).GetHashCode();
            }
        }



        public class Table_Trigger
        {
            public string TRIGGER_SCHEMA { get; set; }
            public string EVENT_OBJECT_SCHEMA { get; set; }
            public string EVENT_OBJECT_TABLE { get; set; }
            public string TRIGGER_NAME { get; set; }
            public string EVENT_MANIPULATION { get; set; }
            public string ACTION_TIMING { get; set; }
            public string ACTION_STATEMENT { get; set; }

            /// <summary>
            /// 删除触发器
            /// </summary>
            /// <returns></returns>
            public string GetMysqlDropTriggerString()
            {
                string mysqlStr = string.Format("DROP TRIGGER IF EXISTS `{0}`; DELIMITER ; ; ", this.TRIGGER_NAME);
                return mysqlStr;
            }
            /// <summary>
            /// 创建更新触发器
            /// </summary>
            /// <returns></returns>
            public string GetMysqlCreateTriggerString()
            {
                string mysqlStr = string.Format("DROP TRIGGER IF EXISTS `{0}`; DELIMITER ; ; create trigger {0} {1} {2} on {3} for each row {4}",
                    this.TRIGGER_NAME,
                    this.ACTION_TIMING,
                    this.EVENT_MANIPULATION,
                    this.EVENT_OBJECT_TABLE,
                    this.ACTION_STATEMENT.Replace("\r\n", "\n").Replace("\"", "\'"));
                return mysqlStr;
            }


            public override bool Equals(object obj)
            {
                var temp = obj as Table_Trigger;
                var paraments = typeof(Table_Trigger).GetProperties();
                var result = paraments.Where(item => item.GetValue(this).GetHashCode() != item.GetValue(obj).GetHashCode()).ToList();
                if (result == null)
                {
                    return true;
                }
                return result.Count() <= 0;
            }

            public override int GetHashCode()
            {
                var paraments = typeof(Table_Trigger).GetProperties();
                var hash = paraments.Select(item => item.GetValue(this).GetHashCode()).ToArray();
                return (string.Join("", hash)).GetHashCode();
            }


        }

    }