SQL拼接方法

常见的SQL拼接

id =3;
"select * from orders where employeeid="+id;

这样存在的问题是相当明显的就是SQL注入，如果需要参数化那在编写代码的时候就相对多了些工作。

自动参数化处理

id=3;
SQL sql="select * from orders where empoyeeid=@id";
sql = sql +id;

更多实际应用效果

 string city = "sdf";
            SQL sql = "select * from orders where employeeid=@i";
            sql = sql + 3;
            Output(sql);
            sql = "select * from order where employeeid in(@p1,@p2)";
            sql = sql + 3 + 4;
            Output(sql);
            sql = "select * from orders where 1=1";
            if (city != null)
                sql = sql+" and city=@p1" + city;
            Output(sql);

最终处理参数化的结果是：

SQL:select * from orders where employeeid=@i
    Name:@i=3
-------------------------------------------
SQL:select * from order where employeeid in(@p1,@p2)
    Name:@p1=3
    Name:@p2=4
-------------------------------------------
SQL:select * from orders where 1=1 and city=@p1
    Name:@p1=sdf
-------------------------------------------

 参数化Like查询

dynamicSqlParam.Add("FrequencyBarCode", $"%{model.FrequencyBarCode}%" );
queryItems.Add($" FrequencyBarCode like @FrequencyBarCode");

" AND [DefineName] LIKE '%'+@DefineName+'%' "

 STUFF 将一列拼接成一行，逗号分隔

 

此随笔或为自己所写、或为转载于网络。仅用于个人收集及备忘。