SQL syntax error with MariaDB

[valentin-harrang]

Prerequisites

• I understand and accept the project's code of conduct.
• I have already searched in existing issues and found no previous report of this bug.

Describe the bug and add attachments

Some of my customers have a 500 error at the end of their order. I've tried to reproduce the problem, but I don't think it's for everyone, and I don't quite understand how it happens.

I use version 8.1.5 of Prestashop, my PHP version is 8.1 and I use version 10.5.21 of MariaDB.

Error log:

ERROR v8.1.5 2024/04/28 - 22:53:49: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND ccr.id_cart_rule = 40 AND o.id_order IS NULL LIMIT 1' at line 5 at line 151 in file classes/db/DbPDO.php

I think it's the same problem as here: #33157 (comment) But the error occurs with a SELECT instead of a DELETE.

The error seems to be in this query from classes/CartRule.php:

                $quantityUsed += (int) Db::getInstance()->getValue('
                    SELECT count(*)
                    FROM `' . _DB_PREFIX_ . 'cart_cart_rule` ccr
                    INNER JOIN `' . _DB_PREFIX_ . 'cart` c ON c.id_cart = ccr.id_cart
                    LEFT JOIN `' . _DB_PREFIX_ . 'orders` o ON o.id_cart = c.id_cart
                    WHERE c.id_customer = ' . $cart->id_customer . ' AND c.id_cart = ' . $cart->id . ' AND ccr.id_cart_rule = ' . (int) $this->id . ' AND o.id_order IS NULL
                ');

Expected behavior

No response

Steps to reproduce

Making an order with Prestashop 8.1.5.

If you run this SQL command by hand, you won't get the syntax error:

SELECT count(*) FROM `hd9W_cart_cart_rule` ccr INNER JOIN `hd9W_cart` c ON c.id_cart = ccr.id_cart LEFT JOIN `hd9W_orders` o ON o.id_cart = c.id_cart WHERE c.id_customer = 58 AND c.id_cart = 127 AND ccr.id_cart_rule = 40 AND o.id_order IS NULL;

PrestaShop version(s) where the bug happened

8.1.5

PHP version(s) where the bug happened

8.1

If your bug is related to a module, specify its name and its version

No response

Your company or customer's name goes here (if applicable).

No response

[cruftex]

@valentin-harrang
This is a different problem than #33157.

I can see that the error contains "LIMIT 1" but your test query below does not contain it.
Somehow something different is executed. Can you turn on full query logging and check what actually is send to the DB?

[valentin-harrang]

@cruftex thank you for the answer.

I added LIMIT 1 to the end of my query in PhpMyAdmin and it works fine.

SELECT count(*) FROM `hd9W_cart_cart_rule` ccr INNER JOIN `hd9W_cart` c ON c.id_cart = ccr.id_cart LEFT JOIN `hd9W_orders` o ON o.id_cart = c.id_cart WHERE c.id_customer = 58 AND c.id_cart = 127 AND ccr.id_cart_rule = 40 AND o.id_order IS NULL LIMIT 1;

I need to know the exact query, so I've added error_log in the CartRule.php file, but I can't reproduce the error, because when I place an order, it works fine on my end, but not for certain customers.

[cruftex]

@valentin-harrang
I recommend that you verify the actual query that the database receives. Maybe some database driver or database middleware is modifying things in between. Check and enable the error log and also the general log file. The general log file logs all queries, so do this temporarily in the less busy hour. See about the general query log here: https://mariadb.com/kb/en/general-query-log/

Best is you make a test order by yourself with a customer that is known to have problems. Just replace the password entry temporarily in the database. There are also modules that allow "Login as Customer" from the backoffice.

Try to reproduce the error in production by sending the query via the mysql client. Make sure you send the query exactly as the same user. The error messaged said, syntax error or access violation. Maybe there is nothing wrong with the syntax at all. Also double-check the configuration and look for differences with things interfering with queries, like query caching.

Good luck!