登录验证之sql注入问题解决方案

1.初始版本的sql注入问题

sql注入：是指把用户输入的参数作为sql语句的本身来执行

public class Demo_login {
	public static void main(String[] args) {
		System.out.println("请输入用户名：");
		Scanner sc = new Scanner(System.in);
		String username = sc.nextLine();
		System.out.println("请输入密码：");
		String password = sc.nextLine();
		boolean result = checkUser(username,password);
		if (result) {
			System.out.println("登录成功");
		}else {
			System.out.println("登录失败");
		}
	}

	private static boolean checkUser(String username, String password) {
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			//1.加载驱动
			Class.forName("com.mysql.jdbc.Driver");
			
			String url = "jdbc:mysql:///test";
			Properties p = new Properties();
			p.setProperty("user", "root");
			p.setProperty("password", "123");

			//2，获取Connection对象
			conn = DriverManager.getConnection(url, p);
			//3,获取Statement对象
			st = conn.createStatement();

			//4执行sql
			String sql = "select * from tb_user where uname='"+username+"'and password='"+password+"'";
			rs = st.executeQuery(sql);
			//5。根据结果返回布尔值的数据
			if (rs.next()) {
				 //用户名与密码正确
				return true;
			}else {//用户名和密码错误
				return false;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (st!=null) {
				try {
					st.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(rs!=null) {
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return false;
	}
}

问题：如果在输入密码时输入一些如  wasg' or '1'='1 就会出现错误的用户名和密码也登录成功的情况出现

2.prepareStatement解决sql注入问题

prepareStatement对象是一个sql语句的预编译对象，把sql语句先编译存储，用户输入的参数只作为参数

？号表示点位符

将原来的sql语句修改：

修改前：String sql = "select * from tb_user where uname='"+username+"'and password='"+password+"'";

修改后：String sql = "select * from tb_user where uname =? and password=?";

代码实现如下：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.util.Scanner;

public class Demo_login2 {
	public static void main(String[] args) {
		System.out.println("请输入用户名：");
		Scanner sc = new Scanner(System.in);
		String username = sc.nextLine();
		System.out.println("请输入密码：");
		String password = sc.nextLine();
		boolean result = checkUser(username, password);
		if (result) {
			System.out.println("登录成功");
		} else {
			System.out.println("登录失败");
		}
	}

	private static boolean checkUser(String username, String password) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			// 1.加载驱动
			Class.forName("com.mysql.jdbc.Driver");
			// 2，获取Connection对象
			String url = "jdbc:mysql:///test";
			Properties info = new Properties();
			info.setProperty("user", "root");
			info.setProperty("password", "123");

			conn = DriverManager.getConnection(url, info);
            //使用？号作为点位符
			String sql = "select * from tb_user where uname =? and password=?";
            //使用prepareStatement提前传入sql进行预编译
			ps = conn.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, password);
			// 执行但不需要有参数，因为sql和参数都已经给出了
			rs = ps.executeQuery();
			if (rs.next()) {
				return true;
			} else {
				return false;
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
            //关闭资源
			if (conn!=null) {
				try {
					conn.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 (rs!=null) {
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return false;
	}
}