Passing strings containing quotes causes problems with DBTools.J

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.

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).