Issue with a module under PHP 7.4 and TB 1.4

[movieseals]

Hi!

 

I am testing TB 1.4 using PHP 7.4.

One of the modules which works fine under PHP 7.3 now gives me this error in 7.4:

Any idea how to resolve this?

ThirtyBeesDatabaseException

Unknown column 'pps.id_shop' in 'field list'

in file /home/librairiezbookstore.com/public_html/modules/ets_abandonedcart/controllers/admin/AdminEtsACDashboardController.php at line 256

SQL

            SELECT SUM((tmp.price + tmp.attribute_price)* pcp.quantity)
            FROM `tb_cart` c
            LEFT JOIN `tb_cart_product` pcp ON pcp.id_cart = c.id_cart
            LEFT JOIN (
                SELECT p.id_product
                    , pps.id_shop
                    , pps.`price`
                    , pps.`ecotax`
                    , 0 as id_product_attribute, 0 as attribute_price
                FROM `tb_product` p
                 
            ) as tmp ON (pcp.id_product = tmp.id_product AND pcp.id_product_attribute = tmp.id_product_attribute AND tmp.id_shop = c.id_shop)
            LEFT JOIN `tb_ets_abancart_tracking` eat ON (eat.id_cart = c.id_cart)
            WHERE c.id_shop = 1 AND (eat.id_cart is NOT NULL OR eat.id_cart > 0) LIMIT 1

Source file: /home/librairiezbookstore.com/public_html/modules/ets_abandonedcart/controllers/admin/AdminEtsACDashboardController.php

