444 lines
16 KiB
C#
444 lines
16 KiB
C#
|
||
using CYQ.Data.Extension;
|
||
using CYQ.Data.Table;
|
||
using System;
|
||
using System.Text.RegularExpressions;
|
||
using System.Collections.Generic;
|
||
using System.Data;
|
||
|
||
namespace CYQ.Data.SQL
|
||
{
|
||
/// <summary>
|
||
/// Sql 语句格式化类 (类似助手工具)
|
||
/// </summary>
|
||
internal class SqlFormat
|
||
{
|
||
/// <summary>
|
||
/// Sql关键字处理
|
||
/// </summary>
|
||
public static string Keyword(string name, DalType dalType)
|
||
{
|
||
if (!string.IsNullOrEmpty(name))
|
||
{
|
||
name = name.Trim();
|
||
if (name.IndexOfAny(new char[] { ' ', '[', ']', '`', '"', '(', ')' }) == -1)
|
||
{
|
||
string pre = null;
|
||
int i = name.LastIndexOf('.');// 增加跨库支持（demo.dbo.users）
|
||
if (i > 0)
|
||
{
|
||
string[] items = name.Split('.');
|
||
pre = items[0];
|
||
name = items[items.Length - 1];
|
||
}
|
||
switch (dalType)
|
||
{
|
||
case DalType.Access:
|
||
return "[" + name + "]";
|
||
case DalType.MsSql:
|
||
case DalType.Sybase:
|
||
return (pre == null ? "" : pre + "..") + "[" + name + "]";
|
||
case DalType.MySql:
|
||
return (pre == null ? "" : pre + ".") + "`" + name + "`";
|
||
case DalType.SQLite:
|
||
case DalType.PostgreSQL:
|
||
return "\"" + name + "\"";
|
||
case DalType.Txt:
|
||
case DalType.Xml:
|
||
return NotKeyword(name);
|
||
}
|
||
}
|
||
}
|
||
return name;
|
||
}
|
||
/// <summary>
|
||
/// 去除关键字符号
|
||
/// </summary>
|
||
/// <param name="name"></param>
|
||
/// <returns></returns>
|
||
public static string NotKeyword(string name)
|
||
{
|
||
name = name.Trim();
|
||
if (name.IndexOfAny(new char[] { '(', ')' }) == -1 && name.Split(' ').Length == 1)
|
||
{
|
||
//string pre = string.Empty;
|
||
int i = name.LastIndexOf('.');// 增加跨库支持（demo.dbo.users）
|
||
if (i > 0)
|
||
{
|
||
// pre = name.Substring(0, i + 1);
|
||
name = name.Substring(i + 1);
|
||
}
|
||
name = name.Trim('[', ']', '`', '"');
|
||
|
||
}
|
||
return name;
|
||
}
|
||
/// <summary>
|
||
/// Sql数据库兼容和Sql注入处理
|
||
/// </summary>
|
||
public static string Compatible(object where, DalType dalType, bool isFilterInjection)
|
||
{
|
||
string text = GetIFieldSql(where);
|
||
if (isFilterInjection)
|
||
{
|
||
text = SqlInjection.Filter(text, dalType);
|
||
}
|
||
text = SqlCompatible.Format(text, dalType);
|
||
|
||
return RemoveWhereOneEqualsOne(text);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 移除"where 1=1"
|
||
/// </summary>
|
||
internal static string RemoveWhereOneEqualsOne(string sql)
|
||
{
|
||
try
|
||
{
|
||
sql = sql.Trim();
|
||
if (sql == "where 1=1")
|
||
{
|
||
return string.Empty;
|
||
}
|
||
if (sql.EndsWith(" and 1=1"))
|
||
{
|
||
return sql.Substring(0, sql.Length - 8);
|
||
}
|
||
int i = sql.IndexOf("where 1=1", StringComparison.OrdinalIgnoreCase);
|
||
//do
|
||
//{
|
||
if (i > 0)
|
||
{
|
||
if (i == sql.Length - 9)//以where 1=1 结束。
|
||
{
|
||
sql = sql.Substring(0, sql.Length - 10);
|
||
}
|
||
else if (sql.Substring(i + 10, 8).ToLower() == "order by")
|
||
{
|
||
sql = sql.Remove(i, 10);//可能有多个。
|
||
}
|
||
// i = sql.IndexOf("where 1=1", StringComparison.OrdinalIgnoreCase);
|
||
}
|
||
//}
|
||
//while (i > 0);
|
||
}
|
||
catch
|
||
{
|
||
|
||
}
|
||
|
||
return sql;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 创建补充1=2的SQL语句
|
||
/// </summary>
|
||
/// <param name="tableName">表名、或视图语句</param>
|
||
/// <returns></returns>
|
||
internal static string BuildSqlWithWhereOneEqualsTow(string tableName)
|
||
{
|
||
tableName = tableName.Trim();
|
||
if (tableName[0] == '(' && tableName.IndexOf(')') > -1)
|
||
{
|
||
int end = tableName.LastIndexOf(')');
|
||
string sql = tableName.Substring(1, end - 1);//.Replace("\r\n", "\n").Replace('\n', ' '); 保留注释的换行。
|
||
string[] keys = new string[] { " where ", "\nwhere ", "\nwhere\r", "\nwhere\n" };
|
||
foreach (string key in keys)
|
||
{
|
||
if (sql.IndexOf(key, StringComparison.OrdinalIgnoreCase) > -1)
|
||
{
|
||
return Regex.Replace(sql, key, " where 1=2 and ", RegexOptions.IgnoreCase);
|
||
}
|
||
}
|
||
//检测是否有group by
|
||
keys = new string[] { " group by", "\ngroup by" };
|
||
foreach (string key in keys)
|
||
{
|
||
if (sql.IndexOf(key, StringComparison.OrdinalIgnoreCase) > -1)
|
||
{
|
||
string newKey = key.Replace("group", "where 1=2 group");
|
||
return Regex.Replace(sql, key, newKey, RegexOptions.IgnoreCase);
|
||
}
|
||
}
|
||
return sql + " where 1=2";
|
||
}
|
||
return string.Format("select * from {0} where 1=2", tableName);
|
||
}
|
||
|
||
/// <summary>
|
||
/// Mysql Bit 类型不允许条件带引号 （字段='0' 不可以）
|
||
/// </summary>
|
||
/// <param name="where"></param>
|
||
/// <param name="mdc"></param>
|
||
/// <returns></returns>
|
||
internal static string FormatMySqlBit(string where, MDataColumn mdc)
|
||
{
|
||
if (where.Contains("'0'"))
|
||
{
|
||
foreach (MCellStruct item in mdc)
|
||
{
|
||
int groupID = DataType.GetGroup(item.SqlType);
|
||
if (groupID == 1 || groupID == 3)//视图模式里取到的bit是bigint,所以数字一并处理
|
||
{
|
||
if (where.IndexOf(item.ColumnName, StringComparison.OrdinalIgnoreCase) > -1)
|
||
{
|
||
string pattern = @"\s?" + item.ColumnName + @"\s*=\s*'0'";
|
||
where = Regex.Replace(where, pattern, " " + item.ColumnName + "=0", RegexOptions.IgnoreCase);
|
||
}
|
||
}
|
||
}
|
||
}
|
||
return where;
|
||
}
|
||
internal static string FormatOracleDateTime(string where, MDataColumn mdc)
|
||
{
|
||
if (where.IndexOf(':') > -1 && where.IndexOfAny(new char[] { '>', '<', '-', '/' }) > -1)//判断是否存在日期的判断
|
||
{
|
||
|
||
foreach (MCellStruct item in mdc)
|
||
{
|
||
if (DataType.GetGroup(item.SqlType) == 2 && where.IndexOf(item.ColumnName, StringComparison.OrdinalIgnoreCase) > -1)
|
||
{
|
||
string pattern = @"(\s?" + item.ColumnName + @"\s*[><]{1}[=]?\s*)('.{19,23}')";
|
||
Regex reg = new Regex(pattern, RegexOptions.IgnoreCase);
|
||
if (reg.IsMatch(where))
|
||
{
|
||
where = reg.Replace(where, delegate(Match match)
|
||
{
|
||
if (item.SqlType == SqlDbType.Timestamp)
|
||
{
|
||
return match.Groups[1].Value + "to_timestamp(" + match.Groups[2].Value + ",'yyyy-MM-dd HH24:MI:ss.ff')";
|
||
}
|
||
else
|
||
{
|
||
return match.Groups[1].Value + "to_date(" + match.Groups[2].Value + ",'yyyy-mm-dd hh24:mi:ss')";
|
||
}
|
||
});
|
||
}
|
||
|
||
}
|
||
}
|
||
}
|
||
return where;
|
||
}
|
||
internal static List<string> GetTableNamesFromSql(string sql)
|
||
{
|
||
List<string> nameList = new List<string>();
|
||
|
||
//获取原始表名
|
||
string[] items = sql.Split(new char[] { ' ', ';', '(', ')', ',' });
|
||
if (items.Length == 1) { return nameList; }//单表名
|
||
if (items.Length > 3) // 总是包含空格的select * from xxx
|
||
{
|
||
bool isKeywork = false;
|
||
foreach (string item in items)
|
||
{
|
||
if (!string.IsNullOrEmpty(item))
|
||
{
|
||
string lowerItem = item.ToLower();
|
||
switch (lowerItem)
|
||
{
|
||
case "from":
|
||
case "update":
|
||
case "into":
|
||
case "join":
|
||
case "table":
|
||
isKeywork = true;
|
||
break;
|
||
default:
|
||
if (isKeywork)
|
||
{
|
||
if (item[0] == '(' || item.IndexOf('.') > -1) { isKeywork = false; }
|
||
else
|
||
{
|
||
isKeywork = false;
|
||
nameList.Add(NotKeyword(item));
|
||
}
|
||
}
|
||
break;
|
||
}
|
||
}
|
||
}
|
||
}
|
||
return nameList;
|
||
}
|
||
|
||
#region IField处理
|
||
|
||
/// <summary>
|
||
/// 静态的对IField接口处理
|
||
/// </summary>
|
||
public static string GetIFieldSql(object whereObj)
|
||
{
|
||
if (whereObj is IField)
|
||
{
|
||
IField filed = whereObj as IField;
|
||
string where = filed.Sql;
|
||
filed.Sql = "";
|
||
return where;
|
||
}
|
||
return Convert.ToString(whereObj);
|
||
}
|
||
#endregion
|
||
|
||
/// <summary>
|
||
/// 将各数据库默认值格式化成标准值，将标准值还原成各数据库默认值
|
||
/// </summary>
|
||
/// <param name="flag">[0:转成标准值],[1:转成各数据库值],[2:转成各数据库值并补充字符串前后缀]</param>
|
||
/// <param name="sqlDbType">该列的值</param>
|
||
/// <returns></returns>
|
||
public static string FormatDefaultValue(DalType dalType, object value, int flag, SqlDbType sqlDbType)
|
||
{
|
||
string defaultValue = Convert.ToString(value).Trim().TrimEnd('\n');//oracle会自带\n结尾
|
||
if (dalType != DalType.Access)
|
||
{
|
||
defaultValue = defaultValue.Replace("GenGUID()", string.Empty);
|
||
}
|
||
if (defaultValue.Length == 0)
|
||
{
|
||
return null;
|
||
}
|
||
int groupID = DataType.GetGroup(sqlDbType);
|
||
if (flag == 0)
|
||
{
|
||
if (groupID == 2)//日期的标准值
|
||
{
|
||
return SqlValue.GetDate;
|
||
}
|
||
else if (groupID == 4)
|
||
{
|
||
return SqlValue.Guid;
|
||
}
|
||
switch (dalType)
|
||
{
|
||
case DalType.MySql://用转\' \"，所以不用替换。
|
||
defaultValue = defaultValue.Replace("\\\"", "\"").Replace("\\\'", "\'");
|
||
break;
|
||
case DalType.Access:
|
||
case DalType.SQLite:
|
||
defaultValue = defaultValue.Replace("\"\"", "≮");
|
||
break;
|
||
default:
|
||
defaultValue = defaultValue.Replace("''", "≯");
|
||
break;
|
||
}
|
||
switch (defaultValue.ToLower().Trim('(', ')'))
|
||
{
|
||
case "newid":
|
||
case "guid":
|
||
case "sys_guid":
|
||
case "genguid":
|
||
case "uuid":
|
||
return SqlValue.Guid;
|
||
}
|
||
}
|
||
else
|
||
{
|
||
if (defaultValue == SqlValue.Guid)
|
||
{
|
||
switch (dalType)
|
||
{
|
||
case DalType.MsSql:
|
||
case DalType.Oracle:
|
||
case DalType.Sybase:
|
||
return SqlCompatible.FormatGUID(defaultValue, dalType);
|
||
default:
|
||
return "";
|
||
}
|
||
|
||
}
|
||
}
|
||
switch (dalType)
|
||
{
|
||
case DalType.Access:
|
||
if (flag == 0)
|
||
{
|
||
if (defaultValue[0] == '"' && defaultValue[defaultValue.Length - 1] == '"')
|
||
{
|
||
defaultValue = defaultValue.Substring(1, defaultValue.Length - 2);
|
||
}
|
||
}
|
||
else
|
||
{
|
||
defaultValue = defaultValue.Replace(SqlValue.GetDate, "Now()").Replace("\"", "\"\"");
|
||
if (groupID == 0)
|
||
{
|
||
defaultValue = "\"" + defaultValue + "\"";
|
||
}
|
||
}
|
||
break;
|
||
case DalType.MsSql:
|
||
case DalType.Sybase:
|
||
if (flag == 0)
|
||
{
|
||
if (defaultValue.StartsWith("(") && defaultValue.EndsWith(")"))//避免 (newid()) 被去掉()
|
||
{
|
||
defaultValue = defaultValue.Substring(1, defaultValue.Length - 2);
|
||
}
|
||
defaultValue = defaultValue.Trim('N', '\'');//'(', ')',
|
||
}
|
||
else
|
||
{
|
||
defaultValue = defaultValue.Replace(SqlValue.GetDate, "getdate()").Replace("'", "''");
|
||
if (groupID == 0)
|
||
{
|
||
defaultValue = "(N'" + defaultValue + "')";
|
||
}
|
||
}
|
||
break;
|
||
case DalType.Oracle:
|
||
if (flag == 0)
|
||
{
|
||
defaultValue = defaultValue.Trim('\'');
|
||
}
|
||
else
|
||
{
|
||
defaultValue = defaultValue.Replace(SqlValue.GetDate, "sysdate").Replace("'", "''");
|
||
if (groupID == 0)
|
||
{
|
||
defaultValue = "'" + defaultValue + "'";
|
||
}
|
||
}
|
||
break;
|
||
case DalType.MySql:
|
||
if (flag == 0)
|
||
{
|
||
defaultValue = defaultValue.Replace("b'0", "0").Replace("b'1", "1").Trim(' ', '\'');
|
||
}
|
||
else
|
||
{
|
||
defaultValue = defaultValue.Replace(SqlValue.GetDate, "CURRENT_TIMESTAMP").Replace("'", "\\'").Replace("\"", "\\\"");
|
||
if (groupID == 0)
|
||
{
|
||
defaultValue = "\"" + defaultValue + "\"";
|
||
}
|
||
}
|
||
break;
|
||
case DalType.SQLite:
|
||
if (flag == 0)
|
||
{
|
||
defaultValue = defaultValue.Trim('"');
|
||
if (groupID > 0)//兼容一些不规范的写法。像数字型的加了引号 '0'
|
||
{
|
||
defaultValue = defaultValue.Trim('\'');
|
||
}
|
||
}
|
||
else
|
||
{
|
||
defaultValue = defaultValue.Replace(SqlValue.GetDate, "CURRENT_TIMESTAMP").Replace("\"", "\"\"");
|
||
if (groupID == 0)
|
||
{
|
||
defaultValue = "\"" + defaultValue + "\"";
|
||
}
|
||
}
|
||
break;
|
||
}
|
||
if (flag == 0)
|
||
{
|
||
return defaultValue.Replace("≮", "\"").Replace("≯", "'");
|
||
}
|
||
return defaultValue;
|
||
}
|
||
}
|
||
}
|