Passing strings containing quotes causes problems with DBTools.J

Article ID: 1056
Last updated: 02 Feb, 2008
Article ID: 1056
Last updated: 02 Feb, 2008
Revision: 1
Views: 11324
Posted: 28 Jul, 1998
by Dean J.
Updated: 02 Feb, 2008
by Dean J.
Problem


When I try and pass a string that contains a single or double quote into the database I get an error back from the database.


Cause


In general, there is no completely portable way to handle the escaping of quote characters in database queries. Moreover, it was a design decision of DBTools.J not to provide special handling of this issue because there is a simple and elegant solution -- using BoundObjects.




Action


Consider the following piece of code:

    Connection conn = pool.getConnection();
DBTable table = new DBTable("myOrders");
int orderId = 12345;
String customerName = "Thomas O'Malley";

Inserter inserter = new Inserter(table);
inserter.addInt(orderId);
inserter.addString(customerName);
inserter.execute(conn);

When the inserter is executed, it is possible for an error to occur in the database. This is due to the fact that the single quote in O'Malley is misinterpreted to be the end of a string in the SQL statement that is created.

Using a bound object can eliminate this problem.

    Connection conn = pool.getConnection();
DBTable table = new DBTable("myOrders");
BoundObject orderId = new BoundObject();
BoundObject customerName = new BoundObject();

Inserter inserter = new Inserter(table);
inserter.addBoundObject(orderId);
inserter.addBoundObject(customerName);

orderId.setInt(12345);
customerName.setString("Thomas O'Malley");
inserter.execute(conn);

Using the bound object will create an SQL statement that does not interfere with the end of string markers that are used by the database.

You can, of course, also use the standard SQL method of quoting ' and characters by '' and (two in a row).

This article was:   Helpful | Not helpful
Report an issue
Article ID: 1056
Last updated: 02 Feb, 2008
Revision: 1
Views: 11324
Posted: 28 Jul, 1998 by Dean J.
Updated: 02 Feb, 2008 by Dean J.

Others in this category