cphalcon: [BUG]: The implementation of the findFirst method in Phalcon\Mvc\Model class seems to have a bug."

Questions? Forum: https://phalcon.io/forum or Discord: https://phalcon.io/discord

Describe the bug I encountered an error when passing a search query with the string ‘notif_code’ as an argument to the findFirst method of the Phalcon\Mvc\Model object. The error message is as follows:

“Column ‘if_code’ doesn’t belong to any of the selected models (1), when preparing: SELECT [Notif].* FROM [Notif] WHERE notif_code = 5 AND account_id = :account_id: AND other_account_id = :other_account_id: LIMIT :APL0:”

Since the actual column ‘if_code’ does not exist in the table, it seems that within the implementation of the findFirst method, there might be a bug where the string ‘notif_code’ is modified, possibly removing the ‘not’ keyword for processing in SQL. As a result, ‘notif_code’ may have been transformed into ‘if_code,’ leading to the passing of a nonexistent column name ‘if_code’ in the generated low-level SQL statement.

To Reproduce If you add a column containing the word ‘not’ to any table and specify it as a search condition in the findFirst method, you will notice an attempt to access the column name with the ‘not’ removed. For example, the column name ‘notification’ would be recognized as ‘ification’ within the findFirst method.

Provide minimal script to reproduce the issue

class Notif extends \Phalcon\Mvc\Model
{
    /**
     *
     * @var integer
     */
    public $id;

    /**
     *
     * @var integer
     */
    public $notif_code;
}

$notif =  Notif::findFirst(['notif_code = 0']); // this produces an error.
if  ($notif) doSomething();
else doSomethingElse();

//$notif2 = Notif::findFirstByNotifCode(0); // Magic method version doesn't produces an error.

Expected behavior The error, ‘Column ‘if_code’ doesn’t belong to any of the selected models,’ should not occur.

$notif = Notif::findFirst(['notif_code = 0']);
if  ($notif) doSomething();
else doSomethingElse();

Details

Phalcon version: 5.5.0
PHP Version: 8.0.30
Operating System: Rocky Linux release 9.3 (Blue Onyx)
Installation type: Compiling from source
Zephir version (if any): 0.18.0
Server: Nginx 1.20.1
Other related info (Database, table schema): postgresql CREATE TABLE notif ( id bigint DEFAULT nextval(‘notif_id_seq’::regclass) NOT NULL , notif_code integer NOT NULL , CONSTRAINT pk_notif PRIMARY KEY ( id ) );

About this issue

Original URL
State: open
Created 6 months ago
Comments: 24 (5 by maintainers)

Most upvoted comments

Thanks for your feedback. Normally, keywords are separated by spaces. For instance, in cases like ‘notice’ where ‘not’ and ‘ice’ are together without spaces, there shouldn’t be an issue. Also, in Phalcon 4, there wasn’t such a problem as it is now.

whooperlove on Jan 9, 2024

not is a reserved word for Zephir and PHQL. As such, the parser does not know if this is part of your filed name or an actual word used in the framework.

Change your findFirst like this:

$notif =  Notif::findFirst(['[notif_code] = 0']);

Enclosing the field in square brackets will tell the parser that this is the name of a field and not part of the statement.

niden on Jan 9, 2024

I’ve presented a proposal that I think will solve the problem, and I hope the developers will start working on it soon.

s-ohnishi on Mar 15, 2024

@niden @whooperlove I agree that users use [ ] as a precaution, but I disagree that the system cannot function without [ ]. This is clearly different from the original behavior of SQL. And there is no reason why NOT BETWEEN should be treated specially as a token, so scanner.re should be modified. (HINTEGER should also be fixed)

s-ohnishi on Mar 1, 2024

You mentioned that the problem has been present since the implementation of PHQL 10 years ago, but I find two peculiar points. First, when accessing notif_code in the format of a magic method, such as ‘Notif::findFirstByNotifCode(0),’ no issues seem to arise. Perhaps it internally handles the square brackets [] differently? Second, as mentioned in the previous comment, there were no problems with the same code in Phalcon version 4. My project was created before this issue emerged, and it only occurred after applying Phalcon version 5, making me aware of the need for reserved word escaping.

whooperlove on Jan 25, 2024

Even though it is a reserved word, it is very troublesome if it is determined by partial match. I also think it’s an incorrect implementation.

s-ohnishi on Jan 25, 2024

The reason these bugs arise or if you like difference in behavior, is the changes in PHP and their internal engine. We all had to adjust to new functionality and adjusted our methods. This one appeared with PHP 8 it seems.

Note that you don’t need to enclose all your field names with square brackets. The ones prefixed with not are the most common ones (note, notes, notifications etc.)

I don’t know C so I cannot dive in and diagnose and potentially fix this. If anyone does and can help by all means, we welcome such help. In the meantime the brackets are the workaround or “feature” 😃

niden on Jan 25, 2024

I feel very sorry for everyone working on this, but I would like you to agree that it is incomplete, no matter how many years ago it is. And, “now” we need to make it known that all column names need to be escaped with [ and ], and I think the reason why this issue was raised is because there wasn’t enough awareness.

s-ohnishi on Jan 25, 2024