#{}防止SQL注入
今天的阿洋依旧很菜 人气:0#{} 和 ${} 的区别
#{}
匹配的是一个占位符,相当于 JDBC 中的一个?,会对一些敏感字符进行过滤,编译过后会对传递的值加上双引号,因此可以防止 SQL 注入问题。
${}
匹配的是真实传递的值,传递过后,会与 SQL 语句进行字符串拼接。${} 会与其他 SQL 进行字符串拼接,无法防止 SQL 注入问题。
<mapper namespace="com.gitee.shiayanga.mybatis.wildcard.dao.UserDao"> <select id="findByUsername" resultType="com.gitee.shiayanga.mybatis.wildcard.entity.User" parameterType="string"> select * from user where username like #{userName} </select> <select id="findByUsername2" resultType="com.gitee.shiayanga.mybatis.wildcard.entity.User" parameterType="string"> select * from user where username like '%${userName}%' </select> </mapper>
==> Preparing: select * from user where username like ? ==> Parameters: '%小%' or 1=1 --(String) <== Total: 0 ==> Preparing: select * from user where username like '%aaa' or 1=1 -- %' ==> Parameters: <== Total: 4
#{} 底层是如何防止 SQL 注入的?
- #{} 底层采用的是 PreparedStatement,因此不会产生 SQL 注入问题
- #{} 不会产生字符串拼接,而 ${} 会产生字符串拼接
为什么能防止SQL注入?
以MySQL为例,#{} 使用的是 com.mysql.cj.ClientPreparedQueryBindings#setString 方法,在这里会对一些特殊字符进行处理:
public void setString(int parameterIndex, String x) { if (x == null) { setNull(parameterIndex); } else { int stringLength = x.length(); if (this.session.getServerSession().isNoBackslashEscapesSet()) { // Scan for any nasty chars boolean needsHexEscape = isEscapeNeededForString(x, stringLength); if (!needsHexEscape) { StringBuilder quotedString = new StringBuilder(x.length() + 2); quotedString.append('\''); quotedString.append(x); quotedString.append('\''); byte[] parameterAsBytes = this.isLoadDataQuery ? StringUtils.getBytes(quotedString.toString()) : StringUtils.getBytes(quotedString.toString(), this.charEncoding); setValue(parameterIndex, parameterAsBytes, MysqlType.VARCHAR); } else { byte[] parameterAsBytes = this.isLoadDataQuery ? StringUtils.getBytes(x) : StringUtils.getBytes(x, this.charEncoding); setBytes(parameterIndex, parameterAsBytes); } return; } String parameterAsString = x; boolean needsQuoted = true; if (this.isLoadDataQuery || isEscapeNeededForString(x, stringLength)) { needsQuoted = false; // saves an allocation later StringBuilder buf = new StringBuilder((int) (x.length() * 1.1)); buf.append('\''); // // Note: buf.append(char) is _faster_ than appending in blocks, because the block append requires a System.arraycopy().... go figure... // for (int i = 0; i < stringLength; ++i) { char c = x.charAt(i); switch (c) { case 0: /* Must be escaped for 'mysql' */ buf.append('\\'); buf.append('0'); break; case '\n': /* Must be escaped for logs */ buf.append('\\'); buf.append('n'); break; case '\r': buf.append('\\'); buf.append('r'); break; case '\\': buf.append('\\'); buf.append('\\'); break; case '\'': buf.append('\''); buf.append('\''); break; case '"': /* Better safe than sorry */ if (this.session.getServerSession().useAnsiQuotedIdentifiers()) { buf.append('\\'); } buf.append('"'); break; case '\032': /* This gives problems on Win32 */ buf.append('\\'); buf.append('Z'); break; case '\u00a5': case '\u20a9': // escape characters interpreted as backslash by mysql if (this.charsetEncoder != null) { CharBuffer cbuf = CharBuffer.allocate(1); ByteBuffer bbuf = ByteBuffer.allocate(1); cbuf.put(c); cbuf.position(0); this.charsetEncoder.encode(cbuf, bbuf, true); if (bbuf.get(0) == '\\') { buf.append('\\'); } } buf.append(c); break; default: buf.append(c); } } buf.append('\''); parameterAsString = buf.toString(); } byte[] parameterAsBytes = this.isLoadDataQuery ? StringUtils.getBytes(parameterAsString) : (needsQuoted ? StringUtils.getBytesWrapped(parameterAsString, '\'', '\'', this.charEncoding) : StringUtils.getBytes(parameterAsString, this.charEncoding)); setValue(parameterIndex, parameterAsBytes, MysqlType.VARCHAR); } }
所以 '%小%' or 1=1 --
经过处理之后就变成了 '''%小%'' or 1=1 --'
而 ${} 只是简单的拼接字符串,不做其他处理。
这样,它们就变成了:
-- %aaa' or 1=1 -- select * from user where username like '%aaa' or 1=1 -- %' -- '%小%' or 1=1 -- select * from user where username like '''%小%'' or 1=1 --'
所以就避免了 SQL 注入的风险。
加载全部内容