237:        $qr = '
238:            SELECT SUM((tmp.price + tmp.attribute_price)* pcp.quantity)
239:            FROM `' . _DB_PREFIX_ . 'cart` c
240:            LEFT JOIN `' . _DB_PREFIX_ . 'cart_product` pcp ON pcp.id_cart = c.id_cart
241:            LEFT JOIN (
242:                SELECT p.id_product
243:                    , pps.id_shop
244:                    , pps.`price`
245:                    , pps.`ecotax`
246:                    , ' . ($is_feature_active ? 'IFNULL(ppas.id_product_attribute, 0) id_product_attribute, ppas.`price` as attribute_price' : '0 as id_product_attribute, 0 as attribute_price') . '
247:                FROM `' . _DB_PREFIX_ . 'product` p
248:                 ' . ($is_feature_active ? '
249:                    LEFT JOIN `' . _DB_PREFIX_ . 'product_shop` pps on (p.id_product = pps.id_product)
250:                    LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute_shop` ppas on (p.id_product = ppas.id_product)
251:                ' : '') . '
252:            ) as tmp ON (pcp.id_product = tmp.id_product AND pcp.id_product_attribute = tmp.id_product_attribute AND tmp.id_shop = c.id_shop)
253:            LEFT JOIN `' . _DB_PREFIX_ . 'ets_abancart_tracking` eat ON (eat.id_cart = c.id_cart)
254:            WHERE c.id_shop = ' . (int)$this->context->shop->id . ' AND (eat.id_cart is NOT NULL OR eat.id_cart > 0);
255:        ';
256:        $totalAbandonedCart = Tools::displayPrice((float)Db::getInstance()->getValue($qr), $currency);
257:        /*---End Abandoned Carts---*/
258:
259:        /*---Generated voucher code---*/
260:        $toSQL = '
261:			SELECT COUNT(t.id_cart_rule) FROM (SELECT id_cart_rule FROM `' . _DB_PREFIX_ . 'ets_abancart_tracking` t WHERE t.id_shop=' . (int)$this->context->shop->id . ') as t
262:			INNER JOIN `' . _DB_PREFIX_ . 'cart_rule` cr ON (cr.id_cart_rule = t.id_cart_rule)
263:		';
264:        $totalGeneratedCode = (int)Db::getInstance()->getValue($toSQL);
265:        $totalUseGeneratedCode = (int)Db::getInstance()->getValue($toSQL . '
266:			LEFT JOIN `' . _DB_PREFIX_ . 'cart_cart_rule` ccr ON (ccr.id_cart_rule = cr.id_cart_rule)

Stack trace

1. classes/db/Db.php:867 source ()

848:     * @throws PrestaShopException
849:     * @deprecated 1.1.1
850:     */
851:    protected function q($sql, $useCache = true)
852:    {
853:        Tools::displayAsDeprecated();
854:        return $this->query($sql);
855:    }
856:
857:    /**
858:     * Displays last SQL error
859:     *
860:     * @param string|bool $sql
861:     * @throws PrestaShopDatabaseException
862:     */
863:    public function displayError($sql = false)
864:    {
865:        $errno = $this->getNumberError();
866:        if ($errno) {
867:            throw new PrestaShopDatabaseException($this->getMsgError(), $sql);
868:        }
869:    }
870:
871:    /**
872:     * Sanitize data which will be injected into SQL query
873:     *
874:     * @param string $string SQL data which will be injected into SQL query
875:     * @param bool   $htmlOk Does data contain HTML code ? (optional)
876:     * @param bool   $bqSql  Escape backquotes
877:     *

2. classes/db/Db.php:502 source DbCore->displayError()

483:     * @return bool|PDOStatement
484:     * @throws PrestaShopDatabaseException
485:     * @throws PrestaShopException
486:     */
487:    public function query($sql)
488:    {
489:        if ($sql instanceof DbQuery) {
490:            $sql = $sql->build();
491:        }
492:
493:        $this->result = $this->_query($sql);
494:
495:        if ($this->result === false && $this->getNumberError() == 2006) {
496:            if ($this->connect()) {
497:                $this->result = $this->_query($sql);
498:            }
499:        }
500:
501:        if ($this->result === false && $this->throwOnError) {
502:            $this->displayError($sql);
503:        }
504:
505:        return $this->result;
506:    }
507:
508:    /**
509:     * Executes an INSERT query
510:     *
511:     * @param string $table      Table name without prefix
512:     * @param array  $data       Data to insert as associative array. If $data is a list of arrays, multiple insert will be done

3. classes/db/Db.php:789 source DbCore->query()

770:     * This function automatically adds "LIMIT 1" to the query
771:     *
772:     * @param string|DbQuery $sql      the select query (without "LIMIT 1")
773:     * @param bool           $useCache Deprecated, the internal query cache is no longer used
774:     *
775:     * @return array|false
776:     * @throws PrestaShopDatabaseException
777:     * @throws PrestaShopException
778:     */
779:    public function getRow($sql, $useCache = true)
780:    {
781:        if ($sql instanceof DbQuery) {
782:            $sql = $sql->build();
783:        }
784:
785:        $sql = rtrim($sql, " \t\n\r\0\x0B;").' LIMIT 1';
786:        $this->result = false;
787:        $this->last_query = $sql;
788:
789:        $this->result = $this->query($sql);
790:        if (!$this->result) {
791:            $result = false;
792:        } else {
793:            $result = $this->nextRow($this->result);
794:        }
795:
796:        $this->last_cached = false;
797:
798:        if (! is_array($result)) {
799:            $result = false;

4. classes/db/Db.php:816 source DbCore->getRow()

797:
798:        if (! is_array($result)) {
799:            $result = false;
800:        }
801:
802:        return $result;
803:    }
804:
805:    /**
806:     * Returns a value from the first row, first column of a SELECT query
807:     *
808:     * @param string|DbQuery $sql
809:     * @param bool           $useCache Deprecated, the internal query cache is no longer used
810:     *
811:     * @return mixed
812:     * @throws PrestaShopException
813:     */
814:    public function getValue($sql, $useCache = true)
815:    {
816:        if (!$result = $this->getRow($sql, $useCache)) {
817:            return false;
818:        }
819:
820:        return array_shift($result);
821:    }
822:
823:    /**
824:     * Get number of rows for last result
825:     *
826:     * @return int

5. modules/ets_abandonedcart/controllers/admin/AdminEtsACDashboardController.php:256 source DbCore->getValue()

237:        $qr = '
238:            SELECT SUM((tmp.price + tmp.attribute_price)* pcp.quantity)
239:            FROM `' . _DB_PREFIX_ . 'cart` c
240:            LEFT JOIN `' . _DB_PREFIX_ . 'cart_product` pcp ON pcp.id_cart = c.id_cart
241:            LEFT JOIN (
242:                SELECT p.id_product
243:                    , pps.id_shop
244:                    , pps.`price`
245:                    , pps.`ecotax`
246:                    , ' . ($is_feature_active ? 'IFNULL(ppas.id_product_attribute, 0) id_product_attribute, ppas.`price` as attribute_price' : '0 as id_product_attribute, 0 as attribute_price') . '
247:                FROM `' . _DB_PREFIX_ . 'product` p
248:                 ' . ($is_feature_active ? '
249:                    LEFT JOIN `' . _DB_PREFIX_ . 'product_shop` pps on (p.id_product = pps.id_product)
250:                    LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute_shop` ppas on (p.id_product = ppas.id_product)
251:                ' : '') . '
252:            ) as tmp ON (pcp.id_product = tmp.id_product AND pcp.id_product_attribute = tmp.id_product_attribute AND tmp.id_shop = c.id_shop)
253:            LEFT JOIN `' . _DB_PREFIX_ . 'ets_abancart_tracking` eat ON (eat.id_cart = c.id_cart)
254:            WHERE c.id_shop = ' . (int)$this->context->shop->id . ' AND (eat.id_cart is NOT NULL OR eat.id_cart > 0);
255:        ';
256:        $totalAbandonedCart = Tools::displayPrice((float)Db::getInstance()->getValue($qr), $currency);
257:        /*---End Abandoned Carts---*/
258:
259:        /*---Generated voucher code---*/
260:        $toSQL = '
261:			SELECT COUNT(t.id_cart_rule) FROM (SELECT id_cart_rule FROM `' . _DB_PREFIX_ . 'ets_abancart_tracking` t WHERE t.id_shop=' . (int)$this->context->shop->id . ') as t
262:			INNER JOIN `' . _DB_PREFIX_ . 'cart_rule` cr ON (cr.id_cart_rule = t.id_cart_rule)
263:		';
264:        $totalGeneratedCode = (int)Db::getInstance()->getValue($toSQL);
265:        $totalUseGeneratedCode = (int)Db::getInstance()->getValue($toSQL . '
266:			LEFT JOIN `' . _DB_PREFIX_ . 'cart_cart_rule` ccr ON (ccr.id_cart_rule = cr.id_cart_rule)

6. modules/ets_abandonedcart/controllers/admin/AdminEtsACDashboardController.php:203 source AdminEtsACDashboardController->initBlock()

184:
185:    public function initToolbar()
186:    {
187:    }
188:
189:    public function initProcess()
190:    {
191:        parent::initProcess();
192:
193:        if (null == $this->display) {
194:            $this->display = 'list';
195:        }
196:    }
197:
198:    public function initContent()
199:    {
200:        parent::initContent();
201:
202:        if ($this->display == 'list')
203:            $this->initBlock();
204:    }
205:
206:    public function setMedia($isNewTheme = false)
207:    {
208:        parent::setMedia($isNewTheme);
209:
210:        $this->addJS(array(
211:            $this->mPath . 'views/js/chart.admin.js',
212:        ));
213:    }

7. classes/controller/Controller.php:215 source AdminEtsACDashboardController->initContent()

196:    public function run()
197:    {
198:        $this->init();
199:        if ($this->checkAccess()) {
200:            if (!$this->content_only && ($this->display_header || (isset($this->className) && $this->className))) {
201:                $this->setMedia();
202:            }
203:
204:            $this->postProcess();
205:
206:            if (!empty($this->redirect_after)) {
207:                $this->redirect();
208:            }
209:
210:            if (!$this->content_only && ($this->display_header || (isset($this->className) && $this->className))) {
211:                $this->initHeader();
212:            }
213:
214:            if ($this->viewAccess()) {
215:                $this->initContent();
216:            } else {
217:                $this->errors[] = Tools::displayError('Access denied.');
218:            }
219:
220:            if (!$this->content_only && ($this->display_footer || (isset($this->className) && $this->className))) {
221:                $this->initFooter();
222:            }
223:
224:            if ($this->ajax) {
225:                $action = Tools::toCamelCase(Tools::getValue('action'), true);

8. classes/Dispatcher.php:861 source ControllerCore->run()

842:                    include_once($retrocompatibilityAdminTab);
843:                    include_once(_PS_ADMIN_DIR_.'/functions.php');
844:                    runAdminTab($this->controller, !empty($_REQUEST['ajaxMode']));
845:
846:                    return;
847:                }
848:                break;
849:
850:            default:
851:                throw new PrestaShopException('Bad front controller chosen');
852:        }
853:
854:        // Instantiate controller
855:        $controller = ServiceLocator::getInstance()->getController($controllerClass);
856:
857:        // Execute hook dispatcher
858:        Hook::exec('actionDispatcher', $paramsHookActionDispatcher);
859:
860:        // Running controller
861:        $controller->run();
862:    }
863:
864:    /**
865:     * Retrieve the controller from url or request uri if routes are activated
866:     *
867:     * @param int|null $idShop
868:     *
869:     * @return string
870:     *
871:     * @throws PrestaShopException

9. override/classes/Dispatcher.php:39 source DispatcherCore->dispatch()

20: *  International Registered Trademark & Property of ETS-Soft
21: */
22:class Dispatcher extends DispatcherCore
23:{
24:    /*
25:    * module: ets_superspeed
26:    * date: 2021-02-28 16:53:39
27:    * version: 1.2.4
28:    */
29:    public function dispatch() {
30:        if(@file_exists(dirname(__FILE__).'/../../modules/ets_superspeed/ets_superspeed.php'))
31:        {
32:            require_once(dirname(__FILE__).'/../../modules/ets_superspeed/ets_superspeed.php');
33:            if($cache = Ets_superspeed::displayContentCache(true))
34:            {
35:                echo $cache;
36:                exit;
37:            }
38:        }
39:        parent::dispatch();
40:    }
41:}

10. smtgitb9e0nbr7r0/index.php:58 source Dispatcher->dispatch()

39:if (!defined('PS_ADMIN_DIR')) {
40:    define('PS_ADMIN_DIR', _PS_ADMIN_DIR_);
41:}
42:
43:// For retrocompatibility with "tab" parameter
44:if (!isset($_GET['controller']) && isset($_GET['tab']) && is_string($_GET['tab'])) {
45:    $_GET['controller'] = strtolower($_GET['tab']);
46:}
47:if (!isset($_POST['controller']) && isset($_POST['tab']) && is_string($_POST['tab'])) {
48:    $_POST['controller'] = strtolower($_POST['tab']);
49:}
50:if (!isset($_REQUEST['controller']) && isset($_REQUEST['tab']) && is_string($_REQUEST['tab'])) {
51:    $_REQUEST['controller'] = strtolower($_REQUEST['tab']);
52:}
53:
54:require(_PS_ADMIN_DIR_.'/../config/config.inc.php');
55:require(_PS_ADMIN_DIR_.'/functions.php');
56:
57:// Prepare and trigger admin dispatcher
58:Dispatcher::getInstance()->dispatch();

[datakick]

I don't think this is php7.3 vs php7.4 issue. More likely tb1.3 vs tb1.4 issue.

In tb1.4 we no longer silently ignore invalid SQL queries, but we throw exceptions instead.

This module generates and executes invalid SQL query. In tb1.3, line

$totalAbandonedCart = Tools::displayPrice((float)Db::getInstance()->getValue($qr), $currency);

would result with $totalAbandonedCart variable containing 0.0.

The same result would be if you executed this:

$totalAbandonedCart = Tools::displayPrice((float)Db::getInstance()->getValue("SELECT 1 FROM table_that_does_not_exists"), $currency);

In TB1.4 this throws exception. There is obviously something fishy in the code, and we shouldn't try hide it.

If you need previous functionality, you can enable it in Advanced Parameters > Performance. I strongly advice against that. You should contact module author and have this fixed instead. 

[movieseals]

Thank you very much for the explanation - at least it is a direction for a solution!