JDBC和SQL注入(一)

1 Indroduction

简单介绍下JDBC的使用,并举例说明如何进行SQL注入和防止SQL注入

2 JDBC

JDBC(Java Database Connectivity),规范了Java程序进行数据库操作的API

使用的数据库为MySQL,需要到MySQL网站下载MySQL JDBC Driver

2.1 CRUD(DML)

程序的目录结构如下:

tree .
.
├── lib
│   └── mysql-connector-java-8.0.17.jar
└── src
    └── main
        └── org
            └── zqq
                └── jdbc
                    ├── Driver.class
                    └── Driver.java

数据库初始状态如下:

如下方式编译运行:

$ javac src/main/org/zqq/jdbc/Driver.java
$ java --class-path src/main:lib/* org.zqq.jdbc.Driver

2.1.1 Select

代码如下:

package org.zqq.jdbc;

import java.sql.*;

public class Driver {
    public static void main(String[] args) {
        try {
            // String sql = "select * from employees; drop table students;";

            String sql = "select * from employees";

            Connection myConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "12345678");

            Statement myState = myConn.createStatement();

            ResultSet myRes = myState.executeQuery(sql);

            while (myRes.next()) {
                System.out.println(myRes.getString("last_name") + ", " + myRes.getString("first_name"));
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
            //TODO: handle exception
        }
    }
}

2.1.2 Insert

代码如下:

package org.zqq.jdbc;

import java.sql.*;

public class Driver {
    public static void main(String[] args) {
        try {
            // String sql = "select * from employees; drop table students;";

            String sql = "insert into employees "
            + "(last_name, first_name, age)"
            + "values ('test', 'test', 18)";

            Connection myConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "12345678");

            Statement myState = myConn.createStatement();

            myState.executeUpdate(sql);
            
        } catch (Exception e) {
            System.out.println(e.getMessage());
            //TODO: handle exception
        }
    }
}

结果如下:

2.1.3 Update

代码如下:

package org.zqq.jdbc;

import java.sql.*;

public class Driver {
    public static void main(String[] args) {
        try {
            // String sql = "select * from employees; drop table students;";

            String sql = "update employees set age=20 where id=3";

            Connection myConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "12345678");

            Statement myState = myConn.createStatement();

            myState.executeUpdate(sql);

        } catch (Exception e) {
            System.out.println(e.getMessage());
            //TODO: handle exception
        }
    }
}

2.1.4 Delete

代码如下:

package org.zqq.jdbc;

import java.sql.*;

public class Driver {
    public static void main(String[] args) {
        try {
            // String sql = "select * from employees; drop table students;";

            String sql = "delete from employees where id=3";

            Connection myConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc", "root", "12345678");

            Statement myState = myConn.createStatement();

            int rowsAffected = myState.executeUpdate(sql);

            System.out.println(rowsAffected);

        } catch (Exception e) {
            System.out.println(e.getMessage());
            //TODO: handle exception
        }
    }
}

结果如下:

2.2 Other

2.2.1 存储过程

咱略

2.2.2 函数

咱略

2.2.3 DDL

Data Definition Language,创建表这些 咱略

3 SQL注入

Tags:

Categories:

Updated: