JDBC SQl注入

首先创建jdbc工具类

public class DBUtils {
    //静态加载
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接
     *
     * @return
     * @throws SQLException
     */
    public static Connection getConn() throws SQLException {
        return DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc_test", "root", "root");
    }

    /**
     * 预处理sql语句
     *
     * @param conn   连接
     * @param sql    SQL语句
     * @param params 参数列表
     * @return
     * @throws SQLException
     */
    public static PreparedStatement getPre(Connection conn, String sql, Object... params) throws SQLException {
        PreparedStatement pre = conn.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            pre.setObject(i+1, params[i]);
        }
        return pre;
    }

    /**
     * 执行executeUpdate
     * @param pre
     * @return
     * @throws SQLException
     */
    public static int executeUpdate(PreparedStatement pre) throws SQLException {
        return pre.executeUpdate();
    }

    /**
     * 执行executeQuery
     * @param pre
     * @return
     * @throws SQLException
     */
    public static ResultSet executeQuery(PreparedStatement pre) throws SQLException {
         return pre.executeQuery();
    }
}

创建fxml文件作为窗口的样式

<?xml version="1.0" encoding="UTF-8"?>

<?import javafx.scene.control.*?>
<?import javafx.scene.layout.*?>
<?import javafx.scene.text.*?>

<AnchorPane prefHeight="339.0" prefWidth="450.0" xmlns="http://javafx.com/javafx/10.0.2-internal" xmlns:fx="http://javafx.com/fxml/1" fx:controller="com.sqlzhuru.Controller">
   <children>
      <TextField layoutX="86.0" layoutY="119.0" prefHeight="36.0" prefWidth="278.0" promptText="用户名" fx:id="username"/>
      <TextField layoutX="87.0" layoutY="174.0" prefHeight="36.0" prefWidth="278.0" promptText="密码" fx:id="pwd"/>
      <Button layoutX="99.0" layoutY="233.0" mnemonicParsing="false" prefHeight="65.0" prefWidth="254.0" text="登录" onAction="#login"/>
      <Label layoutX="215.0" layoutY="207.0" fx:id="msg"/>
      <Label layoutX="187.0" layoutY="39.0" text="登录" >
         <font>
            <Font size="28.0" />
         </font>
      </Label>
   </children>
</AnchorPane>

创建入口

public class Main extends Application {

    @Override
    public void start(Stage primaryStage) throws Exception{
        Parent root = FXMLLoader.load(getClass().getResource("./sample.fxml"));
        primaryStage.setTitle("Hello World");
        primaryStage.setScene(new Scene(root, 450, 339));
        primaryStage.show();
    }


    public static void main(String[] args) {
        launch(args);
    }
}

创建控制层

public class Controller {

    @FXML
    TextField username;
    @FXML
    TextField pwd;
    @FXML
    Label msg;


    /**
     * 登录
     * @param actionEvent
     */
    public void login(ActionEvent actionEvent) throws SQLException, ClassNotFoundException {

        //加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc_test","root","root");

        //处理sql语句
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select * from user where username ='" + username.getText() + "'and password ='" + pwd.getText()+"'");
        if (resultSet.next()){
            msg.setText("登录成功");
        }else{
            msg.setText("登录失败");
        }
    }

数据展示

我们开始登录

可以看见我们登录失败了

换一种方法

 现在却成功了

那是因为我们的sql语句是拼接的从预想的

select * from user where username ='aa'and password ='aa'

变为了

select * from user where username ='aa'and password ='aa'or'1=1'

 而我们的判断是

if (resultSet.next())只要有数据就是登录成功，这串select * from user where username ='aa'and password ='aa'or'1=1'sql语句则是将全部数据查出来当然有数据了

如何预防sql注入呢

那我们就要prepareStatement方法中的预处理参数来解决这个问题

     //处理sql语句
        String sql = "select * from user where username = ? and password = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,username.getText());
        preparedStatement.setString(2,pwd.getText());
        ResultSet resultSet = preparedStatement.executeQuery();

 

我们可以看见现在就已经是登录不进去了，预处理参数会将你的" ' "转义。