Java Oracle 插入查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1233179/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Java Oracle insert Query
提问by Jay
I am trying to insert user's input in oracle using Java JDBC. I have to use Insert query.
我正在尝试使用 Java JDBC 在 oracle 中插入用户的输入。我必须使用插入查询。
Oracle's column type is varchar2.
Oracle 的列类型是 varchar2。
I am able to insert if user is not entering special character. when user enter special character like # ? / and $ it gives exception
如果用户没有输入特殊字符，我可以插入。当用户输入特殊字符如 # 时？/ 和 $ 它给出异常
Please help to parse these special character. i need to use insert query i cant use stored procedure or callable statement in my project.
请帮助解析这些特殊字符。我需要使用插入查询我不能在我的项目中使用存储过程或可调用语句。
Thank you for your time.
感谢您的时间。
回答by Nick Holt
So far as I know Oracle doesn't require you to escape string values so:
据我所知，Oracle 不要求您转义字符串值，因此：
INSERT INTO some_table (my_varchar_column) VALUES ('my string with a ?');
should work.
应该管用。
But to be sure use a java.sql.PreparedStatementas follows:
但要确保使用 ajava.sql.PreparedStatement如下：
PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO some_table (my_varchar_column) VALUES (?)");
preparedStatement.setString(1, "my string with a ?");
preparedStatement.execute();
Using a prepared statement is generally the recommended way to execute SQL against a database. It will aid performance and help prevent SQL injection attacks.
使用准备好的语句通常是对数据库执行 SQL 的推荐方法。它将有助于提高性能并有助于防止 SQL 注入攻击。
回答by Jay
The only character that needs escaping in a SQL string constant is the single quote, for the obvious reason that it could otherwise be confused for the end of the string.
SQL 字符串常量中唯一需要转义的字符是单引号，原因很明显，否则它可能会与字符串的结尾混淆。
If you are using JDBC PreparedStatement's, then a question mark by itself stands for a parameter. You would write something like
如果您使用的是 JDBC PreparedStatement，那么问号本身就代表一个参数。你会写一些类似的东西
insert into mytable (field1, field2) values (?, ?)
插入到 mytable (field1, field2) 值 (?, ?)
and then use set statements to set these values. If you enclose the question mark in quotes, then it should just be another character in a string, i.e.
然后使用 set 语句来设置这些值。如果您将问号括在引号中，那么它应该只是字符串中的另一个字符，即
insert into mytable (field1, field2) values (?, '?')
插入 mytable (field1, field2) 值 (?, '?')
has ONE parameter ? and one string literal '?'.
有一个参数？和一个字符串文字 '?'。
PreparedStatement.set will do the necessary escaping of quotes. If you build the query yourself, you'll need to include code to do that escaping. (Just double the quote, e.g.
PreparedStatement.set 将执行必要的引号转义。如果您自己构建查询，则需要包含代码来进行转义。（只需将引号加倍，例如
insert into mytable (field1) values ('Bob said, ''Hello''')
插入 mytable (field1) 值（'Bob said, ''Hello'''）
