java - SQL语法错误异常 : Column 'TEST' is either not in any table in the FROM list or appears within a join specification

Question

我正在尝试将一些值插入到我的表中，该表是通过执行此查询创建的

public static final String tableName = "ACCOUNT_TABLE"; 

statement.executeUpdate("CREATE TABLE "+ tableName +" (" +
   " ID INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY ("+
   " START WITH 1, INCREMENT BY 1), username VARCHAR(15), password VARCHAR(100)" + ")");

当表创建成功后，我调用注册方法将用户插入表

public boolean registerAccount(final User user){
    if (statement != null){
        final String userName = user.getUserName();
        final String password = user.getPassword();
        try {
            return statement.execute("INSERT INTO "+tableName +" VALUES (" + userName +"," + password +")");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return false;
}

在此示例中，用户名 == "TEST"和密码 == "123"

这里
return statement.execute("INSERT INTO "+tableName+"VALUES ("+ userName +","+ password +")");
抛出异常

java.sql.SQLSyntaxErrorException: Column 'TEST' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'TEST' is not a column in the target table.
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)

Answer 1

1. 字符串应位于两个 'username' 之间，因此您的查询应如下所示 statement.execute("INSERT INTO "+tableName +"VALUES ('"+ userName +"', '"+ 密码 +"')");

2. 但这并不安全，为了避免任何语法错误或 SQL 注入(inject)，您必须改用 PreparedStatement。

3. 关于此错误 java.sql.SQLSyntaxErrorException 发生这种情况是因为您没有指定要在查询中插入哪些列，因此它应该如下所示:

    INSERT INTO tableName(username_col, password_col) VALUES ('userName', 'password')    //-----------------------^-------------^----------------------^-----------^