[BUGFIX] Use prepared statements for pdo_mysql per default 47/62047/7
authorManuel Selbach <manuel_selbach@yahoo.de>
Tue, 22 Oct 2019 16:07:44 +0000 (18:07 +0200)
committerOliver Hader <oliver.hader@typo3.org>
Thu, 24 Oct 2019 08:34:42 +0000 (10:34 +0200)
With this change prepared statements will be used per default for the
driver `pdo_mysql` to retrieve native data types.

Before emulated prepared statement were used in pdo_mysql,
which return all values as string.

Resolves: #87518
Releases: master, 9.5
Change-Id: I0f1e47cf7a30e47445452967aebf52e98f4413ad
Reviewed-on: https://review.typo3.org/c/Packages/TYPO3.CMS/+/62047
Reviewed-by: Oliver Hader <oliver.hader@typo3.org>
Reviewed-by: Alexander Schnitzler <review.typo3.org@alexanderschnitzler.de>
Tested-by: Oliver Hader <oliver.hader@typo3.org>
Tested-by: Alexander Schnitzler <review.typo3.org@alexanderschnitzler.de>
Build/Scripts/runTests.sh
Build/testing-docker/local/docker-compose.yml
typo3/sysext/core/Classes/Database/Driver/PDOMySql/Driver.php
typo3/sysext/core/Documentation/Changelog/9.5.x/Important-87518-UsePreparedStatementsForPdo_mysqlPerDefault.rst [new file with mode: 0644]
typo3/sysext/core/Documentation/Changelog/master/Important-87518-UsePreparedStatementsForPdo_mysqlPerDefault.rst [new file with mode: 0644]

index f1f3c9d..56b4abd 100755 (executable)
@@ -51,6 +51,9 @@ Options:
     -a <mysqli|pdo_mysql|sqlsrv|pdo_sqlsrv>
         Only with -s functional
         Specifies to use another driver, following combinations are available:
+            - mysql55
+                - mysqli (default)
+                - pdo_mysql
             - mariadb
                 - mysqli (default)
                 - pdo_mysql
@@ -88,10 +91,11 @@ Options:
             - unitJavascript: JavaScript unit tests
             - unitRandom: PHP unit tests in random order, add -o <number> to use specific seed
 
-    -d <mariadb|mssql|postgres|sqlite>
+    -d <mariadb|mysql55|mssql|postgres|sqlite>
         Only with -s install|functional
         Specifies on which DBMS tests are performed
             - mariadb (default): use mariadb
+            - mysql55: use MySQL 5.5 server
             - mssql: use mssql microsoft sql server
             - mssql2017latest: use latest version of microsoft sql server 2017
             - postgres: use postgres
@@ -392,6 +396,12 @@ case ${TEST_SUITE} in
                 docker-compose run functional_mariadb10
                 SUITE_EXIT_CODE=$?
                 ;;
+            mysql55)
+                [[ ! -z "$DATABASE_DRIVER" ]] && echo "Using driver: ${DATABASE_DRIVER}"
+                docker-compose run prepare_functional_mysql55
+                docker-compose run functional_mysql55
+                SUITE_EXIT_CODE=$?
+                ;;
             mssql)
                 [[ ! -z "$DATABASE_DRIVER" ]] && echo "Using driver: ${DATABASE_DRIVER}"
                 docker-compose run prepare_functional_mssql2017cu9
index dab7a7a..5b388a2 100644 (file)
@@ -3,6 +3,13 @@ services:
   chrome:
     image: selenium/standalone-chrome:3.12
 
+  mysql55:
+    image: mysql:5.5
+    environment:
+      MYSQL_ROOT_PASSWORD: funcp
+    tmpfs:
+      - /var/lib/mysql/:rw,noexec,nosuid
+
   mariadb10:
     image: mariadb:10.3
     environment:
@@ -541,6 +548,56 @@ services:
         fi
       "
 
