Validation rule "formatWheres" method incorrectly quoting NULL and NOT_NULL values as "NULL" and "NOT_NULL"

[tomblakemore]

Laravel Version

10.x

PHP Version

8.2.11

Database Driver & Version

MySQL 8.1.0

Description

https://github.com/laravel/framework/blob/10.x/src/Illuminate/Validation/Rules/DatabaseRule.php#L235

When creating a unique validation rule and adding a NULL where clause the resulting formatted string wraps NULL values in quotes as "NULL" which doesn't match NULL column values.

Example:

use Illuminate/Validation/Rules;

$unique = Rule::unique('users', 'email')->withoutTrashed()->ignore($model->id);

echo (string) $unique; // unique:users,email,NULL,id,deleted_at,"NULL"

The string value should in fact be:

echo (string) $unique; // unique:users,email,NULL,id,deleted_at,NULL

Similarly for NOT_NULL.

Possible fix:

/**
 * Format the where clauses.
 *
 * @return string
 */
protected function formatWheres()
{
    return collect($this->wheres)->map(function ($where) {
        if ($where['value'] === 'NULL') {
            return $where['column'].',NULL';
        } elseif ($where['value'] === 'NOT_NULL') {
            return $where['column'].',NOT_NULL';
        } else {
            return $where['column'].','.'"'.str_replace('"', '""', $where['value']).'"';
        }
    })->implode(',');
}

Steps To Reproduce

use Illuminate/Validation/Rules;

$unique = Rule::unique('users', 'email')->whereNull('deleted_at')->ignore($model->id);

echo (string) $unique;

[driesvints]

I'm not sure I follow. Is $model->id null?

[tomblakemore]

Apologies, that's typo, try this:

use Illuminate/Validation/Rules;

$unique = Rule::unique('users', 'email')->withoutTrashed()->ignore($user);

echo (string) $unique; // unique:users,email,"1",id,deleted_at,"NULL"

Whereas it should be:

echo (string) $unique; // unique:users,email,"1",id,deleted_at,NULL

[driesvints]

Is the ignore a factor here? Is $user null?

[tomblakemore]

No, not a factor, this also produces the wrong string:

use Illuminate/Validation/Rules;

$unique = Rule::unique('users', 'email')->withoutTrashed();

echo (string) $unique; // unique:users,email,NULL,id,deleted_at,"NULL"

It should be:

echo (string) $unique; // unique:users,email,NULL,id,deleted_at,NULL

Equally using the "whereNull" and "whereNotNull" methods on the rule produces the same error, e.g.,

use Illuminate/Validation/Rules;

$unique = Rule::unique('users', 'email')->whereNull('column1')->whereNotNull('column2');

echo (string) $unique; // unique:users,email,NULL,id,column1,"NULL",column2,"NOT_NULL"

This should be:

echo (string) $unique; // unique:users,email,NULL,id,column1,NULL,column2,NOT_NULL

[driesvints]

Thanks, yeah let's keep this focused with as minimum possible code to reproduce. Would appreciate a PR for this.

[github-actions]

Thank you for reporting this issue!

As Laravel is an open source project, we rely on the community to help us diagnose and fix issues as it is not possible to research and fix every issue reported to us via GitHub.

If possible, please make a pull request fixing the issue you have described, along with corresponding tests. All pull requests are promptly reviewed by the Laravel team.

Thank you!

[apih]

Actually, the string output is correct. AFAIK, the string is formatted as CSV string, where the values are enclosed in double quote enclosures. There are several rules that do the same thing.

You can see in parseParameters() method, where str_getcsv() is used to parse the parameters string.

For example, let's run the following code in Tinker.

str_getcsv('unique:users,email,NULL,id,deleted_at,"NULL"');

The string is parsed correctly into an array.

[
    "unique:users",
    "email",
    "NULL",
    "id",
    "deleted_at",
    "NULL",
]

[driesvints]

Closing this issue because it's inactive, already solved, old or not relevant anymore. Feel to open up a new issue if you're still experiencing this.