Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2011
    Posts
    2

    Unanswered: DB2 encrypt function/java preparedStatement error

    I'm trying to use the DB2 encrypt function with a java preparedStatement and receive the error "[FMWGEN][DB2 JDBC Driver][DB2]Character data, right truncation occurred; for example, an update or insert value is a string that is too long for the column ..."

    DB2 Table :
    column1 varchar(24000)

    DB2 driver used : db2jcc4.jar

    SQL statement is in the form of :
    String sqlStr = "update tableName set column1 = encrypt(?, ?) where column2 = ?"

    ps = con.preparedStatement(sqlStr);
    ps.setString(1, inputStr);
    ps.setString(2, password);
    ps.setString(3, varName);
    ps.executeUpdate();

    This only works when the inputStr is 250 chars or less, even though the DB column length is 24000.
    The query also works if I just use the sqlStr as : (a concatination string)
    String sqlStr = "update tableName set column1 = encrypt('"+inputStr+"', '"+password+"') where column2 = '" + varName "'"
    without the use of setString, but this can allow sql injection and don't really see a reason why setString should not work.
    Any help would be appreciated.
    thanks.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Firstly, ENCRYPT() returns VARCHAR FOR BIT DATA, so you should not be storing it in a VARCHAR column.

    Secondly, the Java to DB2 data type mapping rules state (Data types that map to database data types in Java applications - IBM DB2 9.7 for Linux, UNIX, and Windows) that the java.lang.String values longer than 254 bytes will map to VARCHAR(32672).

    Thirdly, the function description indicates that " the length attribute of the result is equal to the length attribute of the unencrypted data + 8 bytes + the number of bytes until the next 8-byte boundary", meaning that for input strings longer than 254 bytes the result will be 32680 bytes (or 32688 bytes, depending on how you read that description).
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Sep 2011
    Posts
    2
    thanks for the reply, it was very helpful, in my post I stated the column was varchar(24000), Toad was only showing the column as varchar but found out that it is setup for "varchar(24000) for bit data".

    I asked our DBA to increase the column size to 32688 for this column to account for java.lang.String getting mapped to 32672 and the other 8 byte data. Unfortunately our DBA said the max size for this column type is 32672 and when she updated the column to this size, I still received the SQLCODE=-302, SQLSTATE=22001 error when updating data larger than 254 chars.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Two things you could try:
    - redefine the column as BLOB of the appropriate length.
    - explicitly cast the input parameter:

    update tableName set column1 = encrypt(cast(? as varchar(23992)), ?) where column2 = ?
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •