插入单引号之间的数据时出错 [英] Error while inserting data having single quotes between it

问题描述

我有一个将Clob数据保存到数据库中的应用程序. 假设当我在文本框中输入一些文本，然后单击保存". 它将调用一个过程，该过程会将文本(clob)插入数据库表中.

I have an application that is saving clob data into database. Suppose when I put some text into textbox and then click save. It will call a procedure that will insert this text (clob) into database table.

假设我有以下文字:

我是高拉夫的索尼人

Hi i am gaurav's soni's

现在，我的过程将此Clob数据作为:

Now my procedure take this clob data as:

insert into rtf_clob(1,'Hi i am gaurav's soni's');

但是这会引发错误.

如何处理来自前端的动态数据中的单引号?
我正在使用oracle作为RDBMS.

How to handle this single quote in dynamic data coming from front end?
I'm using oracle as RDBMS.

推荐答案

您不应编写代码来转义字符串.您需要转义字符串的事实表明您做错了事.

You should not be writing code to escape the string. The fact that you need to escape the string implies that you are doing something wrong.

如果在INSERT语句中使用绑定变量，则在有一个单引号的情况下将不需要转义该字符串.如果您不使用绑定变量，也无需尝试识别字符串中的SQL注入攻击，这是一个巨大的安全问题.而且，您不会强迫Oracle在每次执行查询时都对查询进行解析，这对系统性能至关重要.

If you are using bind variables in your INSERT statement, there will be no need to escape the string when there is a stray single quote. There will also be no need to try to identify SQL injection attacks in the string which is a huge security issue if you're not using bind variables. And you won't be forcing Oracle to parse the query every time it is executed which is critical to system performance.

如果您正在谈论的过程是用PL/SQL编写的，它将自动使用绑定变量.猜测列名，并假设您正在使用序列生成主键，您将拥有类似的内容

If the procedure you're talking about is written in PL/SQL, it will use bind variables automatically. Guessing at the column names and assuming that you're using a sequence to generate your primary key, you'd have something like this

CREATE PROCEDURE insert_rtf_clob( p_clob IN NOCOPY CLOB )
AS
BEGIN
  INSERT INTO rtf_clob( rtf_clob_id, rtf_clob_value )
    VALUES( seq_rtf_clob_id.nextval, p_clob );
END;

其他前端语言将具有使用绑定变量的不同方法.例如，如果您使用JDBC编写Java，则需要创建PreparedStatement，然后调用相应的setXXX方法，即

Other front-end languages will have different approaches to using bind variables. If you're writing Java using JDBC, for example, you'd create a PreparedStatement and then then call appropriate setXXX methods, i.e.

PreparedStatement stmt = conn.prepareStatement( "INSERT INTO rtf_clob VALUES( ?, ? )" );
stmt.setInt( 1, 1 ); // Set column 1 to a value of 1
stmt.setString( 2, someStringVariable ); // Set column 2 to someStringVariable
stmt.executeUpdate();

这篇关于插入单引号之间的数据时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！