Demo_JDBC_实现一个用户登陆的功能并改进sql的注入问题

小灰灰 2022-06-09 04:10 155阅读 0赞

图解如何使用JDBC实现一个用户登陆的功能

![实现一个用户登录的功能][SouthEast]

**1、新建一个Java Project项目**

**2、新建一个User类对应MySQL中的users表**  
![这里写图片描述][SouthEast 1]

package com.soar.entity;
    
    import java.util.Date;
    
    public class User {
        
        private int id;
        private String name;
        private String password;
        private String email;
        private Date birthday;
        public int getId() {
            return id;
        }
        public void setId(int id) {
            this.id = id;
        }
        public String getName() {
            return name;
        }
        public void setName(String name) {
            this.name = name;
        }
        public String getPassword() {
            return password;
        }
        public void setPassword(String password) {
            this.password = password;
        }
        public String getEmail() {
            return email;
        }
        public void setEmail(String email) {
            this.email = email;
        }
        public Date getBirthday() {
            return birthday;
        }
        public void setBirthday(Date birthday) {
            this.birthday = birthday;
        }
        @Override
        public String toString() {
            return "User [id=" + id + ", name=" + name + ", password=" + password + ", email=" + email + ", birthday="
                    + birthday + "]";
        }
    
    
    }

**3、新建一个DBUtils类**

package com.soar.util;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.ResourceBundle;
    
    
    public class DBUtils {
        
    
        private static String driverClass;
        private static String url;
        private static String user;
        private static String password;
    
        static{
            ResourceBundle rb = ResourceBundle.getBundle("dbinfo");
            //给上面四个变量赋值
            driverClass = rb.getString("driverClass");
            url = rb.getString("url");
            user = rb.getString("user");
            password = rb.getString("password");
    
            try {
                Class.forName(driverClass);
            } catch (Exception e) {
    
                e.printStackTrace();
            }
        }
    
        //得到连接
        public static Connection getConnection() throws Exception{
            return DriverManager.getConnection(url, user, password);
        }
    
        //关闭资源
        public static void closeAll(ResultSet rs,Statement stmt,Connection conn){
            if(rs!=null){
                try {
                    rs.close();
                } catch (SQLException e) {
    
                    e.printStackTrace();
                }
                rs = null;
            }
    
            if(stmt!=null){
                try {
                    stmt.close();
                } catch (SQLException e) {
    
                    e.printStackTrace();
                }
                stmt = null;
            }
    
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
    
                    e.printStackTrace();
                }
                conn = null;
            }
    
        }
    }

**4、创建一个properties的配置文件和DBUtils类进行关联**  
![这里写图片描述][SouthEast 2]  
**注意：不用加分号**

**5、创建一个DoLogin类**

package com.soar.service;
    
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.Statement;
    
    import com.soar.entity.User;
    import com.soar.util.DBUtils;
    
    public class DoLogin {
        
    
        /**
         * 根据用户名和密码查询用户对象信息
         * @param name
         * @param pwd
         * @return u
         */
    
        public User findUser(String name, String pwd){
            Connection conn = null;
            Statement stmt = null;
            ResultSet rs = null;
    
            User u = null;
            try {
                conn = DBUtils.getConnection(); //得到连接对象
                stmt = conn.createStatement();  //得到执行sql语句的对象
                rs = stmt.executeQuery("SELECT * FROM users WHERE NAME='"+name+"' AND PASSWORD='"+pwd+"'"); //执行sql语句
                if(rs.next()){
                    u = new User();
                    u.setId(rs.getInt(1));
                    u.setName(rs.getString(2));
                    u.setPassword(rs.getString(3));
                    u.setEmail(rs.getString(4));
                    u.setBirthday(rs.getDate(5));
                }
            } catch (Exception e) {
    
                e.printStackTrace();
            }finally{
                DBUtils.closeAll(rs, stmt, conn);
            }
    
            return u;
        }
    }

注意事项：在调用executeQuery()方法时，括号中的sql语句应该在SQLyog中写完后复制到MyEclipse中，因为如果在ME中写sql语句即使写错了，编译器也不会报错。

**6、创建一个Login类**

