kreta/Kreta.Core/Logic/SqlLogic.cs

using System;
using System.Collections.Generic;
using System.Text.RegularExpressions;
using Kreta.Core.Enum;
using Kreta.Core.Exceptions;

namespace Kreta.Core.Logic
{
    /// <summary>
    /// Author: Kovács Kornél (DevKornél) Created On: 2019.09.
    /// </summary>
    public static class SqlLogic
    {
        public static object ParseListToParameter<T>(ICollection<T> list)
        {
            object response;
            if (list == null)
            {
                response = DBNull.Value;
            }
            else
            {
                if (list.Count < 1)
                {
                    throw new BlException(BlExceptionType.ListaNemTartalmazElemet);
                }
                response = CleanSqlParameter(string.Join(Constants.General.VesszoSeparator, list));
            }
            return response;
        }

        /// <summary>
        /// A paraméterben kapott collection elemeit egy sql-template-be menti bele, például: "...NOT IN ({0})"
        /// </summary>
        public static string ParseListIntoSqlQuery<T>(string sqlTemplate, ICollection<T> list)
        {
            string response;
            if (list == null)
            {
                return sqlTemplate;
            }

            if (list.Count < 1)
            {
                throw new BlException(BlExceptionType.ListaNemTartalmazElemet);
            }
            response = CleanSqlParameter(string.Join(Constants.General.VesszoSeparator, list));
            return string.Format(sqlTemplate, response);
        }

        public static string CleanSqlParameter(string parameter)
        {
            string[] blacklistCharacters = new string[] { "--", ";", "/*", "*/", "@@", "@" };
            string[] blacklistWords = new string[] {
                "CHAR", "NCHAR", "VARCHAR", "NVARCHAR", "ALTER", "BEGIN", "CAST",
                "CREATE", "CURSOR", "DECLARE", "DELETE", "DROP", "END", "EXEC",
                "EXECUTE", "FETCH", "INSERT", "KILL", "OPEN", "SELECT", "SYS",
                "SYSOBJECTS", "SYSCOLUMNS", "TABLE", "UPDATE"
            };

            string result = parameter;

            foreach (var blacklistItem in blacklistCharacters)
            {
                result = result.Replace(blacklistItem, "");
            }
            foreach (var blacklistItem in blacklistWords)
            {
                result = Regex.Replace(result, blacklistItem, "", RegexOptions.IgnoreCase);
            }

            return result.Trim();
        }
    }
}