淺談為什么#{}可以防止SQL注入
#{} 和 ${} 的區(qū)別
#{} 匹配的是一個(gè)占位符,相當(dāng)于 JDBC 中的一個(gè)?,會(huì)對(duì)一些敏感字符進(jìn)行過(guò)濾,編譯過(guò)后會(huì)對(duì)傳遞的值加上雙引號(hào),因此可以防止 SQL 注入問(wèn)題。
${} 匹配的是真實(shí)傳遞的值,傳遞過(guò)后,會(huì)與 SQL 語(yǔ)句進(jìn)行字符串拼接。${} 會(huì)與其他 SQL 進(jìn)行字符串拼接,無(wú)法防止 SQL 注入問(wèn)題。
<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,因此不會(huì)產(chǎn)生 SQL 注入問(wèn)題
- #{} 不會(huì)產(chǎn)生字符串拼接,而 ${} 會(huì)產(chǎn)生字符串拼接
為什么能防止SQL注入?
以MySQL為例,#{} 使用的是 com.mysql.cj.ClientPreparedQueryBindings#setString 方法,在這里會(huì)對(duì)一些特殊字符進(jìn)行處理:
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 -- 經(jīng)過(guò)處理之后就變成了 '''%小%'' or 1=1 --'
而 ${} 只是簡(jiǎn)單的拼接字符串,不做其他處理。
這樣,它們就變成了:
-- %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 注入的風(fēng)險(xiǎn)。
到此這篇關(guān)于淺談為什么#{}可以防止SQL注入的文章就介紹到這了,更多相關(guān)#{}防止SQL注入內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
SQLServer 使用ADSI執(zhí)行分布式查詢ActiveDorectory對(duì)象
SQLServer 通過(guò)使用 ADSI 執(zhí)行分布式查詢ActiveDorectory對(duì)象的實(shí)現(xiàn)方法。2010-05-05
sql數(shù)據(jù)庫(kù)不能直接用instr函數(shù)
sql數(shù)據(jù)庫(kù)不能直接用instr函數(shù)...2007-01-01
SQL Server數(shù)據(jù)庫(kù)之備份和恢復(fù)數(shù)據(jù)庫(kù)
在一些對(duì)數(shù)據(jù)可靠性要求很高的行業(yè),若發(fā)生意外停機(jī)或數(shù)據(jù)丟失,其損失是十分慘重的,因此,本文詳細(xì)介紹了數(shù)據(jù)庫(kù)備份和恢復(fù)數(shù)據(jù)庫(kù)方法,感興趣的同學(xué)可以借鑒一下2023-03-03
一個(gè)SQL語(yǔ)句獲得某人參與的帖子及在該帖得分總和
一個(gè)SQL語(yǔ)句獲得某人參與的帖子及在該帖得分總和...2007-04-04

