1021 class Walkthrough----SQL injection and Avoidance (banking system)

Tag:upd   rom    auto-Generate    .com   tco    manage    call    set   double   

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;//Package Bank card Database operation class public class Carddao {//Add card//Return card number public String Addcard (string userid,string username,string password) {string rtn=null;//Generate card number String Cardid = (int) (Math.random () * 1000000) + ""; try{//save data//1. Load driver Class.forName ("Oracle.jdbc.driver.OracleDriver");//2. Get Connection Connection conn= Drivermanager.getconnection ("Jdbc:oracle:thin:@127.0.0.1:1521:orcl", "test0816", "123456");//3. Creating a declaration statement st= Conn.createstatement ();//4. Execute statement 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. Release resources st.close (); Conn.close ();} catch (Exception e) {e.printstacktrace ();} return RTN;} Modify the balance//can complete the withdrawal and deposit function//card number, balance public BooleaN updatebalance (String cardid,double Yue) throws Exception{boolean Rtn =false;//Verify balance if (yue<0) {throw new Exception (" Balance Data Exception ");} try{//save data//1. Load driver Class.forName ("Oracle.jdbc.driver.OracleDriver");//2. Get Connection Connection conn= Drivermanager.getconnection ("Jdbc:oracle:thin:@127.0.0.1:1521:orcl", "test0816", "123456");//3. Creating a declaration statement st= Conn.createstatement ();//4. Execute statement String sql = "Update t_bankcard set yue=" +yue+ "where cardid= '" +cardid+ "'"; rtn= St.executeupdate (SQL) ==1;//5. Release resources st.close (); Conn.close ();} catch (Exception e) {e.printstacktrace ();} return RTN;} Login//Log in public boolean login (String cardid,string password) {Boolean rtn=false;try{//save data//1. Load Driver Class.forName (" Oracle.jdbc.driver.OracleDriver ")//2. Get the link connection conn=drivermanager.getconnection (" jdbc:oracle:thin:@ Localhost:1521:orcl "," test0816 "," 123456 ");//3. Create declaration statement st=conn.createstatement ();//4. Execute statement// This approach causes SQL injection string sql= "select * from T_bankcard where cardid= '" +cardid + "'" + "and password= '" +password+ "'";// Traverse result set ResultSet Rs=st.executeqUery (SQL); Rtn=rs.next ();//5. Release Resources st.close (); Conn.close ();} catch (Exception e) {e.printstacktrace ();} return RTN;} Verify that the logon public is 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");//with? Placeholder for the statement string sql= "select * from T_bankcard where" + "cardid=? and "+" password=? and state=? "; /precompiled declarations//advantages: 1. High efficiency//2. Avoid SQL injection preparedstatement ps= conn.preparestatement (SQL);//Replace Placeholder ps.setstring (1, Cardid); Ps.setstring (2, password);p s.setstring (3, "1");//Query result set ResultSet rs = Ps.executequery (), Rtn=rs.next ();//If there is data to verify through// Calling a stored procedure//not using//callablestatement Cs=conn.preparecall ("{Call stored procedure (?,?)}"); /obtain metadata for the database DatabaseMetaData dmd= conn.getmetadata (); System.out.println (Dmd.geturl ()); System.out.println ("GetUserName ()" +dmd.getusername ()); System.out.println ("Getdatabaseproductname=" +dmd.getdatabaseproductname ());//meta-data resultsetmetadata rsmd= of result set 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;} Query

　　

Package Com.bank;import static org.junit.assert.*;p ublic class Test {@org. junit.testpublic void Testinsert () {// Test hairpin Carddao CD =new Carddao (); String Cardid=cd.addcard ("1234567890", "Zhang San", "123456"), if (Cardid!=null) {System.out.println ("hairpin success" +cardid);} ELSE{SYSTEM.OUT.PRINTLN ("card issuer Failed");}} Test Modify balance @org.junit.testpublic void  testedit () {Carddao cd =new Carddao (); Boolean b=false; try  {b= Cd.updatebalance ("148102", +);}  catch (Exception e)  {//TODO auto-generated catch block E.printstacktrace ();} if (b) {System.out.println ("Modified balance succeeded");} Else{system.out.println ("Modify balance Failed");}} Log in @org.junit.testpublic void Testlogin () {Carddao cd=new Carddao (), if (Cd.login ("' or 1=1--", "+")//sql inject { System.out.println ("Landing Success");} ELSE{SYSTEM.OUT.PRINTLN ("Login Failed");}} @org. junit.test//Avoid SQL injection test login public void testLogin2 () {Carddao cd=new Carddao (), if (Cd.login2 ("148102", "123456") { System.out.println ("Landing Success");} ELSE{SYSTEM.OUT.PRINTLN ("Login Failed");}}}

　　

1021 class Walkthrough----SQL injection and Avoidance (banking system)