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("登录失败");
}
}}