package cn.code.jdbc; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import org.junit.Test; /** * PreparedStatement * 他是statement接口的子接口 * 强大之处: * 1、防sql攻击 * 2、提高代码的可读性、可维护性 * 3、提高效率 * PreparedStatement用法: * */ public class NumberFive { /** * 防sql攻击 * @throws ClassNotFoundException * */ //登录校验,查看用户名密码是否正确 public boolean findByUser(String username,String password) throws ClassNotFoundException{ String url="jdbc:mysql://localhost:3306/mydb1"; String driverclassname="com.mysql.jdbc.Driver"; String mysqlusername = "root"; String mysqlpassword ="123"; Connection con =null; PreparedStatement ps =null; ResultSet rs=null; Class.forName(driverclassname); try{ con= DriverManager.getConnection(url, mysqlusername, mysqlpassword); //给出sql模板 String sql="select * from t_user where username=? and password=?"; //获取preparedStatement,并将sql模板传给它 ps = con.prepareStatement(sql); //为参数赋值 ps.setString(1, username); ps.setString(2, password); //执行sql,因为已经把sql语句给了preparedStatement,所以不用再给; rs = ps.executeQuery(); return rs.next();//根据查询结果,返回下一行是否有数据,如果有数据证明用户名密码正确; }catch(SQLException e){ throw new RuntimeException(e); } finally{ if(rs!=null) try { rs.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } if(ps!=null) try { ps.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } if(con!=null) try { con.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } @Test public void login() throws ClassNotFoundException{ // String username="lisi"; // String password="123"; String username="a' or 'a'='a"; String password="a' or 'a'='a"; // Boolean b = findByUser(username, password); Boolean b = findByUser2(username, password); System.out.println(b); } //没有解决sql攻击的方法findByUser2 public boolean findByUser2(String username,String password) throws ClassNotFoundException{ String url="jdbc:mysql://localhost:3306/mydb1"; String driverclassname="com.mysql.jdbc.Driver"; String mysqlusername = "root"; String mysqlpassword ="123"; Connection con =null; Statement s =null; ResultSet rs=null; Class.forName(driverclassname); try{ con= DriverManager.getConnection(url, mysqlusername, mysqlpassword); String sql="select * from t_user where username='"+username+"' and password='"+password+"'"; System.out.println(sql); s = con.createStatement(); rs = s.executeQuery(sql); return rs.next(); }catch(SQLException e){ throw new RuntimeException(e); } finally{ if(rs!=null) try { rs.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } if(s!=null) try { s.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } if(con!=null) try { con.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } }