Bug #81688 [Com]: PDO_ODBC doesn't handle fixed-length character columns with character conversio

Edit report at https://bugs.php.net/bug.php?id=81688&edit=1 ID: 81688 Comment by: kadler at us dot ibm dot com Reported by: calvin at cmpct dot info Summary: PDO_ODBC doesn't handle fixed-length character columns with character conversio Status: Open Type: Bug Package: ODBC related Operating System: IBM i 7.2 PHP Version: 8.0.13 Block user comment: N Private report: N New Comment: I know my colleague has done some investigation here as to how different databases/drivers react when running in different encodings between the database and the client. IIRC most drivers don't do any conversion and just give back the data in the original encoding (which works "ok" for the most part when everyone is some ASCII variant, but not so when one is EBCDIC). As to how the PHP code works using SQL_DESC_DISPLAY_SIZE, this is the docs: https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlcolattribute-function?view=sql-server-ver15 "SQL_DESC_DISPLAY_SIZE - Maximum number of characters required to display data from the column. For more information about display size, see Column Size, Decimal Digits, Transfer Octet Length, and Display Size in Appendix D: Data Types." Of course, the link to the Appendix isn't any more enlightening: https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/column-size-decimal-digits-transfer-octet-length-and-display-size?view=sql-server-ver15 "The display size value for all data types corresponds to the value in a single descriptor field, SQL_DESC_DISPLAY_SIZE." In the straightforward reading of the spec, I suppose one could argue that the driver should account for any encoding conversion of the data that it will do. Previous Comments: ------------------------------------------------------------------------ [2021-12-03 19:05:15] calvin at cmpct dot info I agree overallocation is ugly and something we want to avoid if we can help it. SQL_DESC_OCTET_LENGTH is the same as the display length for the column (in this case, 30) when I write a program to confirm this in C. I'm guessing this is technically correct because it's what it's true for how the database may be representing it, but not after the driver does conversion for the client's encoding. My comment about the overflow is more about how there's garbage at the end there; I suspect it's more not truncating and letting it go out reading stuff in the abyss. Or I'm also reading the situation wrong, and it's reallocating the buffer, just not doing it correctly in a way that leaves garbage. Orrrrrrr the driver isn't truncating properly; either way, I'll probably bug the IBM people responsible for the driver and get their read on the situation. (Apologies if you see dupes, bugs.php keeps timing out...) ------------------------------------------------------------------------ [2021-12-03 18:03:22] cmb@php.net Well, the relevant differenec to the Node ODBC library is apparently that PDO does not rely on SQLDescribeCol() to detect the size, but rather on SQLColAttribute(SQL_DESC_DISPLAY_SIZE)[1]. Whether that is *supposed* to return the "correct" length is not clear to me. Could you please check what SQLColAttribute(SQL_DESC_OCTET_LENGTH) would yield? Might be the same value as SQL_DESC_DISPLAY_SIZE, but maybe not. > there's seemingly a possibility of a buffer overflow I don't think that is possible, since the buffer size is passed to SQLBindCol(), so only truncation may happen. Note that I'm not generally against overallocation, but would try to avoid it for the general case. Some setting (not necessarily INI) might be an option. But please check SQL_DESC_OCTET_LENGTH first. [1] <https://github.com/php/php-src/blob/php-8.0.13/ext/pdo_odbc/odbc_stmt.c#L604> ------------------------------------------------------------------------ [2021-12-02 18:30:32] calvin at cmpct dot info Description: ------------ (was kinda wanting to wait until the transition to GH issues, but deciding to post this now... let me know if i should) It seems PDO_ODBC doesn't handle situations where if characters are converted as part of a data bind. For example, if the CHAR(30) column on the database stores as one encoding and it gets converted to something requiring more bytes to represent, there's seemingly a possibility of a buffer overflow (what looks like to me, anyways). The example script has one such example; an é takes one byte to represent in some encodings, and two in UTF-8. Db2 is a pathological case because most columns are stored as some kind of EBCDIC encoding and gets converted to usually UTF-8 on the client, but it's quite possible for other databases too (i.e. ISO-8859-1 columns to UTF-8). The example I posted would probably trigger the same issue if the driver is converting. Looking at the unixODBC trace a user provided, I can see PHP just allocates what the driver claims is the CHAR(n) size plus one. PHP PDO_ODBC: ``` [ODBC][24294][1638369671.619132][SQLDescribeCol.c][504] Exit:[SQL_SUCCESS] Column Name = [VARTE1] Data Type = 70000000009b318 -> 1 Column Size = fffffffffffce78 -> 30 (64 bits) Decimal Digits = NULLPTR Nullable = NULLPTR [ODBC][24294][1638369671.619175][SQLColAttribute.c][294] Entry: Statement = 180847ad0 Column Number = 1 Field Identifier = SQL_DESC_DISPLAY_SIZE Character Attr = 0 Buffer Length = 0 String Length = 0 Numeric Attribute = fffffffffffce70 [ODBC][24294][1638369671.619219][SQLColAttribute.c][709] Exit:[SQL_SUCCESS] [ODBC][24294][1638369671.619261][SQLBindCol.c][245] Entry: Statement = 180847ad0 Column Number = 1 Target Type = 1 SQL_CHAR Target Value = 700000000056540 Buffer Length = 31 StrLen Or Ind = 70000000009b310 [ODBC][24294][1638369671.619304][SQLBindCol.c][353] Exit:[SQL_SUCCESS] ``` For comparison, Node ODBC library: ``` [ODBC][24271][1638369403.771225][SQLDescribeCol.c][504] Exit:[SQL_SUCCESS] Column Name = [VARTE1] Data Type = 1805796a4 -> 1 Column Size = 1805796a8 -> 30 (64 bits) Decimal Digits = 1805796b0 -> 0 Nullable = 1805796c0 -> 0 [ODBC][24271][1638369403.771312][SQLBindCol.c][245] Entry: Statement = 180552e70 Column Number = 1 Target Type = 1 SQL_CHAR Target Value = 180583130 Buffer Length = 121 StrLen Or Ind = 1805437d0 [ODBC][24271][1638369403.771392][SQLBindCol.c][353] Exit:[SQL_SUCCESS] ``` It looks to be overallocating, per: https://github.com/markdirish/node-odbc/blob/master/src/odbc_connection.cpp#L1659 ibm_db2 actually has a flag (i5_dbcs_alloc) in case of these situations. It is a bit crude, but seeing other ODBC consumers do it is kind of suggesting to me it may be a valid solution. The other option might be to reallocate more aggressively if the buffer is inadequate. I'm unsure how tricky that'd be to implement. The annoying option for users but simplest to prevent crashes in PHP is to emit a warning if the driver is trying to return more that what PHP allocated. Test script: --------------- <?php // SQL to repro create table calvin.bigchars( x char(30) ); // character that must be representable as a single char in DB but expanded on client (i.e. accented e in ISO-8859 or EBCDIC is multiple bytes in UTF-8) insert into calvin.bigchars values('éééééééééééééééééééééééééééééé'); // 30 insert into calvin.bigchars values('ééééééééééééééé'); // 15 insert into calvin.bigchars values('ééééé'); // 5 // End SQL //Connect to IBM i $user = 'user'; $pw = 'password'; // CCSID 1208 for UTF-8, 819 for ISO-8859-1 $dsn = 'Driver={IBM i Access ODBC Driver};System=127.0.0.1;AlwaysCalculateResultLength=1;CCSID=1208'; $persistence = false; // create pdo odbc connection try { $pdo_odbc = new PDO("odbc:$dsn", $user, $pw, array( PDO::ATTR_PERSISTENT => $persistence, PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING, )); } catch (PDOException $e) { die($e->getMessage()); } $sql = "select * from calvin.bigchars"; $pdoStmt = $pdo_odbc->prepare($sql); echo "before PDO\n"; var_dump($pdoStmt->execute()); echo "after PDO\n"; while($record = $pdoStmt->fetch(PDO::FETCH_ASSOC)){ echo "Record\n"; var_dump($record["X"]); } Actual result: -------------- before PDO bool(true) after PDO Record string(60) "éééééééééééééééT`� ��sql" Record string(45) "éééééééééééééééT`� " Record string(35) "ééééé " ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=81688&edit=1