Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-5615

Error when binding NULL to nullable parameter in a prepared statement with PDO PostgreSQL ODBC client

    Details

    • Type: Bug
    • Status: Resolved (View Workflow)
    • Priority: Major
    • Resolution: Done
    • Affects Version/s: 11.0, 11.2
    • Fix Version/s: 11.2.2, 12.0.1, 12.1
    • Component/s: ODBC
    • Labels:
      None
    • Environment:
    • Steps to Reproduce:
      Hide

      1. Use Docker to install and configure the following containers

      a. PostgreSQL database (library/postgres:latest)
      b. Teiid with Wildfly (jboss/teiid:latest)
      c. Apache/PHP (php:7.1-apache)

      2. For Teiid deploy JDBC driver for PostgreSQL (latest from Maven) and create a datasource in standalone-teiid.xml

      3. For PHP, install PDO extension (pdo_pgsql) for PostgreSQL, in Dockerfile, using docker-php-ext-install, for example.

      4. Create a table in Postgres DB with an optional (nullable) field

      CREATE TABLE public.foobar (
      	id int4 NOT NULL,
      	optional varchar NULL,
      	CONSTRAINT foobar_pk PRIMARY KEY (id)
      );
      

      5. In Teiid create and deploy a VDB with a model using the above Postgres datasource.

      6. In PHP try to create a prepared statement for an INSERT into the table created above and bind a null value to the optional parameter.

      $dbh = new PDO('pgsql:host=<teiid_host>;port=35432;dbname=<vdb_name>;user=<teiid_user>;password=<teiid_password>');
      $stmt = $dbh->prepare("INSERT INTO foobar (id, optional) VALUES(?, ?)");
      $stmt->bindValue(1, 1);
      $stmt->bindValue(2, null, PDO::PARAM_NULL);
      $stmt->execute();
      

      The exception is thrown.

      As a temporary fix we changed the code in org.teiid.transport.PgFrontendProtocol class method "buildBind" to

      // ...
      for (int i = 0; i < paramCount; i++) {
                  int paramLen = data.readInt();
                  
                  // do not create an array for paramLen = -1
                  if (paramLen == -1){
                  	continue;
      	    }
      // ...
      }
      
      

      After deploying this change (in a JAR teiid-runtime-11.2.0.jar) to WildFly the binding seems to work.

      Show
      1. Use Docker to install and configure the following containers a. PostgreSQL database (library/postgres:latest) b. Teiid with Wildfly (jboss/teiid:latest) c. Apache/PHP (php:7.1-apache) 2. For Teiid deploy JDBC driver for PostgreSQL (latest from Maven) and create a datasource in standalone-teiid.xml 3. For PHP, install PDO extension (pdo_pgsql) for PostgreSQL, in Dockerfile, using docker-php-ext-install, for example. 4. Create a table in Postgres DB with an optional (nullable) field CREATE TABLE public .foobar ( id int4 NOT NULL , optional varchar NULL , CONSTRAINT foobar_pk PRIMARY KEY (id) ); 5. In Teiid create and deploy a VDB with a model using the above Postgres datasource. 6. In PHP try to create a prepared statement for an INSERT into the table created above and bind a null value to the optional parameter. $dbh = new PDO( 'pgsql:host=<teiid_host>;port=35432;dbname=<vdb_name>;user=<teiid_user>;password=<teiid_password>' ); $stmt = $dbh ->prepare("INSERT INTO foobar (id, optional) VALUES(?, ?)"); $stmt ->bindValue(1, 1); $stmt ->bindValue(2, null , PDO::PARAM_NULL); $stmt ->execute(); The exception is thrown. As a temporary fix we changed the code in org.teiid.transport.PgFrontendProtocol class method "buildBind" to // ... for ( int i = 0; i < paramCount; i++) { int paramLen = data.readInt(); // do not create an array for paramLen = -1 if (paramLen == -1){ continue ; } // ... } After deploying this change (in a JAR teiid-runtime-11.2.0.jar) to WildFly the binding seems to work.

      Description

      On Teiid 11.0 (and probably later versions, as well) running in a WildFly there is a problem when binding to a NULLABLE parameter in a prepared statement with NULL value when running against a PostgreSQL source from a PDO (PHP) ODBC client.

      PDO calls causing the error are related to PDOStatement::bindValue
      and PDOStatement::bindParam.

      It occurs exclusively when the client is trying to bind a NULL value (corresponding to a NULLABLE column in a table) to the prepared statement using PDO::PARAM_NULL type.

      Example PHP code

      $dbh = new PDO('pgsql:host=<teiid_host>;port=35432;dbname=<vdb_name>;user=<teiid_user>;password=<teiid_password>');
      $stmt = $dbh->prepare("INSERT INTO foobar (id, optional) VALUES(?, ?)");
      $stmt->bindValue(1, 1);
      $stmt->bindValue(2, null, PDO::PARAM_NULL);
      $stmt->execute();
      

      Exception thrown is

      Caused by: java.lang.NegativeArraySizeException
      at org.teiid.transport.PgFrontendProtocol.createByteArray(PgFrontendProtocol.java:328)
      at org.teiid.transport.PgFrontendProtocol.buildBind(PgFrontendProtocol.java:266)
      at org.teiid.transport.PgFrontendProtocol.createRequestMessage(PgFrontendProtocol.java:153)
      at org.teiid.transport.PgFrontendProtocol.decode(PgFrontendProtocol.java:133)
      at io.netty.handler.codec.ByteToMessageDecoder.callDecode(ByteToMessageDecoder.java:411)
      ... 15 more

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  shawkins Steven Hawkins
                  Reporter:
                  george.ushakov George Ushakov
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  2 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: