【sql语句中的符号】（通配符+#{}+${}）

女爷i 2022-09-05 12:37 209阅读 0赞

背景：别人写的代码，由于数据库添加了一个is\_delete（0：未删除；1：已删除）字段用来做假删除，大家都知道，如果说一条数据删除了，那么肯定是不能查出来的了，因此，我的任务就是修改由于添加一个字段而引发的灾难。

问题：

我们的sql:

select
        i.id,
        i.project_id,
        i.sets,
        i.name,
        i.length,
        i.likes,
        i.url,
        i.type,
        p.project_name
    from
        t_information as i, t_project as p
    where
        1=1
            and i.project_id=p.id
            and i.is_delete=0
        <if test="name!=null and name!=''">
            and i.name like ('%${name}%')
        </if>
        <if test="orderBy !=null and orderBy!=''">
            order by ${orderBy}
        </if>
        <bind name="pageNum" value="(pageNum-1)*pageSize"></bind>
        limit ${pageNum},${pageSize}

我们用了mybatis的动态sql中的if语句:

//如果name不为空，就在上面的sql语句后面拼接上and name
    <if test="name!=null and name!=''">
            and i.name like ('%${name}%')
    </if>

下面我们来说说sql语句中的通配符---

使用SQL 通配符可以替代一个或多个字符，即模糊查询。

SQL 通配符必须与 LIKE 运算符一起使用。在 SQL 中，可使用以下通配符如下（举例都是以表person为例）：

1.% 替代一个或多个字符

可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号(%% ) 表示

举例：

查找名字里有王的人：select \* from person where name like '%王%'

查找名字里第一个字是王的人：select \* from person where name like '王%'

查找名字里最后一个字是王的人：select \* from person where name like '%王'

2、\_ 仅替代一个字符,匹配单个任意字符，它常用来限制表达式的字符长度语句

举例：

查找名字第第一个字之后的字是静的人：select \* from person where name like '\_静%'

3、\[charlist\] 指定一个字符列(字符、字符串或范围)，要求所匹配对象为它们中的任一个。

举例：

查询姓氏拼音中包含‘a’或‘b’或‘c’的人：select \* from person where name like '\[abc\]%'

4、\[^charlist\]或者\[!charlist\] 不在字符列中的任何单一字符

举例：

查询姓氏拼音中不包含‘a’或‘b’或‘c’的人：select \* from person where name like '\[!abc\]%'

上面通配符的一些举例都是一些最基础的，有一些更复杂的需要我们到时候动动我们灵活的小脑袋瓜排列组合一下啦~~~

下面说一下sql语句中常用的\#\{\}和$\{\}：

\#\{\}表示占位符，可以有效防止sql注入，使用\#\{\}设置参数无需考虑参数类型

$\{\}表示拼接符，无法防止sql注入，使用\#\{\}设置参数需要考虑参数类型

\#\{\}将传入的数据都当成一个字符串，会对自动传入的数据加一个双引号。如：order by \#user\_id\#，如果传入的值是11,那么解析成sql时的值为order by “11”, 如果传入的值是id，则解析成的sql为order by “id”。

$\{\}将传入的数据直接显示生成在sql中。如：order by userid，如果传入的值是11,那么解析成sql时的值为order by 11, 如果传入的值是id，则解析成的sql为order by id。

一般能用\#的就别用$。

4.有些情况下必须使用使用\#\{\}(，就是当我们需要拼接的变量上不能带单引号时，就必须使用$\{\},其他情况都尽量使用\#\{\}的方式，因为$\{\}会有sql注入的问题)：

i.动态拼接排序字段

MyBatis排序时使用order by 动态参数时需要注意，用$而不是\#。

order by排序语句中，因为order by 后边必须跟字段名，这个字段名不能带引号，如果带引号会被识别会字符串，而不是字段。

默认情况下，使用\#\{\}格式的语法会导致MyBatis创建预处理语句属性并以它为背景设置安全的值（比如?）。这样做很安全，很迅速也是首选做法，有时你只是想直接在SQL语句中插入一个不改变的字符串如order by，可以这样使用：order by $\{columnName\};这里MyBatis不会修改或转义字符串。

ii.当sql中表名是从参数中取的情况

表名是从参数中获取的时候，由于表名不能加引号，因此不能用\#\{\}

我在项目中遇到的问题：

如上sql大家来找一找存在的问题，大家来找茬啦！！！

揭晓答案：

select
            i.id,
            i.project_id,
            i.sets,
            i.name,
            i.length,
            i.likes,
            i.url,
            i.type,
            p.project_name
         from
            t_information as i, t_project as p
        where
            1=1
                and i.project_id=p.id
            <if test="name!=null and name!=''">
                and i.name like ('%#{name}%')
            </if>
            <if test="orderBy !=null and orderBy!=''">
                order by ${orderBy}
            </if>
            <bind name="pageNum" value="(pageNum-1)*pageSize"></bind>
            limit #{pageNum},${pageSize}

这里用\#\{\}防止了sql注入

![20210817222043667.png][]

这里的第一个\#\{\}也是防止sql注入，但是第二个为什么不需要改成\#\{\}呢（按照idea提示进行的修改）？

![20210817222124512.png][]

还有一个问题，为什么按照现在的修改完成之后，发布之后该sql语句反而不能使用了呢？？？

望大神指导

![d09bfcd2a74008f4af9fe2b1bda24557.png][]

[20210817222043667.png]: /images/20220829/3cbf0d5c49424e5fbb11bd0930a2da58.png
[20210817222124512.png]: /images/20220829/30a42c4efde94db89f18f0359817daf2.png
[d09bfcd2a74008f4af9fe2b1bda24557.png]: /images/20220829/ad80ac3ebcbd4278afeab52b88262308.png