+  prepare_functional_mysql55:
+    image: alpine:3.8
+    links:
+      - mysql55
+      - redis4
+      - memcached1-5
+    command: >
+      /bin/sh -c "
+        if [ ${SCRIPT_VERBOSE} -eq 1 ]; then
+          set -x
+        fi
+        echo Waiting for database start...;
+        while ! nc -z mysql55 3306; do
+          sleep 1;
+        done;
+        echo Database is up;
+      "
+  functional_mysql55:
+    image: typo3gmbh/${DOCKER_PHP_IMAGE}:latest
+    user: ${HOST_UID}
+    volumes:
+      - ${CORE_ROOT}:${CORE_ROOT}
+      - ${HOST_HOME}:${HOST_HOME}
+      - /etc/passwd:/etc/passwd:ro
+      - /etc/group:/etc/group:ro
+    environment:
+      typo3DatabaseDriver: "${DATABASE_DRIVER:-mysqli}"
+      typo3DatabaseName: func_test
+      typo3DatabaseUsername: root
+      typo3DatabasePassword: funcp
+      typo3DatabaseHost: mysql55
+      typo3TestingRedisHost: redis4
+      typo3TestingMemcachedHost: memcached1-5
+    working_dir: ${CORE_ROOT}
+    command: >
+      /bin/sh -c "
+        if [ ${SCRIPT_VERBOSE} -eq 1 ]; then
+          set -x
+        fi
+        php -v | grep '^PHP'
+        if [ ${PHP_XDEBUG_ON} -eq 0 ]; then
+          php -n -c /etc/php/cli-no-xdebug/php.ini \
+            vendor/phpunit/phpunit/phpunit -c vendor/typo3/testing-framework/Resources/Core/Build/FunctionalTests.xml ${EXTRA_TEST_OPTIONS} ${TEST_FILE};
+        else
+          DOCKER_HOST=`route -n | awk '/^0.0.0.0/ { print $$2 }'`
+          XDEBUG_CONFIG=\"remote_port=${PHP_XDEBUG_PORT} remote_enable=1 remote_host=$${DOCKER_HOST}\" \
+            vendor/phpunit/phpunit/phpunit -c vendor/typo3/testing-framework/Resources/Core/Build/FunctionalTests.xml ${EXTRA_TEST_OPTIONS} ${TEST_FILE};
+        fi
+      "
+
   prepare_functional_mssql2017cu9:
     image: alpine:3.8
     links:
index 7085742..98a426e 100644 (file)
@@ -39,6 +39,11 @@ class Driver extends DoctrinePDOMySqlDriver
                 $password,
                 $driverOptions
             );
+
+            // use prepared statements for pdo_mysql per default to retrieve native data types
+            if (!isset($driverOptions[\PDO::ATTR_EMULATE_PREPARES])) {
+                $conn->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);
+            }
         } catch (PDOException $e) {
             throw DBALException::driverException($this, $e);
         }
diff --git a/typo3/sysext/core/Documentation/Changelog/9.5.x/Important-87518-UsePreparedStatementsForPdo_mysqlPerDefault.rst b/typo3/sysext/core/Documentation/Changelog/9.5.x/Important-87518-UsePreparedStatementsForPdo_mysqlPerDefault.rst
new file mode 100644 (file)
index 0000000..442258f
--- /dev/null
@@ -0,0 +1,40 @@
+.. include:: ../../Includes.txt
+
+=====================================================================
+Important: #87518 - Use prepared statements for pdo_mysql per default
+=====================================================================
+
+See :issue:`87518`
+
+Description
+===========
+
+Before this adaption, the `pdo_mysql` driver used emulated prepared statements per default.
+With that, all returned values of a query were strings.
+
+With this change the behavior changes to use the actual prepared statements,
+which return native data types. Thus, if a column is defined as INTEGER,
+the returned value in PHP will also be an INTEGER.
+
+It is possible to deactivate this feature as follows:
+
+You need to "overwrite" the option to set `PDO::ATTR_EMULATE_PREPARES`
+(reference: https://www.php.net/manual/en/pdo.setattribute.php) in your database connection:
+
+.. code-block:: php
+
+   'Connections' => [
+       'Default' => [
+           'dbname' => 'some_database_name',
+           'driver' => 'pdo_mysql',
+           'driverOptions' => [
+               \PDO::ATTR_EMULATE_PREPARES => true
+            ],
+           'password' => 's0meS3curePW!',
+           'user' => 'someUser',
+       ],
+   ],
+
+.. Please keep in mind to update the rst file in 10.2.
+
+.. index:: Database, ext:core
diff --git a/typo3/sysext/core/Documentation/Changelog/master/Important-87518-UsePreparedStatementsForPdo_mysqlPerDefault.rst b/typo3/sysext/core/Documentation/Changelog/master/Important-87518-UsePreparedStatementsForPdo_mysqlPerDefault.rst
new file mode 100644 (file)
index 0000000..533c4a6
--- /dev/null
@@ -0,0 +1,40 @@
+.. include:: ../../Includes.txt
+
+=====================================================================
+Important: #87518 - Use prepared statements for pdo_mysql per default
+=====================================================================
+
+See :issue:`87518`
+
+Description
+===========
+
+Before this adaption, the `pdo_mysql` driver used emulated prepared statements per default.
+With that, all returned values of a query were strings.
+
+With this change the behavior changes to use the actual prepared statements,
+which return native data types. Thus, if a column is defined as INTEGER,
+the returned value in PHP will also be an INTEGER.
+
+It is possible to deactivate this feature as follows:
+
+You need to "overwrite" the option to set `PDO::ATTR_EMULATE_PREPARES`
+(reference: https://www.php.net/manual/en/pdo.setattribute.php) in your database connection:
+
+.. code-block:: php
+
+   'Connections' => [
+       'Default' => [
+           'dbname' => 'some_database_name',
+           'driver' => 'pdo_mysql',
+           'driverOptions' => [
+               \PDO::ATTR_EMULATE_PREPARES => true
+            ],
+           'password' => 's0meS3curePW!',
+           'user' => 'someUser',
+       ],
+   ],
+
+.. Please keep in mind to update the rst file in 9.5.x.
+
+.. index:: Database, ext:core