CODEWITHSUNDEEP
phpmysqllaravel
nitrex
nitrex

Reputation: 542

How to make MySQL strict on types

Here's the scenario, we are building a web application in Laravel, and assigned a route as follows:

Route::get('/sample/{client}', 'SampleController@SampleMethod')->name('SampleNaming');

The method SampleMethod, reads from the client variable and displays data about the client in the page.

if we go to http://localhost/appurl/sample/1 for example, it displays data about client id 1. The trick is that if we go to http://localhost/appurl/sample/1abc , it will still display the data for client id 1.

Tried the query in MySQL, 

select * from clients where ID = '1abc'

The ID column is an integer and the primary key of the clients table.

The above query gave me the result for Client ID 1, turns out that MySQL checks the provided string and searches for the integer part in it and fetches the rows accordingly.

Surely, we can escape this in PHP / Laravel however my question is, is there a way to make MySQL strict on such scenarios?

Upvotes: 1

Views: 461

Answers (3)

user2576266
user2576266

Reputation: 2683

preventing type casting is not configurable in mysql. mysql implicit type casting rules

you can use the cast function to solve the issue at the sql level 

select * from t where cast(id as char) = "1abcd"

Upvotes: 0

Kyslik
Kyslik

Reputation: 8385

Pure Laravel solution is to use Regular Expression Constraints.

Route::get('/sample/{client}', 'SampleController@SampleMethod')
    ->name('SampleNaming')
    ->where(['client' => '[0-9]+']);

Upvotes: 1

user2576266
user2576266

Reputation: 2683

you probably need to change sql_mode setting for your server. you need to use strict mode. enter link description here

Upvotes: 0

Related Questions