oracle SQLSyntaxErrorException: ORA-00911: 无效字符

SQLSyntaxErrorException: ORA-00911: invalid character

提问by es0329

I'm trying to INSERT records to an Oracle DB using PreparedStatement but I only get this error. At this point, my effort to overcome it far outweighs my progress so another set of eyes might help. Where is the invalid character?

我正在尝试使用 PreparedStatement 将记录插入 Oracle 数据库，但我只收到此错误。在这一点上，我克服它的努力远远超过了我的进步，所以另一组眼睛可能会有所帮助。无效字符在哪里？

A lot of what I've found suggests that a trailing ";" inside of your sql String can be the culprit, but I haven't had one in my statement from the outset.

我发现的很多内容都表明;sql 字符串中的尾随“ ”可能是罪魁祸首，但从一开始我的语句中就没有。

My connection itself, which worksperfectly at several other places in the program:

我的连接本身，其作品在程序中的其他几个地方完美：

Properties props = new Properties();
props.setProperty( "user", username );
props.setProperty( "password", password );
props.setProperty( "defaultRowPrefetch", "10" );
props.setProperty( "defaultBatchValue", "10" );
props.setProperty( "processEscapes", "false" );

DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
Connection conn = DriverManager.getConnection(DB_URL_SVC, props);

The way I'd like to get it done (except that I would wrap it in a method that accepts three Strings) but it throws a SQLSyntaxErrorException

我想要完成它的方式（除了我将它包装在一个接受三个字符串的方法中）但它抛出一个SQLSyntaxErrorException

String INSERT_BIKE = "INSERT INTO RACEBIKES ( BIKENAME , COUNTRY_OF_ORIGIN , COST ) VALUES ( ? , ? , ? )";
PreparedStatement preStatement = conn.prepareStatement( INSERT_BIKE );
preStatement.setString(1, "JHT");
preStatement.setString(2, "USA");
preStatement.setInt(3, 2500);
preStatement.executeUpdate();    // ORA-00911: invalid character error

This works, but defeats the purpose of using PreparedStatement since the parameters are hard-coded:

这有效，但由于参数是硬编码的，因此违背了使用 PreparedStatement 的目的：

String INSERT_BIKE = "INSERT INTO RACEBIKES ( BIKENAME , COUNTRY_OF_ORIGIN , COST ) VALUES ( 'JHT' , 'USA' , '2500' )";
PreparedStatement preStatement = conn.prepareStatement( INSERT_BIKE );
preStatement.executeUpdate();

Works. However, I understand that concatenating variables with single and double quotes also isn't really the best way because PreparedStatement should alleviate us from having to deal with that part of the syntax:

作品。但是，我知道用单引号和双引号连接变量也不是最好的方法，因为 PreparedStatement 应该让我们不必处理那部分语法：

String value1 = "JHT";
String value2 = "USA";
int value3 = 2500;
String INSERT_BIKE = "INSERT INTO RACEBIKES ( BIKENAME , COUNTRY_OF_ORIGIN , COST ) VALUES ( '" + value1 + "', '" + value2 + "', '" + value3 + "' )";
PreparedStatement preStatement = conn.prepareStatement( INSERT_BIKE );
preStatement.executeUpdate();

Failswith a SQLSyntaxErrorException. So even if code quotation syntax myself, I'm still unable to place those variables in the preStatement.setString(), which would at least allow a little flexibility.

无法用SQLSyntaxErrorException。因此，即使我自己使用代码引用语法，我仍然无法将这些变量放在 preStatement.setString() 中，这至少可以提供一点灵活性。

String INSERT_BIKE = "INSERT INTO RACEBIKES ( BIKENAME , COUNTRY_OF_ORIGIN , COST ) VALUES ( ? , ? , ? )";
PreparedStatement preStatement = conn.prepareStatement( INSERT_BIKE );
preStatement.setString(1, "' + value1 + '");
preStatement.setString(2, "' + value2 + '");
preStatement.setInt(3, "' + value3 + '");
preStatement.executeUpdate();    // ORA-00911: invalid character error

