SQL注入问题的解决方案

sql = "select * from user where username=‘"+username+"‘ and password=‘"+password+"‘";

String sql = "select * from user where username=‘tom‘ and password=‘123‘";

select * from user where username=‘jerry‘ and password=‘abc‘；//这种情况是不会有问题的

//测试如果传入的是SQL语句的的一部分

username:tom‘ or ‘1‘=‘1

password:????????

//这时候的SQL语句为一下这种情况

select * from user where username=‘tom‘ or ‘1‘=‘1‘ and password=‘?????‘；

//由于and的执行优先级大于Or 可以看作以下情况

select * from user where username=‘tom‘ or (‘1‘=‘1‘ and password=‘?????‘)；

//由于是or 或者的关系这时的SQL语句可以看作是这样的语句

select * from user where username=‘tom‘；

二、解决方案（引入：PreparedStatement对象，使SQL语句进行预编译）PreparedStatement pstmt = conn.perpareStatement(String sql);其实statement执行executeQuery内部分为两步：第一步：编译sql 第二步：执行sql优点：能预编译sql语句
例子：数据库客户端服务端基本模型登陆端

package com.heima.login.client;
import java.util.Scanner;
import com.heima.login.bean.User;
import com.heima.login.seriver.services;
public class login {
	/**
	 * @param args
	 * @throws Exception 
	 */
	public static void main(String[] args) throws Exception {
		// TODO Auto-generated method stub
		    // 创建Scanner接收输入内容
            Scanner sc= new Scanner(System.in);
			System.out.println("请输入用户名");
			String username = sc.nextLine();
			System.out.println("请输入密码");
			String password = sc.nextLine();
            // 创建服务端对象使用登陆方法
			services s=new services();
			User user = s.login(username, password);
			if(user!=null){
				System.out.println(user);
			}else{
				System.out.println("用户名或密码错误！！！");
			}
		}
	
}

服务端：

package com.heima.login.seriver;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.heima.Tools.JdbcConnection;
import com.heima.login.bean.User;
public class services {
	public User login(String username,String password) throws Exception
	{
		//新建user引用，默认值问null
		User u=null;
		//使用自定义数据库连接工具类进行数据库连接
		Connection conn=JdbcConnection.getConnection();
		//创建预编译数据库连接语句
		String sql = "select * from user where username=? and password=?";
		//创建PreparedStatement对象对数据库语句进行预编译
		PreparedStatement stem=conn.prepareStatement(sql);
		//设置预编译语句中的查询值
		stem.setString(1, username);
		stem.setString(2, password);
		//执行SQL语句
		ResultSet rs=stem.executeQuery();
		//如果有记录对User对象进行赋值
		if(rs.next())
		{
			u = new User();
			u.setAge(rs.getInt("age"));
			u.setDept(rs.getString("Dept"));
			u.setEmail(rs.getString("email"));
			u.setGender(rs.getString("gender"));
			u.setId(rs.getInt("id"));
			u.setPassword(rs.getString("password"));
			u.setRegistTime(rs.getDate("registTime"));
			u.setSalary(rs.getDouble("salary"));
			u.setUsername(rs.getString("username"));
			
		}
		//关闭数据库资源
		JdbcConnection.close(conn, stem, rs);
		return u;
		
	}
}

User对象：

package com.itheima.login.entity;
public class User {
	private int id;
	private String username;
	private String password;
	private String email;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	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;
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", password="
				+ password + ", email=" + email + "]";
	}
	
	
}

来自为知笔记(Wiz)

SQL注入问题的解决方案

标签：