翔の學習淺談

※使用者輸入名稱，調出該名稱相關資料

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

public class Test0617 {
    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        String name = scanner.nextLine();
        
        try {
            Connection con =DriverManager.getConnection("jdbc:derby://localhost/Test", "user1", "pass");
            Statement stamt  = con.createStatement();
            String sql = String.format("select * from cu where cname='%s'", name);
            ResultSet rs = stamt.executeQuery(sql);
            while(rs.next()){
                System.out.printf("customer_id = %3s\t",String.valueOf(rs.getObject("cid")));
                System.out.printf("name = %3s\t",String.valueOf(rs.getObject("cname")));
                System.out.printf("address = %3s\t",String.valueOf(rs.getObject("address")));
                System.out.println("");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Input:
ABC

Out:
customer_id =   1    name = ABC    address = DEF    

※但是這種寫法會產生SQL Injection，

若輸入 ' or 1=1 --' 會把所以資料印出來，OR只要其中為true，出來一定為true

 ' or 1=1 --'   →    ...cname='' or 1=1 --''

以下方法解決:

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

public class Test0617 {
    public static void main(String[] args) {
             Scanner scanner = new Scanner(System.in);
             String name = scanner.nextLine();
            try {
                Connection con = DriverManager.getConnection("jdbc:derby://localhost/Test", "user1", "pass");
                PreparedStatement stmt = con.prepareStatement("select * from cu where cname=?");      //需要尋找的東西用問號代替
                stmt.setString(1, name);                                                                                                          //設定只有一個字串
                ResultSet rs = stmt.executeQuery();
                while(rs.next()){
                System.out.printf("customer_id = %3s\t",String.valueOf(rs.getObject("cid")));
                System.out.printf("name = %3s\t",String.valueOf(rs.getObject("cname")));
                System.out.printf("address = %3s\t",String.valueOf(rs.getObject("address")));
                System.out.println("");
            }
            } catch (Exception e) {
                e.printStackTrace();
            }                  
    }
}

※事實上，輸入到資料庫也需要這種輸入方式。

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

public class Test0617 {
    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        int cid = scanner.nextInt();
        String name = scanner.next();
        String address = scanner.next();
        try {
                Connection con = DriverManager.getConnection("jdbc:derby://localhost/Test", "user1", "pass");
                PreparedStatement stmt = con.prepareStatement("insert into cu values(?,?,?)");
                stmt.setInt(1, cid);
                stmt.setString(2,name);
                stmt.setString(3, address);
                stmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }           
    }
}