1021上课演练----SQL注入与避免（银行系统）

package com.bank;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

//封装银行卡数据库操作类
public class CardDAO 
{

	//添加卡
	//返回卡号
	public String addCard (String userid,String username,String password)
	{
		String rtn=null;
		//生成卡号
		
		String cardid =(int)(Math.random()*1000000)+"";
		try
		{
		//保存数据
		//1.加载驱动
		Class.forName("oracle.jdbc.driver.OracleDriver");
		
		
		//2.获得连接
		Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "test0816", "123456");
		//3.创建声明
		Statement st=conn.createStatement();
		//4.执行语句
		String sql ="insert into t_bankcard (cardid,user_id,user_name,password,times)"
				+" values('"+cardid+"','"+userid+"','"+username+"','"+password+"',sysdate)";
		if(st.executeUpdate(sql)==1)
		{
			rtn=cardid;
		}
		
		//5.释放资源
		st.close();
		conn.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return rtn;
	}
	
	//修改余额
	//可以完成取款和存款的功能
	//卡号，余额
	public boolean updateBalance(String cardid,double yue) throws Exception
	{
		boolean rtn =false;
		//验证余额
		if(yue<0)
		{
			throw new Exception("余额数据异常");
		}
		try
		{
			
		//保存数据
		//1.加载驱动
		Class.forName("oracle.jdbc.driver.OracleDriver");
		
		//2.获得连接
		Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "test0816", "123456");
		//3.创建声明
		Statement st=conn.createStatement();
		//4.执行语句
		String sql ="update t_bankcard set yue= "
				+yue+"where cardid='"+cardid+"'";
	
		
			rtn=st.executeUpdate(sql)==1;
			
		
		//5.释放资源
		st.close();
		conn.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return rtn;
	}
	
	
	//登入
	//登入
	public boolean login(String cardid,String password)
	{
		boolean rtn=false;
		try{
			//保存数据
			//1.加载驱动
			Class.forName("oracle.jdbc.driver.OracleDriver");
			//2.获得链接
			Connection conn=DriverManager.
					getConnection("jdbc:oracle:thin:@localhost:1521:orcl","test0816","123456");
			
			//3.创建声明
			Statement st=conn.createStatement();
			//4.执行语句
			//这种方式会造成SQL注入
			String sql="select * from t_bankcard where cardid='"+cardid
					 +"'"+"and password='"+password+"'";
			//遍历结果集
			ResultSet rs=st.executeQuery(sql);
			rtn=rs.next();
			//5.释放资源
			st.close();
			conn.close();
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		return rtn;
	}

	
	
	
	
		
		//验证登录
		
		public boolean login2 (String cardid,String password)
		{
			boolean rtn =false;
			
			try
			{
				Class.forName("oracle.jdbc.driver.OracleDriver");
				
				Connection conn=DriverManager.getConnection(
						"jdbc:oracle:thin:@127.0.0.1:1521:orcl", 
						"test0816", "123456");
				//带有？占位符的语句
				String sql= "select * from t_bankcard where "
						+ "cardid=? and"
						+ " password=? and state=?";
				//预编译的声明
				//优点：1.执行效率高
				//2.避免SQL注入
				PreparedStatement ps= conn.prepareStatement(sql);
				//替换占位符
				ps.setString(1, cardid);
				ps.setString(2, password);
				ps.setString(3, "1");
				
				//查询结果集
				ResultSet rs = ps.executeQuery();
				rtn=rs.next();//如果有数据就验证通过
				
				//调用存储过程
				//不怎么用
				//CallableStatement cs=conn.prepareCall("{call存储过程(?,?)}");
				
				//获得数据库的元数据
				DatabaseMetaData dmd= conn.getMetaData();
				System.out.println(dmd.getURL());
				System.out.println("getUserName()"+dmd.getUserName());
				System.out.println("getDatabaseProductName="+dmd.getDatabaseProductName());
				
				//结果集的元数据
				ResultSetMetaData rsmd= rs.getMetaData();
				
				System.out.println("getColumnCount="+rsmd.getColumnCount());
				System.out.println("getColumnName="+rsmd.getColumnName(1));
				
				
				rs.close();
				conn.close();
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
			
			
			
			return rtn;
		}
	//查询
	
}

　　

package com.bank;

import static org.junit.Assert.*;

public class Test {

	@org.junit.Test
	public void testInsert() 
	{
		
		//测试发卡
		CardDAO cd =new CardDAO ();
		String cardid=cd.addCard("1234567890", "张三", "123456");
		if(cardid !=null)
		{
			System.out.println("发卡成功"+cardid);
		}
		else
		{
			System.out.println("发卡失败");
		}
	}
	
	//测试修改余额
	@org.junit.Test
	public void  testEdit() 
	{
		CardDAO cd =new CardDAO ();
		boolean b=false;
		 try 
		 {
			
			 b=cd.updateBalance("148102", 100);
		} 
		 catch (Exception e) 
		 {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		if(b)
		{
			System.out.println("修改余额成功");
		}
		else
		{
			System.out.println("修改余额失败");
		}
	}

	//登入
	@org.junit.Test
	public void testLogin()
	{
		CardDAO cd=new CardDAO();
		if(cd.login("'or 1=1--","14"))//SQL注入
	
		{
			System.out.println("登陆成功");
		}
		else{
			System.out.println("登录失败");
		}
	}

	@org.junit.Test
	//避免SQL注入的测试登入
	public void testLogin2()
	{
		CardDAO cd=new CardDAO();
		if(cd.login2("148102","123456"))
		{
			System.out.println("登陆成功");
		}
		else{
			System.out.println("登录失败");
		}
	}
	
}