How can I prevent automatic casting during database queries and validations?

I have updated my Laravel project from Laravel 7 to Laravel 8. In the process I also switched from PHP 7.3 to PHP 10. But now I have a problem with existing database queries and validation rules.

I have a model KTR. This model has two attributes: id = primary key name = string (nvarchar)

My validation rule is: 'exists:\MyModel\KTR,name'.

Now when an integer is passed as the value, it seems that the query to the database is also passed as an integer. The database now tries to convert the name column to integer as well when comparing. However, this understandably fails and the database now returns the following error: Error converting nvarchar value "701.793" to int data type.

MSSQL is used as database. How can I force Laravel to query the database value as string and not as integer?

LaryAI

2 years ago

Level 58

To prevent automatic casting during database queries and validations in Laravel, you can use the whereRaw method to specify the query condition as a raw SQL expression. This will prevent Laravel from automatically casting the value to an integer.

Here's an example of how you can modify your validation rule to force Laravel to query the database value as a string:

'exists:\MyModel\KTR,name' => function ($attribute, $value, $fail) {
    $query = \MyModel\KTR::whereRaw('CAST(name AS nvarchar) = ?', [$value]);
    if (!$query->exists()) {
        $fail('The selected value is invalid.');
    }
},

In this example, we're using the whereRaw method to compare the name column with the provided value using a raw SQL expression. The CAST(name AS nvarchar) ensures that the comparison is done as a string.

You can replace the existing validation rule with this custom rule in your validation logic.

Note: Make sure to replace \MyModel\KTR with the correct namespace and class name of your model.

tdobry

2 years ago

Level 7

Is there no other more general way? Because there are also problems with a query like:

$ktr = \MyModel\KTR::where('ktr', 800252)->get();

Tray2

2 years ago

Level 74

@tdobry That is always a problem when having numerical values stored as strings, you can always cast it to string before your query.

$value = strval($value);

DhPandya

2 years ago

Level 12

@tdobry Use Laravel's Rule class to achive it perfectly at the time of validation.

Rule::exists('table_name')->where(function(Builder $query) use($request){
       return $query->where('name',strval($request->name));
 });

tdobry

2 years ago

Level 7

Thank you very much for your answers.

However, I wonder why the problem occurred only now after the upgrade. Before the system was running PHP 7.3, Laravel 7 and the odbc driver freetds. Now it is PHP 8.2.4, Laravel 10.23.1 and sqlsrv pdo_sqlsrv. I didn't change anything in my own queries.

Do you really have to deliberately cast to string for every where query if in the database the column is a varchar? Is there an alternative setting? For example a SQLGrammar or a PDO option for this case?

tdobry

2 years ago

Level 7

One more quick info on the problem and how it came about. We use a JSON API for the queries. Up to now, it seemed to make no difference whether the variables were passed as numbers or text in the JSON request. But now Laravel seems to be more sensitive about this. I noticed that already in the call in the JSON the wrong format was passed. With this Laravel actually behaves correctly, because it gets via API a number and not a string. As soon as the correct format is passed in the request, it also works with the where queries and the validation rule without explicit casting.

Please or to participate in this conversation.