Framework: Validation exists not working

[WaqasBukhary]

• Laravel Version: 5.7.#
• PHP Version: 7.1
• Database Driver & Version: MySQL 8.0

Description:

Exists is not working in validate

Steps To Reproduce:

I have a form

<form action="/projects/submit-update" method="POST" class="form-example">
    @csrf
    <input type="hidden" name="edit_id" value="{{$edit_form->id}}"  >
....
<input type="submit" name="submit" value="Update">
</form>

and backend code....
$request->validate(['edit_id' => 'required|exists:projects,id']);

Let's say correct id is 5, which does not give error. CORRECT
If edit_id is a5 It gives error. CORRECT

The problem is... when an alphabet comes after the number.
If edit_id is 5a It does not give error. INCORRECT

It is causing fatal errors in my app since $request->edit_id after validation is then used in other parts of my code.

[laurencei]

I think this is actually a symptom of a different issue, where IDs ignore the string part. Example:

If you had a route /posts/{post} and you do

/posts/5 - you'll get shown the post with an id of 5

and if you do

/posts/5a - you'll _still_ get shown the post with an id of 5

Even Laravel Forge suffers from this issue (manually add some string "aaa" to the end of your server ID in the URL and watch the correct page still load).

It's been discussed at length before in other topics - I'll try and find some links if I get a chance.

I'll leave this open and let @driesvints decide how to proceed.

[laurencei]

I found one of the topics where it's been discussed before (I know there's been more): https://github.com/laravel/framework/issues/12566

Personally I think it's a bigger issue and this issue comes up every now and again. Ultimately it seems like a DB issue though, which is why it hasnt been addressed in Laravel.

[laurencei]

If you know you only use int as your id - you could probably do

$request->validate(['edit_id' => 'required|integer|exists:projects,id']);

as a workaround

[ludo237]

Thing is, it's MySQL that mess up not Laravel

[seriquynh]

This is not a bug. A Database Management such as MySQL consider "5a" similar to 5 while searching.

These statements are the same.

select * from users where id = 1;
select * from users where id = '1a';
select * from users where id = '1abc';

I often accept this happens in my applications. For strict number comparison, you better use route path regex.

Route::pattern('edit_id', '\d+'); // Ex: for the value "5a", this will help to throw a NotFoundException before executing a sql query.

[laurencei]

For strict number comparison, you better use route path regex.

Except in this case its a validation issue (not routing, I was just using that as an example). But it does mean a similar workaround of using stricter validation rules is one option.

[WaqasBukhary]

Thank you for clarification and feedback. I think you guys are correct, it's MySQL which is mess up :)
I did search the issue as MySQL and came up with following.

MySQL automatically converts strings to numbers as necessary, and vice versa [[source](https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html)]

[driesvints]

Heya, unfortunately we don't support this version anymore. Please check out our support policy on which versions we are currently supporting. Can you please try to upgrade to the latest version and see if your problem persists? If so feel free to reply and we'll try to have a look.

[WaqasBukhary]

Hi, Yes just updated to v5.8.4, The same problem persists.

[driesvints]

Just took a look at the problem. If you know it's only going to be an integer you should add that to your validation rule: $request->validate(['edit_id' => 'required|integer|exists:projects,id']);