【数据库】JDBC课设（1） statement和preparedstament实例以及SQL注入

JDK14 MySQL8.0

需要在IDEA放进去MySQL的JAR包（Connector/j）

这个新项目是普通Java项目 我的jar包是从Java Web里面的maven复制过来的

maven非常香 不用自己找 写个dependence就下好了

具体maven使用见：b站狂神javaweb的课中讲过

 

 

 

 复制过来还需要激活他

在IDEA点击jar包右键 add to library

然后点击ok就可以使用啦

 

1.连接、断开数据库

这里主要考察那几步

{
    public static void main(String[] args) {
        ResultSet rs = null;
        Statement stmt = null;
        Connection conn = null;

        try {
            //1.注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.建立连接
            conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8&useSSL=FALSE" ,"root","");
            //3.处理结果集
            stmt = conn.createStatement();
            rs = stmt.executeQuery("select * from tablename1");

            while (rs.next()) {
                int age = rs.getInt("age");

// 输出查到的记录的各个字段的值
                System.out.println( " " + age);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                //4.关闭资源
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
                if (stmt != null) {
                    stmt.close();
                    stmt = null;
                }
                if (conn != null) {
                    conn.close();
                    conn = null;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

 

 

2。预编译语句（preparedStatement）

都说预编译防止SQL注入 那么我们先来看一下什么是SQL注入：

SQL注入，简单来说，就是，你的网页的用户在使用，比如论坛的留言板，电商网站的评论页面，提交内容的时候，可以使用'1 or 1'，诸如此类的，非法的字符，然后你的后台，如果在插入评论数据到表中的时候，如果使用Statement，就会原封不动的 将用户填写的内容拼接在SQL中，此时可能会发生对数据库的意外的损坏，甚至数据泄露。

 

我们对于SQL注入来举个例子；

我在注册用户的时候密码叫做 991024' OR 1=1

这样我们在查询的时候还好万一在delete的时候

sql=delete employee where name='cckong' and password = '991024' OR 1=1 '

因为1=1是恒真的表达式 也就是说你前面cckong用户名 指定的都是没用的

这个会导致你的整个employee表中数据被删掉（注意哦 我说的是表中数据 删表要用drop）

导致非常可怕的后果。

 

而preparedStarement的作用是将单引号全部转义了

下面来看一下pstm的源码 会发现在setString方法中都符号被加了\

所以不会出现那种情况啦

public synchronized void setString(int parameterIndex, String x) throws SQLException {
        // 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;
 
                    StringBuffer quotedString = new StringBuffer(x.length() + 2);
                    quotedString.append('\'');
                    quotedString.append(x);
                    quotedString.append('\'');
                    
                    if (!this.isLoadDataQuery) {
                        parameterAsBytes = StringUtils.getBytes(quotedString.toString(),
                                this.charConverter, this.charEncoding,
                                this.connection.getServerCharacterEncoding(),
                                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.getServerCharacterEncoding(),
                                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
                
                StringBuffer buf = new StringBuffer((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(charsetEncoder != null) {
                            CharBuffer cbuf = CharBuffer.allocate(1);
                            ByteBuffer bbuf = ByteBuffer.allocate(1); 
                            cbuf.put(c);
                            cbuf.position(0);
                            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
                                .getServerCharacterEncoding(), this.connection
                                .parserKnowsUnicode(), getExceptionInterceptor());
                } else {
                    parameterAsBytes = StringUtils.getBytes(parameterAsString,
                            this.charConverter, this.charEncoding, this.connection
                                    .getServerCharacterEncoding(), this.connection
                                    .parserKnowsUnicode(), getExceptionInterceptor());
                }
            } else {
                // Send with platform character encoding
                parameterAsBytes = StringUtils.getBytes(parameterAsString);
            }
 
            setInternal(parameterIndex, parameterAsBytes);
            
            this.parameterTypes[parameterIndex - 1 + getParameterIndexOffset()] = Types.VARCHAR;
        }
    }

 

 

我们来看一下调用pstm的例子

{
    public static void main(String[] args) {
        ResultSet rs = null;
        Statement stmt = null;
        Connection conn = null;
        PreparedStatement pstm=null;

        try {
            //1.注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.建立连接
            conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8&useSSL=FALSE" ,"root","");
            //3.处理结果集
//            stmt = conn.createStatement();
//            rs = stmt.executeQuery("select * from tablename1");
//
//
//            while (rs.next()) {
//                int age = rs.getInt("age");
//
//// 输出查到的记录的各个字段的值
//                System.out.println( " " + age);
//            }



            //预编译语句
            String sql="select * from tablename1 where age = ?";
            pstm=conn.prepareStatement(sql);
            pstm.setInt(1,21);
            rs=pstm.executeQuery();
            while (rs.next()) {
                int age = rs.getInt("age");

// 输出查到的记录的各个字段的值
                System.out.println( " " + age);
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                //4.关闭资源
                if (rs != null) {
                    rs.close();
                    rs = null;
                }
                if (stmt != null) {
                    stmt.close();
                    stmt = null;
                }
                if (conn != null) {
                    conn.close();
                    conn = null;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}