java - 如何使用java变量向mysql插入值

Question

我正在使用 CLI 学生注册示例系统。我想使用 CLI 连接数据库。为此，我正在使用 MYSQL 和 Java。运行下面的代码后，异常显示如下:

public class Student_Registration {
    String name,uname,pwd;
    int age, select;
    public void input() {
        Scanner sc = new Scanner(System.in);
        System.out.println("Welcome to University Management System\n\n....\nStudent Registration");
        System.out.println("Please Submit the following information,\nName: ");
        name = sc.next();
        System.out.println("Username: ");
        uname = sc.next();
        System.out.println("Password: ");
        pwd = sc.next();
        System.out.println("Age: ");
        age = sc.nextInt();
        System.out.println("Select Course Number from following list");
        System.out.println("[1] SENG 11111 - Introduction to Programming");
        System.out.println("[2] SENG 11112 - Fundamentals of Engineering");
        System.out.println("[3] SENG 11113 - Data Structures and Algorithms");
        select = sc.nextInt();
    }
    public void add(){
        try{
           Class.forName("com.mysql.jdbc.Driver");
           Connection con = DriverManager.getConnection(
                   "jdbc:mysql://localhost:3306/mydatabase", "root", "Luxan@22"
           );
           Statement st = con.createStatement();
           st.executeUpdate("INSERT INTO emp (Username, Name, Age, Password) VALUES ("+uname+","+ name +","+ age +","+ pwd+")");
           con.close();

        }catch (Exception e){System.out.println(e);}
    }
    public void Display() {
        System.out.println("You have successfully registered for the followuing course: ");
       try {
           Class.forName("com.mysql.jdbc.Driver");
           Connection con = DriverManager.getConnection(
                   "jdbc:mysql://localhost:3306/mydatabase", "root", "Luxan@22"
           );
           Statement sta = con.createStatement();
        switch(select) {
            case 1:
                System.out.println("Subject: SENG 11111 - Introduction to Programming");
                sta.executeUpdate("INSERT INTO course (username,course) VALUES ("+uname+","+ "'Subject: SENG 11111 - Introduction to Programming')");
                break;
            case 2:
                System.out.println("Subject: SENG 11112 - Fundamentals of Engineering");
                sta.executeUpdate("INSERT INTO course (username,course) VALUES ("+uname+","+" 'Subject: SENG 11112 - Fundamentals of Engineering')");
                break;

            case 3:
                System.out.println("Subject: SENG 11113 - Data Structures and Algorithms");
                sta.executeUpdate("INSERT INTO course (username,course) VALUES ("+uname+","+" 'Subject: SENG 11113 - Data Structures and Algorithms')");
                break;
        }
        con.close();
    }catch(Exception e) {System.out.println(e);}
       System.out.println("Thank You");
    }
}

表中的列数和相关名称没有问题。异常状态如下:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'luxan' in 'field list'
You have successfully registered for the followuing course: 
Fri Nov 22 15:21:33 IST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Subject: SENG 11111 - Introduction to Programming
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'luxan' in 'field list'

我输入的输入是:

Name: Thiluxan
Username: luxan
password: 1234
Age: 21

Answer 1

您没有正确转义值，这会导致 INSERT 语句出错。在您的示例中，luxan 应该用 ' 引用为 'luxan' 以指示它是文本常量而不是列名。缺少转义也会使您的代码容易受到 SQL Injection attack 的攻击。 .

要解决这两个问题，您应该使用 PreparedStatements as per docs :.

PreparedStatements st = con.prepareStatement(
    "INSERT INTO emp (Username, Name, Age, Password) VALUES (?, ?, ?, ?)");
st.setString(1, uname);
st.setName(2, name);
st.setInt(3, age);
st.setString(4, pwd);
st.executeUpdate();