图片 1

mysql 存款和储蓄进程多规格查询
自身在做三个询问战绩的系统,准备在mysql里面创造三个囤积进程,参数举个例子:学号,姓名,战表,
当今作者要查战绩,倘若传入的参数是null则不协作,举个例子参数列表(null,姓名,战绩),那么查询语句便是select * from table where name = ‘姓名’ and score =
‘战表’,简单的话正是只要传入了参数,那么查询结果将在符合,不过自个儿未来径直用if
else语句发掘太难为了,这里多少个原则亟待2^3 = 8个if
else分支语句,若是条件越来越多的话正是成指数增加了,有未有怎样好一点的缓和放啊能够消除这么些主题素材呀

我们都明白使用存款和储蓄进程的补益当中有2点

三个粗略的积累进度:

  static void Main(string[] args)
        {

            string 表名 = "water_emstime";
            string sql = "exec GetTableSelect " + 表名;
            string ConString = "server=xxx.xxx.xx.xx;database=newFW;uid=sa;pwd=sa";
            SqlDataAdapter da = new SqlDataAdapter(sql, ConString);
            DataSet ds = new DataSet();
            da.Fill(ds);
            string ParmsList = "";
            foreach (DataRow item in ds.Tables[0].Rows)
            {
                ParmsList += item[0].ToString() + "\n";
            }
            string Select = "";
            Select += ds.Tables[1].Rows[0][0].ToString()+"\n";
            foreach (DataRow item in ds.Tables[2].Rows)
            {
                Select += item[0].ToString() + "\n";
            }
            //根据表生成的查询存储过程
            string procSelect = string.Format(@"create proc [dbo].[get_{0}]
                                (
                                    {1}
                                )
                                as
                                begin
                                    {2}
                                end", 表名, ParmsList, Select);//表名 、参数列表 、查询语句



                    // create proc [dbo].[oper_water_alarm]
                    //(
                    //    @Param_monitoring_mn varchar(32)=Null,
                    //    @Param_factor_id varchar(32)=Null,
                    //    @Param_datatime datetime=Null,
                    //    @Param_value numeric(9)=Null,
                    //    @Param_flag varchar(10)=Null,
                    //    @Param_action int
                    //)
                    //as
                    //begin
                    //    if(@Param_action=0)
                    //    begin
                            //    insert into water_alarm(monitoring_mn,
                            //factor_id,
                            //datatime,
                            //value,
                            //flag)
                            //        values (@Param_monitoring_mn,
                            //@Param_factor_id,
                            //@Param_datatime,
                            //@Param_value,
                            //@Param_flag);
                    //    end
                    //    else if(@Param_action=1)
                    //    begin
                    //        delete water_alarm
                    //        where monitoring_mn=@Param_monitoring_mn
                    //    end
                    //    else
                    //    begin
                    //        Update water_alarm set monitoring_mn=@Param_monitoring_mn,
                    //        factor_id=@Param_factor_id ,
                    //        datatime=@Param_datatime ,
                    //        value=@Param_value ,
                    //        flag=@Param_flag
                    //        where monitoring_mn=@Param_monitoring_mn
                    //    end
                    //end

        }

当点击查询后格外全体准则

1、参数查询安全(用参数就安然?)

Create procedure [dbo].[Proc_SeachJob] 
(@startRecordIndex int,
@endRecordIndex int,
@seach nvarchar(10),
@docount bit)
as
set nocount on
if(@docount=1)
--只需要把%与搜索的字符串用拼接的方式即可
select count(*) from Job where name like '%@seach%' or work_address like '%@seach%'
else
begin
declare @indextable table(id int identity(1,1),nid int)
set rowcount @endRecordIndex
insert into @indextable(nid) select ID from Job  where name like '%@seach%' or work_address like '%@seach%' order by CreateTime desc
select * from Job O,@indextable t where O.ID=t.nid
and t.id between @startRecordIndex and @endRecordIndex order by t.id
end
set nocount off

 

图片 2

2、存款和储蓄进程在成立时预编写翻译,执行成效比SQL语句要高

 

在进展多规格查询时方可

 

CREATE PROC Getcourse

听别人讲这两点,看看下边这段此前平日写的贮存进度吧(查询数据)

@Condition

CREATE PROCEDURE [dbo].[usp_TestExec]
    @Account VARCHAR(50)='',
    @UserName VARCHAR(50)=''
AS
BEGIN
    DECLARE @SQL VARCHAR(8000)
    SET @SQL='SELECT * FROM dbo.tb_Test WHERE 1=1';
    IF(ISNULL(@Account,'')<>'') SET @SQL=@SQL+' AND Account LIKE ''%'+@Account+'%'''
    IF(ISNULL(@UserName,'')<>'') SET @SQL=@SQL+' AND UserName LIKE ''%'+@UserName+'%'''

    EXEC(@SQL)

END    

AS

运作,测验没难点。

SELECT * FROM course

而是有哪个人会驰念到注入?

WHERE (c# LIKE’%’+@Condition+’%’ OR cname LIKE’%’+@Condition+’%’ OR t#
LIKE’%’+@Condition+’%’)

瞅着存款和储蓄进度本身感觉有多少个难点

1.实在依然运转SQL,假设条件不等同,生成的SQL语句不等同,预编写翻译运转成为美貌的假话

2.不能幸免SQL代码注入,不相信请(测)看(试)

  运行如下代码会出现何种结果?

  

 EXEC usp_TestExec "fdsgdsgsdd' OR '' LIKE '"

图片 3

 

出难题咯,作为一个有先进心的技术员,此时是还是不是视听一句话:该重写了

此地是本身的主意:

 

ALTER PROCEDURE [dbo].[usp_TestExec]
    @Account VARCHAR(50)='',
    @UserName VARCHAR(50)=''
AS
BEGIN
     --usp_TestExec "DB' OR '' LIKE '"
    --usp_TestExec 'DB'
    DECLARE @SQL VARCHAR(8000)
    SELECT * FROM dbo.tb_Test 
    WHERE ((@Account IS NULL AND Account IS NULL) OR @Account='' OR (Account LIKE '%'+@Account+'%')) 
        AND ((@UserName IS NULL AND UserName IS NULL) OR @UserName='' OR (UserName LIKE '%'+@UserName+'%')) 

END    

运维测量检验一下:

  

exec usp_TestExec "DB' OR '' LIKE '"

图片 4

exec usp_TestExec 'DB'

 

图片 5

 

流入难点一下子就解决了,并且由于尚未拼接SQL语句,能够说这么的蕴藏进度才预编写翻译后实行查询的。

 

应该还也可以有别的措施。

附上一段SqlCommandBuilder生成的Update语句,给看得懂的人看吗:

UPDATE [dbo].[tb_TT] SET [FRbigint] = @FRbigint
                    , [Fbinary] = @Fbinary
                    , [Fbit] = @Fbit
                    , [Fchar] = @Fchar
                    , [Fdate] = @Fdate
                    , [Fdatetime] = @Fdatetime
                    , [Fdatetime2] = @Fdatetime2
                    , [Fdatetimeoffset] = @Fdatetimeoffset
                    , [Fdecimal] = @Fdecimal
                    , [Ffloat] = @Ffloat
                    , [Fgeography] = @Fgeography
                    , [Fgeometry] = @Fgeometry
                    , [Fhierarchyid] = @Fhierarchyid
                    , [Fimage] = @Fimage
                    , [Fint] = @Fint
                    , [Fmoney] = @Fmoney
                    , [Fnchar] = @Fnchar
                    , [Fntext] = @Fntext
                    , [Fnumeric] = @Fnumeric
                    , [Fnvarchar] = @Fnvarchar
                    , [FnvarcharMax] = @FnvarcharMax
                    , [Freal] = @Freal
                    , [Fsmalldatetime] = @Fsmalldatetime
                    , [Fsmallint] = @Fsmallint
                    , [Fsmallmoney] = @Fsmallmoney
                    , [Fsql_variant] = @Fsql_variant
                    , [Ftext] = @Ftext
                    , [Ftime] = @Ftime
                    , [Ftinyint] = @Ftinyint
                    , [Funiqueidentifier] = @Funiqueidentifier
                    , [Fvarbinary] = @Fvarbinary
                    , [Fvarbinary_Max] = @Fvarbinary_Max
                    , [Fvarchar] = @Fvarchar
                    , [Fvarchar_Max] = @Fvarchar_Max
                    , [Fxml] = @Fxml 
WHERE     (
            ([isid] = @Original_isid) 
        AND ((@IsNull_FRbigint = 1 AND [FRbigint] IS NULL) OR ([FRbigint] = @Original_FRbigint)) 
        AND ((@IsNull_Fbinary = 1 AND [Fbinary] IS NULL) OR ([Fbinary] = @Original_Fbinary)) 
        AND ((@IsNull_Fbit = 1 AND [Fbit] IS NULL) OR ([Fbit] = @Original_Fbit)) 
        AND ((@IsNull_Fchar = 1 AND [Fchar] IS NULL) OR ([Fchar] = @Original_Fchar)) 
        AND ((@IsNull_Fdate = 1 AND [Fdate] IS NULL) OR ([Fdate] = @Original_Fdate)) 
        AND ((@IsNull_Fdatetime = 1 AND [Fdatetime] IS NULL) OR ([Fdatetime] = @Original_Fdatetime)) 
        AND ((@IsNull_Fdatetime2 = 1 AND [Fdatetime2] IS NULL) OR ([Fdatetime2] = @Original_Fdatetime2)) 
        AND ((@IsNull_Fdatetimeoffset = 1 AND [Fdatetimeoffset] IS NULL) OR ([Fdatetimeoffset] = @Original_Fdatetimeoffset)) 
        AND ((@IsNull_Fdecimal = 1 AND [Fdecimal] IS NULL) OR ([Fdecimal] = @Original_Fdecimal)) 
        AND ((@IsNull_Ffloat = 1 AND [Ffloat] IS NULL) OR ([Ffloat] = @Original_Ffloat)) 
        AND ((@IsNull_Fhierarchyid = 1 AND [Fhierarchyid] IS NULL) OR ([Fhierarchyid] = @Original_Fhierarchyid)) 
        AND ((@IsNull_Fint = 1 AND [Fint] IS NULL) OR ([Fint] = @Original_Fint)) 
        AND ((@IsNull_Fmoney = 1 AND [Fmoney] IS NULL) OR ([Fmoney] = @Original_Fmoney)) 
        AND ((@IsNull_Fnchar = 1 AND [Fnchar] IS NULL) OR ([Fnchar] = @Original_Fnchar)) 
        AND ((@IsNull_Fnumeric = 1 AND [Fnumeric] IS NULL) OR ([Fnumeric] = @Original_Fnumeric)) 
        AND ((@IsNull_Fnvarchar = 1 AND [Fnvarchar] IS NULL) OR ([Fnvarchar] = @Original_Fnvarchar)) 
        AND ((@IsNull_Freal = 1 AND [Freal] IS NULL) OR ([Freal] = @Original_Freal)) 
        AND ((@IsNull_Fsmalldatetime = 1 AND [Fsmalldatetime] IS NULL) OR ([Fsmalldatetime] = @Original_Fsmalldatetime)) 
        AND ((@IsNull_Fsmallint = 1 AND [Fsmallint] IS NULL) OR ([Fsmallint] = @Original_Fsmallint)) 
        AND ((@IsNull_Fsmallmoney = 1 AND [Fsmallmoney] IS NULL) OR ([Fsmallmoney] = @Original_Fsmallmoney)) 
        AND ((@IsNull_Fsql_variant = 1 AND [Fsql_variant] IS NULL) OR ([Fsql_variant] = @Original_Fsql_variant)) 
        AND ((@IsNull_Ftime = 1 AND [Ftime] IS NULL) OR ([Ftime] = @Original_Ftime)) 
        AND ((@IsNull_Ftinyint = 1 AND [Ftinyint] IS NULL) OR ([Ftinyint] = @Original_Ftinyint)) 
        AND ((@IsNull_Funiqueidentifier = 1 AND [Funiqueidentifier] IS NULL) OR ([Funiqueidentifier] = @Original_Funiqueidentifier)) 
        AND ((@IsNull_Fvarbinary = 1 AND [Fvarbinary] IS NULL) OR ([Fvarbinary] = @Original_Fvarbinary)) 
        AND ((@IsNull_Fvarchar = 1 AND [Fvarchar] IS NULL) OR ([Fvarchar] = @Original_Fvarchar)))

 

发表评论

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

网站地图xml地图