PDO_Firebird: Bad interpretation of length when char is UTF-8

[danielbredasantos]

Description

When I have a database with UTF-8 charset and a field with UTF-8 char(1), the sql command should return a string with 1 position, but it returns a string with 4 positions.

Ex.: Will return "A" - Char(1), But return "A " - Char(1)

You must check the field size with SQL_VARYING.

PHP Version

PHP 8.1.2

Operating System

Windows 11

[cmb69]

Confirmed:

$pdo->exec("CREATE TABLE gh8576 (name CHAR(1) CHARACTER SET UTF8)");
$pdo->exec("INSERT INTO gh8576 VALUES ('A')");
$stmt = $pdo->query("SELECT * FROM gh8576");
var_dump($stmt->fetchAll());

outputs:

array(1) {
  [0]=>
  array(2) {
    ["NAME"]=>
    string(4) "A   "
    [0]=>
    string(4) "A   "
  }
}

[cmb69]

You must check the field size with SQL_VARYING.

Well, we can't, since isc_dsql_prepare() reports the column as SQL_TEXT, so there is no length indicator. The next best thing we can use is the XSQLVAR.sqllen which gives the maximum length of the column data, so you get the reported result. The ext/interbase extension (unbundled as of PHP 7.4.0) behaves the same.

If you want proper support for MBCS, use VARCHAR fields.

[asfernandes]

You can change the types described. I do it in node-firebird-driver-native to fix this problem, so the client driver does not need to interpret strings of different character sets to return correct value to the user.

[cmb69]

You can change the types described. I do it in node-firebird-driver-native to fix this problem, […]

I'm not sure if I understand; are you transforming the original query to insert CASTs?

[asfernandes]

I'm not sure if I understand; are you transforming the original query to insert CASTs?

No.

isc_dsql_prepare (and isc_dsql_describe_bind) is used to describe query input parameters. You can use the same technique with them, but here we are mainly talking about isc_dsql_describe, used to describe query result data.

isc_dsql_describe describes the columns into an XSQLDA structure with their XSQLVAR items. Then you pass that sqlda to isc_dsql_execute2 or isc_dsql_fetch. But you don't need to pass to then exactly SQLDA as returned by isc_dsql_describe.

You can change it. So you iterate in the sqlvar items looking for columns with SQL_TEXT and change them to SQL_VARYING.

Of course, if developer need to know metadata of query (is first field a CHAR field?), you will need to handle that in the driver instead of just query the sqlvar item that you have modified.

node-firebird-driver-native does this, but using the new Firebird API.

[cmb69]

@asfernandes, thanks for the explanation! I'll have a closer look at this, but I don't think that we can change that for any of the stable versions, so I'm re-classifying as feature request.

[cmb69]

Of course, if developer need to know metadata of query (is first field a CHAR field?), you will need to handle that in the driver instead of just query the sqlvar item that you have modified.

That wouldn't be an issue for now, since PDO_Firebird does not yet expose driver:decl_type or such.

So, the following patch might already be sufficient:

 ext/pdo_firebird/firebird_statement.c | 3 +++
 1 file changed, 3 insertions(+)

diff --git a/ext/pdo_firebird/firebird_statement.c b/ext/pdo_firebird/firebird_statement.c
index ad49147f02..e34485cf91 100644
--- a/ext/pdo_firebird/firebird_statement.c
+++ b/ext/pdo_firebird/firebird_statement.c
@@ -204,6 +204,9 @@ static int firebird_stmt_describe(pdo_stmt_t *stmt, int colno) /* {{{ */
 	int colname_len;
 	char *cp;
 
+	if ((var->sqltype & ~1) == SQL_TEXT) {
+		var->sqltype = SQL_VARYING | (var->sqltype & 1);
+	}
 	colname_len = (S->H->fetch_table_names && var->relname_length)
 					? (var->aliasname_length + var->relname_length + 1)
 					: (var->aliasname_length);

I'll try to set up PDO_Firebird tests in CI first, before proceeding with a PR.

[cmb69]

I'll try to set up PDO_Firebird tests in CI first, […]

I found some issues which need to be resolved first, so this may take a few more days.