SQL注入,PreparedStatement和Statement

在SQL中包含特殊字符或SQL的关键字(如：' or 1 or ')时Statement将出现不可预料的结果（出现异常或查询的结果不正确），可用PreparedStatement来解决。

PreperedStatement（从Statement扩展而来）相对Statement的优点：

       1.没有SQL注入的问题。

       2.Statement会使数据库频繁编译SQL，可能造成数据库缓冲区溢出。

       3.数据库和驱动可以对PreperedStatement进行优化（只有在相关联的数据库连接没有关

闭的情况下有效）。

 

public void read(String name) throws SQLException {

              Connection conn = null;

              PreparedStatement ps = null;

              ResultSet rs = null;

              try {

                     // 2.建立连接

                     conn = JdbcUtils.getConnection();

 

                     // conn = JdbcUtilsSing.getInstance().getConnection();

                     // 3.创建语句

                     String sql = "select id, name, money, birthday  from user where name=?";

                     ps = conn.prepareStatement(sql);

                     ps.setString(1, name);

                     // 4.执行语句

                     rs = ps.executeQuery();

 

                     // 5.处理结果

                     while (rs.next()) {

                            System.out.println(rs.getInt("id") + "\t"

                                          + rs.getString("name") + "\t" + rs.getDate("birthday")

                                          + "\t" + rs.getFloat("money"));

                     }

 

              } finally {

                     JdbcUtils.free(rs, ps, conn);

              }

       }

 

       public void read1(String name) throws SQLException {

              Connection conn = null;

              Statement st = null;

              ResultSet rs = null;

              try {

                     // 2.建立连接

                     conn = JdbcUtils.getConnection();

                     // conn = JdbcUtilsSing.getInstance().getConnection();

 

                     // 3.创建语句

                     String sql = "select id, name, money, birthday  from user where name='"

                                   + name + "'";

                     st = conn.createStatement();

                     // 4.执行语句

                     rs = st.executeQuery(sql);

 

                     // 5.处理结果

                     while (rs.next()) {

                            // System.out.println(rs.getObject("id") + "\t"

                            // + rs.getObject("name") + "\t"

                            // + rs.getObject("birthday") + "\t"

                            // + rs.getObject("money"));

                     }

              } finally {

                     JdbcUtils.free(rs, st, conn);

              }

       }

对于read1()方法，若传入的name是' or 1 or '则会把数据库里所有记录都查询出来。而read()用的PerparedStatement就不会出显这种情况。