SQL注入问题

SQL注入

　　SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中

事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗

数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。 

 

正常输入情况下：

public class SqlInjection {
    public static void main(String[] args) {
        login("lisi","123456");
    }
    public static void login(String username,String password){
        Connection conn = null;
        Statement st= null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            String sql = "select * from users where `NAME`='"+username+"' AND `password`='"+password+"'";
            rs = st.executeQuery(sql);
            while (rs.next()){
                System.out.println("name="+rs.getString("NAME"));
                System.out.println("password="+rs.getString("PASSWORD"));
                System.out.println("+++++++++++++++++++++++");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

非正常情况下：

public class SqlInjection {
    public static void main(String[] args) {
        login("'or '1=1","'or '1=1");
    }
    public static void login(String username,String password){
        Connection conn = null;
        Statement st= null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            String sql = "select * from users where `NAME`='"+username+"' AND `password`='"+password+"'";
            rs = st.executeQuery(sql);
            while (rs.next()){
                System.out.println("name="+rs.getString("NAME"));
                System.out.println("password="+rs.getString("PASSWORD"));
                System.out.println("+++++++++++++++++++++++");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

区别就在于登录的时候输入的用户名密码

 拼接一下sql得到

select * from users where `name` = 'lisi' and `password`='123456';

select * from users where `name`=' 'or ' 1=1' and `password`=' ' or '1=1';(恒成立，name,password中的1=1永远为true)

 

PreparedStatement对象

　　PreparedStatement可以防止SQL注入，效率更高

1.新增

public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            //开始有区别了,使用问号占位符代替参数
            String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,birthday) values(?,?,?,?,?)";
            pst = conn.prepareStatement(sql);//需要一个预编译SQL，不执行
            //手动给参数赋值
            pst.setInt(1,4);//id赋值
            pst.setString(2,"wuwu");//NAME赋值
            pst.setString(3,"33232");//password赋值
            pst.setString(4,"232412@qq.com");//email赋值
            pst.setDate(5,new java.sql.Date(new Date().getTime()));//birthday赋值
            //最后执行
            int i = pst.executeUpdate();
            if (i>0){
                System.out.println("插入成功！！！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,pst,rs);
        }
    }
}

2.删除

public class TestDelete {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "delete from users where id = ?";
            pst = conn.prepareStatement(sql);
            pst.setInt(1,4);
            int i = pst.executeUpdate();
            if (i>0){
                System.out.println("删除成功！！！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,pst,rs);
        }
    }
}

3.更新

public class TestUpdate {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs= null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "update users set `NAME`=? where id=?";
            pst = conn.prepareStatement(sql);
            pst.setString(1,"lisi");
            pst.setInt(2,1);
            int i = pst.executeUpdate();
            if (i>0){
                System.out.println("更新成功！！！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,pst,rs);
        }
    }
}

4.查询

public class TestSelect {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "select * from users where `NAME`=?";
            pst = conn.prepareStatement(sql);
            pst.setString(1,"lisi");
            rs = pst.executeQuery();
            while (rs.next()){
                System.out.println("id="+rs.getObject("id"));
                System.out.println("name="+rs.getObject("NAME"));
                System.out.println("password="+rs.getObject("PASSWORD"));
                System.out.println("email="+rs.getObject("email"));
                System.out.println("birthday="+rs.getObject("birthday"));
                System.out.println("+++++++++++++++++++++++");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,pst,rs);
        }
    }
}