MySQL数据库干货_29——SQL注入

SQL注入

什么是SQL注入

所谓 SQL 注入，就是通过把含有 SQL 语句片段的参数插入到需要执行的 SQL 语句中，最终达到欺骗数据库服务器执行恶意操作的 SQL 命令。

SQL注入案例

/**
 * SQL注入测试类
 */
public class SqlInjectTest {
  /**
   * 体现sql注入
   */
  public void sqlInject(String username,int userage){
    Connection connection =null;
    Statement statement =null;
    ResultSet resultSet =null;
    try{
      //获取连接
      connection = JdbcUtils.getConnection();
      //创建Statement对象
      statement = connection.createStatement();
      //定义sql语句
      String sql ="select * from users where username ='"+username+"' and userage = "+userage;
      System.out.println(sql);
      //执行sql语句
      resultSet = statement.executeQuery(sql);
      //处理结果集
      while(resultSet.next()){
        int userid = resultSet.getInt("userid");
        String name = resultSet.getString("username");
        int age = resultSet.getInt("userage");
        System.out.println(userid+" "+name+" "+age);


       }
     }catch(Exception e){
      e.printStackTrace();
     }finally{
      JdbcUtils.closeResource(resultSet,statement,connection);
     }
   }
  public static void main(String[] args) {
    SqlInjectTest sit = new SqlInjectTest();
    sit.sqlInject("java' or 1=1 -- ",28);
   }
}

解决SQL注入

  public void noSqlInject(String username,int userage){
      Connection connection = null;
      PreparedStatement ps =null;
      ResultSet resultSet = null;
      try{
        //获取连接
        connection = JdbcUtils.getConnection();
        //创建PreparedStatement对象
        ps = connection.prepareStatement("select * from users where username = ? and userage = ?");
        //绑定参数
        ps.setString(1,username);
        ps.setInt(2,userage);
        //执行sql
        resultSet = ps.executeQuery();


        //处理结果集
        while(resultSet.next()){
          int userid = resultSet.getInt("userid");
          String name = resultSet.getString("username");
          int age = resultSet.getInt("userage");
          System.out.println(userid+" "+name+" "+age);


        }
      }catch(Exception e){
        e.printStackTrace();
      }finally{
        JdbcUtils.closeResource(resultSet,ps,connection);
      }


   }

PreparedStatement操作数据库时是语句和参数分离的发送给数据库驱动进行编译的(这句话很关键，面试中很有可能会提及！大家自行理解)