Пример #1
0
        /// <summary>
        /// Sql数据库兼容和Sql注入处理
        /// </summary>
        public static string Compatible(object where, DataBaseType dalType, bool isFilterInjection)
        {
            string text = GetIFieldSql(where);

            if (isFilterInjection)
            {
                text = SqlInjection.Filter(text, dalType);
            }
            text = SqlCompatible.Format(text, dalType);

            return(RemoveWhereOneEqualsOne(text));
        }
Пример #2
0
        /// <summary>
        /// 返回不包括Where条件的字符串
        /// </summary>
        /// <returns>结果如:Update tableName set Name=@Name,Value=@Value</returns>
        internal string GetUpdateSql(object whereObj)
        {
            isCanDo = false;
            StringBuilder _TempSql = new StringBuilder();

            _TempSql.Append("Update " + TableName + " set ");
            if (!string.IsNullOrEmpty(updateExpression))
            {
                _TempSql.Append(SqlCompatible.Format(updateExpression, _action.DalType) + ",");
                updateExpression = null;//取完值后清除值。
                isCanDo          = true;
            }
            string editTime = GetEditTimeSql();//内部判断该字段没有值才会更新。

            if (!string.IsNullOrEmpty(editTime))
            {
                _TempSql.Append(editTime);//自带尾,号
            }
            MDataCell cell = null;

            for (int i = 0; i < _action.Data.Count; i++)
            {
                cell = _action.Data[i];
                if (cell.Struct.IsPrimaryKey || cell.Struct.IsAutoIncrement)
                {
                    continue;//跳过自增或主键列。
                }

                if (cell.cellValue.State > 1 && (cell.Struct.IsCanNull || !cell.IsNull))
                {
                    if (cell.Struct.SqlType == SqlDbType.Timestamp && (_action.DalType == DalType.MsSql || _action.DalType == DalType.Sybase))
                    {
                        //更新时间戳不允许更新。
                        continue;
                    }
                    object value  = cell.Value;
                    DbType dbType = DataType.GetDbType(cell.Struct.SqlType.ToString(), _action.DalType);
                    if (_action.DalType == DalType.Oracle && dbType == DbType.String && cell.strValue == "" && !cell.Struct.IsCanNull)
                    {
                        value = " ";//Oracle not null 字段,不允许设置空值。
                    }

                    _action.dalHelper.AddParameters(_action.dalHelper.Pre + cell.ColumnName, value, dbType, cell.Struct.MaxSize, ParameterDirection.Input);
                    _TempSql.Append(SqlFormat.Keyword(cell.ColumnName, _action.DalType) + "=" + _action.dalHelper.Pre + cell.ColumnName + ",");
                    isCanDo = true;
                }
            }
            if (!isCanDo)
            {
                _action.dalHelper.debugInfo.Append(AppConst.HR + "Tip : Can not find the data can be updated!");
            }
            //switch (_action.dalHelper.dalType)
            //{
            //    case DalType.Oracle:
            //    case DalType.SQLite:
            //        _TempSql = _TempSql.Replace("[", "").Replace("]", "");
            //        break;
            //    case DalType.MySql:
            //        _TempSql = _TempSql.Replace("[", "`").Replace("]", "`");
            //        break;
            //}
            _TempSql = _TempSql.Remove(_TempSql.Length - 1, 1);
            _TempSql.Append(" where " + FormatWhere(whereObj));
            return(_TempSql.ToString());
        }
Пример #3
0
        /// <summary>
        /// 将各数据库默认值格式化成标准值,将标准值还原成各数据库默认值
        /// </summary>
        /// <param name="flag">[0:转成标准值],[1:转成各数据库值],[2:转成各数据库值并补充字符串前后缀]</param>
        /// <param name="sqlDbType">该列的值</param>
        /// <returns></returns>
        public static string FormatDefaultValue(DataBaseType dalType, object value, int flag, SqlDbType sqlDbType)
        {
            string defaultValue = Convert.ToString(value).Trim().TrimEnd('\n');//oracle会自带\n结尾

            if (dalType != DataBaseType.Access)
            {
                defaultValue = defaultValue.Replace("GenGUID()", string.Empty);
            }
            if (defaultValue.Length == 0)
            {
                return(null);
            }
            int groupID = DataType.GetGroup(sqlDbType);

            if (flag == 0)
            {
                #region 转标准值


                if (groupID == 2)//日期的标准值
                {
                    return(SqlValue.GetDate);
                }
                else if (groupID == 4)
                {
                    return(SqlValue.Guid);
                }
                switch (dalType)
                {
                case DataBaseType.MySql:    //用转\' \",所以不用替换。
                    defaultValue = defaultValue.Replace("\\\"", "\"").Replace("\\\'", "\'");
                    break;

                case DataBaseType.Access:
                case DataBaseType.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);
                }
                #endregion
            }
            else
            {
                if (defaultValue == SqlValue.Guid)
                {
                    switch (dalType)
                    {
                    case DataBaseType.MsSql:
                    case DataBaseType.Oracle:
                    case DataBaseType.Sybase:
                    case DataBaseType.PostgreSQL:
                        return(SqlCompatible.FormatGUID(defaultValue, dalType));

                    default:
                        return("");
                    }
                }
            }
            switch (dalType)
            {
            case DataBaseType.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 DataBaseType.MsSql:
            case DataBaseType.Sybase:
                if (flag == 0)
                {
                    if (defaultValue.StartsWith("(") && defaultValue.EndsWith(")"))    //避免 (newid()) 被去掉()
                    {
                        defaultValue = defaultValue.Substring(1, defaultValue.Length - 2);
                    }
                    if (defaultValue.StartsWith("N'"))
                    {
                        defaultValue = defaultValue.TrimStart('N');
                    }
                    defaultValue = defaultValue.Trim('\'');    //'(', ')',
                }
                else
                {
                    defaultValue = defaultValue.Replace(SqlValue.GetDate, "getdate()").Replace("'", "''");
                    if (groupID == 0)
                    {
                        defaultValue = "(N'" + defaultValue + "')";
                    }
                }
                break;

            case DataBaseType.Oracle:
                if (flag == 0)
                {
                    defaultValue = defaultValue.Trim('\'');
                }
                else
                {
                    defaultValue = defaultValue.Replace(SqlValue.GetDate, "sysdate").Replace("'", "''");
                    if (groupID == 0)
                    {
                        defaultValue = "'" + defaultValue + "'";
                    }
                }
                break;

            case DataBaseType.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 DataBaseType.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;

            case DataBaseType.PostgreSQL:
                if (flag == 0)
                {
                    defaultValue = defaultValue.Trim('"');
                    if (groupID == 0)
                    {
                        defaultValue = Regex.Split(defaultValue, "::", RegexOptions.IgnoreCase)[0];
                    }
                    if (groupID > 0)    //兼容一些不规范的写法。像数字型的加了引号 '0'
                    {
                        defaultValue = defaultValue.Trim('\'');
                    }
                }
                else
                {
                    defaultValue = defaultValue.Replace(SqlValue.GetDate, "now()").Replace("\"", "\"\"");
                    if (groupID == 0)
                    {
                        defaultValue = Regex.Split(defaultValue, "::", RegexOptions.IgnoreCase)[0];
                        defaultValue = "'" + defaultValue.Trim('\'') + "'";
                    }
                    else if (groupID == 3)     // bool
                    {
                        defaultValue = defaultValue.Replace("1", "true").Replace("0", "false");
                    }
                }
                break;
            }
            if (flag == 0)
            {
                return(defaultValue.Replace("≮", "\"").Replace("≯", "'"));
            }
            return(defaultValue);
        }
