Help: how do you escape html text when inserting it into a varchar2(2000)

Help: how do you escape html text when inserting it into a varchar2(2000)

Post by johnga.. » Wed, 24 Jan 2001 11:35:45



I have some html I want to insert into an Oracle 8.05 database and
I'm using some java to write the select as such:

String html = (String) session.getAttribute("DOCTEXT");
String id = (String) session.getAttribute("DOCID");

String query = "insert into ATABLE (ID,TEXT) VALUES(" + id + ", '" +
                     html + "')";
stmt.executeUpdate(query);

I get the exception:ORA-00904: invalid column name

and I'm sure it's a problem with escaping some characters but I can't
find which ones after checking the Oracle Reference and some of these
messages.  Do I use the SQLPlus TRANSLATE? or is there an escape
character that is commonly used?

(yes, we will be refactoring later for less presentation abstraction
from the database)

Sent via Deja.com
http://www.deja.com/

 
 
 

Help: how do you escape html text when inserting it into a varchar2(2000)

Post by Maxim Anisko » Wed, 24 Jan 2001 15:17:43


John,

It's generally good idea to use prepared statements in this case:

PreparedStamenet pstmt=connection.prepareStatement("insert into ATABLE
(ID,TEXT) VALUES(?,?)");
pstmt.setInt(1, id);
pstmt.setString(2, html);
pstsmt.execureUpdate();

Regards,
maxim

-------------------------------
Maxim Aniskov

Novosoft Inc.
http://www.novosoft-us.com
IT consulting services
-------------------------------


> I have some html I want to insert into an Oracle 8.05 database and
> I'm using some java to write the select as such:

> String html = (String) session.getAttribute("DOCTEXT");
> String id = (String) session.getAttribute("DOCID");

> String query = "insert into ATABLE (ID,TEXT) VALUES(" + id + ", '" +
>                      html + "')";
> stmt.executeUpdate(query);

> I get the exception:ORA-00904: invalid column name

> and I'm sure it's a problem with escaping some characters but I can't
> find which ones after checking the Oracle Reference and some of these
> messages.  Do I use the SQLPlus TRANSLATE? or is there an escape
> character that is commonly used?

> (yes, we will be refactoring later for less presentation abstraction
> from the database)

> Sent via Deja.com
> http://www.deja.com/


 
 
 

1. Can't insert exactly 2000 chars into VARCHAR2(2000) column

Hello,

I want to insert a string exactly 2000 chars long by host variable into a
VARCHAR2(2000) column from a Pro*C program. The last 20-30 characters get
garbled from unknown reason. (We can call it the "C2K-problem" :-) The same
program works fine with strings 1950-1970 chars long. Tried with VARCHAR
xyz[2000] and char xyz[2000+1] as well. Precompiler: Pro*C for WinNT,
Version 8.0.5, Server: 7.3.2 RDBMS running on AIX.

Any help would be welcome.

2. Choice of database server

3. varchar2 field doesn't break lines in HTML table cell

4. Opening connections using JDBC/ Oracle 8.05 is VERY slow

5. inserting a huge text file into mssql 2000

6. XPath problem with sql:is-constant="1" node

7. Inserting chinese language text in Sql Server 2000

8. package problem

9. inserting a huge text file into mssql 2000

10. HELP: DB-Library - doing multiple reads and inserts

11. How to escape HTML tags in ORDER BY clause

12. DAO3.6, Ora-DB via ODBCDirect, RecordCount -1, no Moves, no use of VARCHAR2(2000)-Fields