package practice2;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.NumberFormat;
import java.util.Locale;
import javax.swing.JOptionPane;

public class SQLInjectionTest {

    public static void main(String[] args) {
        String url = "jdbc:derby://localhost:1527/EmployeeDB";
        String user = "test";
        String pass = "tiger";
        // SQL Injection (隱碼攻擊)
        // 輸入 1' OR '1'='1
        String inputFirstName = JOptionPane.showInputDialog("請輸入要查詢的員工 first name");
        // select * from employee where firstname='xxx'
        String query = "select * from employee where firstname='" + inputFirstName + "'";
        System.out.println("query = " + query);
        // try-with-resource (自動關閉資源)
        try (
            Connection con = DriverManager.getConnection(url, user, pass);
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            ) {
            int count = 0; // 記錄找到幾筆資料
            while (rs.next()) {
                count++;
                int id = rs.getInt("id");
                String firstName = rs.getString("firstname");
                String lastName = rs.getString("lastname");
                java.util.Date birthdate = rs.getDate("birthdate");
                float salary = rs.getFloat("salary");

                // 格式化字串  String.format( 字串格式指定 , 值1 , 值2 , ... )
                //    %d 整數格式
                //    %s 字串格式
                //    %f 浮點數格式
                //    %-20s  總寬度20個字並靠左對齊
                //    %15s   總寬度15個字

                // 貨幣格式化   NumberFormat.getCurrencyInstance( 國家地區 ).format( 值 )
                // 指定國家地區 Locale

                String s = String.format("%d \t %-20s %s %15s",
                        id,
                        firstName + " " + lastName,
                        birthdate,
                        NumberFormat.getCurrencyInstance(Locale.US).format(salary));
                // 輸出目前所讀到的員工資料
                System.out.println(s);
            }

            if(count == 0) {
                System.out.println("查無此人");
            }

        } catch (SQLException ex) {
            System.out.println(ex);
        } // 無須寫 finally 來 close() 資源

    }

}