Fails.Enclosing the placeholders in my String with single quotes results in SQLException.

失败。用单引号将占位符括在我的 String 中会导致SQLException。

String INSERT_BIKE = "INSERT INTO RACEBIKES ( BIKENAME , COUNTRY_OF_ORIGIN , COST ) VALUES ( '?' , '?' , '?' )";
PreparedStatement preStatement = conn.prepareStatement( INSERT_BIKE );
preStatement.setString(1, "JHT");
preStatement.setString(2, "USA");
preStatement.setInt(3, 2500);
preStatement.executeUpdate();    // invalid column index

Fails.Enclosing the two String(but not the int) placeholders in my String with single quotes results in SQLException.

失败。用单引号将我的 String 中的两个String（但不是int）占位符括起来导致SQLException。

String INSERT_BIKE = "INSERT INTO RACEBIKES ( BIKENAME , COUNTRY_OF_ORIGIN , COST ) VALUES ( '?' , '?' , ? )";
PreparedStatement preStatement = conn.prepareStatement( INSERT_BIKE );
preStatement.setString(1, "JHT");
preStatement.setString(2, "USA");
preStatement.setInt(3, 2500);
preStatement.executeUpdate();    // invalid column index

This doesn't fail, but doesn't write to databaseeither (even though I haven't disabled auto-commit).

这不会失败，但也不会写入数据库（即使我没有禁用自动提交）。

String INSERT_BIKE = "INSERT INTO RACEBIKES ( BIKENAME , COUNTRY_OF_ORIGIN , COST ) VALUES ( ? , ? , ? )";
PreparedStatement preStatement = conn.prepareStatement( INSERT_BIKE );
preStatement.setString(1, "JHT");
preStatement.setString(2, "USA");
preStatement.setInt(3, 2500);
preStatement.executeBatch();

I've also tried all matter of escapes with backslashes, double backslashes, backticks, quitsies, no-startsies, erasies, double-stamps, and tofus-make-it-true! Maybe somebody out there knows the voodoo that will help me?!

我还尝试了所有有关使用反斜杠、双反斜杠、反引号、quitsies、no-startsies、erasies、double-stamps 和 tofus-make-it-true 进行转义的问题！也许外面有人知道可以帮助我的巫毒教？！

回答by Luke Woodward

Is there a reason why you have props.setProperty( "processEscapes", "false" );?

你有理由props.setProperty( "processEscapes", "false" );吗？

I believe that this turns off the ability to use ?as bind parameter placeholder. I believe that if escape processing is enabled, JDBC does some 'magic' with the ?placeholders before passing the SQL string to Oracle. Otherwise, the ?character is sent to the database as-is.

我相信这会关闭?用作绑定参数占位符的能力。我相信如果启用转义处理，JDBC?在将 SQL 字符串传递给 Oracle 之前会使用占位符执行一些“魔术” 。否则，?字符将按原样发送到数据库。

There are occasional uses for disabling escape processing. I used it in a previous answerto a question involving ?characters in passwords. I believe it can be disabled at the connection or the statement level; to re-enable escape processing on a PreparedStatement, try calling preStatement.setEscapeProcessing(true);. I would expect the first of your failing examples to succeed with this option set.

偶尔用于禁用转义处理。我在之前对涉及?密码字符的问题的回答中使用了它。我相信它可以在连接或语句级别禁用；要在 PreparedStatement 上重新启用转义处理，请尝试调用preStatement.setEscapeProcessing(true);. 我希望您的第一个失败示例能够通过此选项集成功。

As for your failing examples, those with unescaped ?s will cause problems as ?is not a valid character in SQL. Surrounding ?in single quotes turns it into a single-character string, so it wouldn't be a bind parameter even if escape processing is enabled. I can't say why the last one doesn't write to the database.

至于你失败的例子，那些没有转义的?s 会导致问题，因为?它不是 SQL 中的有效字符。周围?用单引号把它变成一个单字符字符串，所以它不会是即使启用转义处理的绑定参数。我不能说为什么最后一个不写入数据库。