Пример #4
0
        /// <summary>
        /// 返回不包括Where条件的字符串
        /// </summary>
        /// <returns>结果如:Update tableName set Name=@Name,Value=@Value</returns>
        internal string GetUpdateSql(object whereObj)
        {
            isCanDo = false;
            StringBuilder _TempSql = new StringBuilder();

            _TempSql.Append("Update " + SqlFormat.Keyword(TableName, _action.dalHelper.DataBaseType) + " set ");
            if (!string.IsNullOrEmpty(updateExpression))
            {
                _TempSql.Append(SqlCompatible.Format(updateExpression, _action.DataBaseType) + ",");
                updateExpression = null;//取完值后清除值。
                isCanDo          = true;
            }
            string editTime = GetEditTimeSql();//内部判断该字段没有值才会更新。

            if (!string.IsNullOrEmpty(editTime))
            {
                _TempSql.Append(editTime);//自带尾,号
            }
            MDataCell cell = null;

            for (int i = 0; i < _action.Data.Count; i++)
            {
                cell = _action.Data[i];
                if (cell.Struct.IsPrimaryKey || cell.Struct.IsAutoIncrement)
                {
                    continue;//跳过自增或主键列。
                }

                if (cell.State > 1 && (cell.Struct.IsCanNull || !cell.IsNull))
                {
                    if (cell.Struct.SqlType == SqlDbType.Timestamp && (_action.DataBaseType == DataBaseType.MsSql || _action.DataBaseType == DataBaseType.Sybase))
                    {
                        //更新时间戳不允许更新。
                        continue;
                    }
                    if (_action.DataBaseType == DataBaseType.MsSql && cell.Struct.SqlTypeName != null && cell.Struct.SqlTypeName.EndsWith("hierarchyId"))
                    {
                        _TempSql.Append(SqlFormat.Keyword(cell.ColumnName, _action.DataBaseType) + "=HierarchyID::Parse('" + cell.StringValue + "')" + ",");
                    }
                    else
                    {
                        if (_action.DataBaseType == DataBaseType.FoxPro)
                        {
                            string value = "\"" + cell.StringValue + "\",";
                            if (cell.Struct.SqlType == SqlDbType.DateTime)
                            {
                                value = "{^" + cell.StringValue + "},";
                            }
                            //不支持参数化
                            _TempSql.Append(SqlFormat.Keyword(cell.ColumnName, _action.DataBaseType) + "=" + value);
                        }
                        else
                        {
                            object value  = cell.Value;
                            DbType dbType = DataType.GetDbType(cell.Struct.SqlType.ToString(), _action.DataBaseType);
                            if (dbType == DbType.String && cell.StringValue == "")
                            {
                                if (_action.DataBaseType == DataBaseType.Oracle && !cell.Struct.IsCanNull)
                                {
                                    value = " ";//Oracle not null 字段,不允许设置空值。
                                }
                                if (_action.DataBaseType == DataBaseType.MySql && cell.Struct.MaxSize == 36)
                                {
                                    value = DBNull.Value;//MySql 的char36 会当成guid处理,不能为空,只能为null。
                                }
                            }
                            _action.dalHelper.AddParameters(_action.dalHelper.Pre + cell.ColumnName, value, dbType, cell.Struct.MaxSize, ParameterDirection.Input);
                            _TempSql.Append(SqlFormat.Keyword(cell.ColumnName, _action.DataBaseType) + "=" + _action.dalHelper.Pre + cell.ColumnName + ",");
                        }
                    }
                    isCanDo = true;
                }
            }
            if (!isCanDo)
            {
                string err = AppConst.HR + "warn : " + TableName + " can't find the data can be updated!";
                Log.Write(err, LogType.Warn);
                _action.dalHelper.DebugInfo.Append(err);
            }
            //switch (_action.dalHelper.dalType)
            //{
            //    case DalType.Oracle:
            //    case DalType.SQLite:
            //        _TempSql = _TempSql.Replace("[", "").Replace("]", "");
            //        break;
            //    case DalType.MySql:
            //        _TempSql = _TempSql.Replace("[", "`").Replace("]", "`");
            //        break;
            //}
            _TempSql = _TempSql.Remove(_TempSql.Length - 1, 1);
            _TempSql.Append(" where " + FormatWhere(whereObj));
            return(_TempSql.ToString());
        }