Client-based parameter validation in a mixed code set environment

An important side effect of potential character data expansion or contraction between the client and server involves the validation of data passed between the client application and the database server. In an unequal code page situation, it is possible that data determined to be valid at the client is actually invalid at the database server after code page conversion. Conversely, data that is invalid at the client might be valid at the database server after conversion.

Any end-user application or API library has the potential of not being able to handle all possibilities in an unequal code page situation. In addition, while some parameter validation, such as string length, is performed at the client for commands and APIs, the tokens within SQL statements are not verified until they have been converted to the database's code page. This verification can lead to situations where it is possible to use an SQL statement in an unequal code page environment to access a database object, such as a table, but it will not be possible to access the same object using a particular command or API.

Consider an application that returns data contained in a table provided by an end-user, and checks that the table name is not greater than 128 bytes long. Now consider the following scenarios for this application:

  1. A DBCS database is created. From a DBCS client, a table (t1) is created with a table name which is 128 bytes long. The table name includes several characters which would be greater than two bytes in length if the string is converted to EUC, resulting in the EUC representation of the table name being a total of 131 bytes in length. Because there is no expansion for DBCS to DBCS connections, the table name is 128 bytes in the database environment, and the CREATE TABLE is successful.
  2. An EUC client connects to the DBCS database. It creates a table (t2) with a table name that is 120 bytes long when encoded as EUC, and 100 bytes long when converted to DBCS. The table name in the DBCS database is 100 bytes. The CREATE TABLE is successful.
  3. The EUC client creates a table (t3) with a table name that is 64 EUC characters in length (131 bytes). When this name is converted to DBCS, its length shrinks to the 128-byte limit. The CREATE TABLE is successful.
  4. The EUC client invokes the application against the each of the tables (t1, t2, and t3) in the DBCS database, which results in:
    Table
    Result
    t1
    The application considers the table name invalid because it is 131 bytes long.
    t2
    Displays correct results
    t3
    The application considers the table name invalid because it is 131 bytes long.
  5. The EUC client is used to query the DBCS database from the CLP. Although the table name is 131 bytes long on the client, the queries are successful because the table name is 128 bytes long at the server.