package com.soar.client;
    
    import java.util.Scanner;
    
    import com.soar.entity.User;
    import com.soar.service.DoLogin;
    
    public class Login {
        public static void main(String[] args) {
            Scanner input = new Scanner(System.in);
    
            System.out.println("请输入用户名：");
            String name = input.nextLine();
            System.out.println("请输入密码：");
            String pwd = input.nextLine();
    
            DoLogin dl = new DoLogin();
            User user = dl.findUser(name, pwd); //调用查询用户的方法
    
            if(user!=null){
                System.out.println("欢迎你："+user.getName());
            }else{
                System.out.println("用户名和密码错误！");
            }
        }
    }

**7、运行Login类**

MySQL中的数据表  
![这里写图片描述][SouthEast 3]

Console中输入正确的信息  
![这里写图片描述][SouthEast 4]

Console中输入错误的信息  
![这里写图片描述][SouthEast 5]

**8、在 DoLogin类中程序代码不完善，存在sql注入问题**  
当任意输入一个用户名后，在输入密码时填写如下语句会把  
所有的数据库信息都调出来

请输入用户名：
    sdaf
    请输入密码：
    fdsa ' or '1'='1

![这里写图片描述][SouthEast 6]

![这里写图片描述][SouthEast 7]

*解决方法：使用**preparedStatement**来代替**Statement***

preparedStatement：预编译对象， 是Statement对象的子类。  
特点：  
性能要高  
会把sql语句先编译  
sql语句中的参数会发生变化，过滤掉用户输入的关键字。

![这里写图片描述][SouthEast 8]

![这里写图片描述][SouthEast 9]

改进后的DoLogin类代码：

package com.soar.service;
    
    import java.sql.Connection;
    import java.sql.ResultSet;
    
    import com.mysql.jdbc.PreparedStatement;
    import com.soar.entity.User;
    import com.soar.util.DBUtils;
    
    public class DoLogin {
        
    
        /**
         * 根据用户名和密码查询用户对象信息
         * @param name
         * @param pwd
         * @return u
         */
    
        public User findUser(String name, String pwd){
            Connection conn = null;
            PreparedStatement stmt = null;
            ResultSet rs = null;
    
            User u = null;
    
            try {
                conn = DBUtils.getConnection(); //得到连接对象
                String sql = "SELECT * FROM users WHERE NAME=? AND PASSWORD=?";
                stmt =  (PreparedStatement) conn.prepareStatement(sql); //得到执行sql语句的对象
                //给？赋值
                stmt.setString(1, name);
                stmt.setString(2, pwd);
                rs = stmt.executeQuery();   //执行sql语句
                if(rs.next()){
                    u = new User();
                    u.setId(rs.getInt(1));
                    u.setName(rs.getString(2));
                    u.setPassword(rs.getString(3));
                    u.setEmail(rs.getString(4));
                    u.setBirthday(rs.getDate(5));
                }
            } catch (Exception e) {
    
                e.printStackTrace();
            }finally{
                DBUtils.closeAll(rs, stmt, conn);
            }
    
            return u;
        }
    }

![这里写图片描述][SouthEast 10]

[SouthEast]: /images/20220609/ceffed900ee24e2cbf3d35077cde2da4.png
[SouthEast 1]: /images/20220609/236f867f31dd42ee9c850e0f3c4496a5.png
[SouthEast 2]: /images/20220609/9c4be9684d244dbd9756c1c7257d886f.png
[SouthEast 3]: /images/20220609/527f2be962ed4190a40d4e3562c08f4a.png
[SouthEast 4]: /images/20220609/cedbb9aedcbe4d70a93aecf3d879dcfc.png
[SouthEast 5]: /images/20220609/66b84435f584406ba6e97541befbeed9.png
[SouthEast 6]: /images/20220609/ade88a5f27874c5bb67ecedebff35d3f.png
[SouthEast 7]: /images/20220609/dce330b4ce5d4ee8bf465283c22b8b92.png
[SouthEast 8]: /images/20220609/328e2114b7c44102a7a0627c9eb1b2b6.png
[SouthEast 9]: /images/20220609/040c3cf3bb724415a2168bb18b55613a.png
[SouthEast 10]: /images/20220609/293ec32282c74543985db034114a8e99.png