JDBC Statement用法和弊端

Statement

package com.cloud.statement.crud;

import org.junit.Test;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;

public class StatementTest {
    @Test
    public void testLogin(){
        Scanner scanner = new Scanner(System.in);

        System.out.print("请输入用户名: ");
        String user = scanner.next();
//        String user = "AA";
        System.out.print("请输入密码: ");
        String password = scanner.next();
//        String password = "123456";
        String sql = "select user,password from user_table where user = '"+ user +"' AND password = '"+ password +"'";
        User returnUser = get(sql,User.class);
        if (returnUser != null){
            System.out.println("登录成功");
        }else{
            System.out.println("用户名不存在或密码错误");
        }
    }
    //使用Statement实现对数据表的查询操作
    public <T> T get(String sql,Class<T> clazz){
        T t = null;
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            //1.加载配置文件
            InputStream is = StatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
            Properties pros = new Properties();
            pros.load(is);

            //2.读取配置信息
            String user = pros.getProperty("user");
            String password = pros.getProperty("password");
            String url = pros.getProperty("url");
            String driverClass = pros.getProperty("driverClass");

            //3.加载驱动
            Class.forName(driverClass);

            //4.获取连接
            conn = DriverManager.getConnection(url,user,password);

            st = conn.createStatement();

            rs = st.executeQuery(sql);

            //获取结果集的元数据
            ResultSetMetaData rsmd = rs.getMetaData();

            //获取结果集的列数
            int columnCount = rsmd.getColumnCount();

            if (rs.next()){
                t = clazz.newInstance();

                for(int i = 0;i< columnCount; i++){
                    //1.获取列的别名
                    String columnName = rsmd.getColumnLabel(i + 1);
                    //2.根据列名获取对应数据表中的数据
                    Object columnVal = rs.getObject(columnName);
                    //3.将数据表中得到的数据，封装进对象
                    Field field = clazz.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(t,columnVal);
                }
                return t;
            }
        }catch(Exception e)
        {
            e.printStackTrace();
        }finally {
            //关闭资源
            if (rs!=null){
                try{
                    rs.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
            if(st!=null)
            {
                try{
                   conn.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
        return null;
    }
}

0.gif

弊端sql注入的问题

package com.cloud.statement.crud;

import org.junit.Test;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;

public class StatementTest {
    //使用Statement的弊端:需要拼写sql语句，并且存在SQL注入的问题
    @Test
    public void testLogin(){
        Scanner scanner = new Scanner(System.in);

        System.out.print("请输入用户名: ");
//        String user = scanner.next();
        String user = scanner.nextLine();
//        String user = "AA";
        System.out.print("请输入密码: ");
//        String password = scanner.next();
        String password = scanner.nextLine();
//        String password = "123456";
        //select user,password from firstDB.user_table where user = '1' or ' AND password = '=1 or '1' = '1';
        String sql = "select user,password from user_table where user = '"+ user +"' AND password = '"+ password +"'";
        User returnUser = get(sql,User.class);
        if (returnUser != null){
            System.out.println("登录成功");
        }else{
            System.out.println("用户名不存在或密码错误");
        }
    }
    //使用Statement实现对数据表的查询操作
    public <T> T get(String sql,Class<T> clazz){
        T t = null;
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            //1.加载配置文件
            InputStream is = StatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
            Properties pros = new Properties();
            pros.load(is);

            //2.读取配置信息
            String user = pros.getProperty("user");
            String password = pros.getProperty("password");
            String url = pros.getProperty("url");
            String driverClass = pros.getProperty("driverClass");

            //3.加载驱动
            Class.forName(driverClass);

            //4.获取连接
            conn = DriverManager.getConnection(url,user,password);

            st = conn.createStatement();

            rs = st.executeQuery(sql);

            //获取结果集的元数据
            ResultSetMetaData rsmd = rs.getMetaData();

            //获取结果集的列数
            int columnCount = rsmd.getColumnCount();

            if (rs.next()){
                t = clazz.newInstance();

                for(int i = 0;i< columnCount; i++){
                    //1.获取列的别名
                    String columnName = rsmd.getColumnLabel(i + 1);
                    //2.根据列名获取对应数据表中的数据
                    Object columnVal = rs.getObject(columnName);
                    //3.将数据表中得到的数据，封装进对象
                    Field field = clazz.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(t,columnVal);
                }
                return t;
            }
        }catch(Exception e)
        {
            e.printStackTrace();
        }finally {
            //关闭资源
            if (rs!=null){
                try{
                    rs.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
            if(st!=null)
            {
                try{
                   conn.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
        return null;
    }
}

1.gif
2.gif

很明显，如果用正确的账号密码，是可以查询到到信息。

select user,password from firstDB.user_table where user = 'AA' AND password = '123456';

但是如果执行一条错误的语句，也能查询到信息。

select user,password from firstDB.user_table where user = '1' or ' AND password = '=1 or '1' = '1';

这就是Statement的弊端。需要拼写sql语句，并且存在sql注入的问题。如何避免出现sql注入，只要用PreparedStatement(从Statement扩展而来)取代Statement