JDBC续集（第十九天）

1.用来登录的程序

package cn.tedu.test.Test2;

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

public class Demo01 {
    public static void main(String[] args) throws Exception {
        method();

    }
    /**
     *
     * @return
     * @throws Exception
     */
    public static Connection getConnection() throws Exception{
        Class.forName("com.mysql.jdbc.Driver");
        String url="jdbc:mysql://localhost:3306/cgb2017";
        Connection root = DriverManager.getConnection(url, "root", "1234");
        return root;
    }
    private static void method(){
        try {
            Connection c = getConnection();
            Statement s = c.createStatement();
            ResultSet r = s.executeQuery("select *from user where name='wangzhenya' and passworld='5211314'");
            if (r.next()){
                System.out.println("恭喜你，登录成功");
            }else {
                System.out.println("失败");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

2.用高级传输器写登录界面

private static void method2() throws SQLException {
    Connection c=null;
    PreparedStatement s=null;
    ResultSet r=null;
    try {
        c = getConnection();
        //Statement s = c.createStatement();
        //当用户输入了特殊的名字时jack#，发生了sql攻击的现象
        //SQL攻击
        String a =new Scanner(System.in).nextLine();
        String b =new Scanner(System.in).nextLine();
        //String sql = "select *from user where name='"+a+"' and passworld='"+b+"'";
        String sql = "select * from user where name =? and passworld=?";
        s = c.prepareStatement(sql);//高级传输器
        //给SQL设计参数
        s.setString(1,a);
        s.setString(2,b);
        //执行拼接好的参数。不需要参数
        r = s.executeQuery();

        if (r.next()){
            System.out.println("恭喜你，登录成功");
        }else {
            System.out.println("失败");
        }

    } catch (Exception e) {
        e.printStackTrace();
    }finally {
        r.close();
        s.close();
        c.close();
    }
}

面试题：SQL攻击：出现了特殊的字符#，改变SQL语义，本质上是因为用了Statement传输器
        解决方案：利用全新的传输方式PreparedStatement，把SQL骨架和SQL的参数分开执行，遇到#只是当作普通的文本而不是注释
        优点：省去了拼接SQL语句的麻烦，防止了SQL攻击，高效；