【JDBC】PreparedStatement防止SQL注入的原理、MyBatis中#和$的区别

1. PreparedStatement防止SQL注入的原理

众所周知，JDBC中可以使用PreparedStatement防止sql注入，那么PreparedStatement是如何防止sql注入呢？让我们先来看一下源码：

   /**
     * Set a parameter to a Java String value. The driver converts this to a SQL
     * VARCHAR or LONGVARCHAR value (depending on the arguments size relative to
     * the driver's limits on VARCHARs) when it sends it to the database.
     * 
     * @param parameterIndex
     *            the first parameter is 1...
     * @param x
     *            the parameter value
     * 
     * @exception SQLException
     *                if a database access error occurs
     */
   public void setString(int parameterIndex, String x) throws SQLException {
        synchronized (checkClosed().getConnectionMutex()) {
            // if the passed string is null, then set this column to null
            if (x == null) {
                setNull(parameterIndex, Types.CHAR);
            } else {
                checkClosed();

                int stringLength = x.length();

                if (this.connection.isNoBackslashEscapesSet()) {
                    // Scan for any nasty chars

                    boolean needsHexEscape = isEscapeNeededForString(x, stringLength);

                    if (!needsHexEscape) {
                        byte[] parameterAsBytes = null;

                        StringBuilder quotedString = new StringBuilder(x.length() + 2);
                        quotedString.append('\'');
                        quotedString.append(x);
                        quotedString.append('\'');

                        if (!this.isLoadDataQuery) {
                            parameterAsBytes = StringUtils.getBytes(quotedString.toString(), this.charConverter, this.charEncoding,
                                    this.connection.getServerCharset(), this.connection.parserKnowsUnicode(), getExceptionInterceptor());
                        } else {
                            // Send with platform character encoding
                            parameterAsBytes = StringUtils.getBytes(quotedString.toString());
                        }

                        setInternal(parameterIndex, parameterAsBytes);
                    } else {
                        byte[] parameterAsBytes = null;

                        if (!this.isLoadDataQuery) {
                            parameterAsBytes = StringUtils.getBytes(x, this.charConverter, this.charEncoding, this.connection.getServerCharset(),
                                    this.connection.parserKnowsUnicode(), getExceptionInterceptor());
                        } else {
                            // Send with platform character encoding
                            parameterAsBytes = StringUtils.getBytes(x);
                        }

                        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.usingAnsiMode) {
                                    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('\\');
                                    }
                                }
                                // fall through

                            default:
                                buf.append(c);
                        }
                    }

                    buf.append('\'');

                    parameterAsString = buf.toString();
                }

                byte[] parameterAsBytes = null;

                if (!this.isLoadDataQuery) {
                    if (needsQuoted) {
                        parameterAsBytes = StringUtils.getBytesWrapped(parameterAsString, '\'', '\'', this.charConverter, this.charEncoding,
                                this.connection.getServerCharset(), this.connection.parserKnowsUnicode(), getExceptionInterceptor());
                    } else {
                        parameterAsBytes = StringUtils.getBytes(parameterAsString, this.charConverter, this.charEncoding, this.connection.getServerCharset(),
                                this.connection.parserKnowsUnicode(), getExceptionInterceptor());
                    }
                } else {
                    // Send with platform character encoding
                    parameterAsBytes = StringUtils.getBytes(parameterAsString);
                }

                setInternal(parameterIndex, parameterAsBytes);

                this.parameterTypes[parameterIndex - 1 + getParameterIndexOffset()] = Types.VARCHAR;
            }
        }
    }

显然，只有在使用setString方法的时候才有可能进行sql注入，通过对com.mysql.jdbc.PreparedStatement类中setString方法的分析，我们可以看到，setString方法中使用的是转义字符替换的方式防止sql注入的，首先在插入参数的时候在两边加入引号，然后对参数字符串中例如引号这样的特殊字符进行替换【’ -> \’】,以防止参数拼接sql语句。
— 会被替换的特殊字符：0 \n \r \\ " \032 \u00a5 \u20a9

---

2. MyBatis中#和$的区别

与PreparedStatement类似，
#可以防止Sql 注入，它会将所有传入的参数作为一个字符串来处理。
$ 则将传入的参数拼接到Sql上去执行，一般用于表名和字段名参数，$ 所对应的参数应该由服务器端提供，前端可以用参数进行选择，避免 Sql 注入的风险。

---

JDBC事务
SavePoint

---

参考文章：
Mybatis #和$区别以及原理：https://blog.csdn.net/zhangyong01245/article/details/90768156
PreparedStatement和Statement详细讲解：https://blog.csdn.net/czh500/article/details/88202971
对于SqlSessionTemplate的理解：https://www.jianshu.com/p/7cb4777a539e
MyBatis-Spring(二)–SqlSessionTemplate实现增删改查：