CODEWITHSUNDEEP
phpmysqllaravel-4eloquentcase-sensitive
Andy Fleming
Andy Fleming

Reputation: 7855

Case-sensitive where statement in laravel

How can I do a case-sensitive string match with laravel?

SELECT * FROM `invites` WHERE `token`='OGwie2e2985tOEGewgu23hUFs'

Can be done as

Invite::where('token',$token)->first()

If I want a case-sensitive match I need to use a statement like this (or similar, as far as I know):

SELECT * FROM `invites` WHERE BINARY `token`='OGwie2e2985tOEGewgu23hUFs'

My best guess would be:

Invite::whereRaw("BINARY `token`='{$token}'")->first()

but then my input is not going through a prepared statement, right?

Upvotes: 26

Views: 29960

Answers (3)

Blake
Blake

Reputation: 2314

Creating a column with a case sensitive collation (eg. utf8_bin or utf8mb4_0900_as_cs) will automatically solve this problem. All queries will automatically be case sensitive

For a new table, you do this like so in your Laravel migration:

$table->string('token')->charset('utf8mb4')->collation('utf8mb4_bin')->nullable();

If the schema has already been created, you can change an existing column's collation with a new migration that features something like this:

$table->string('token')->collation('utf8mb4_bin')->change();

Upvotes: 12

Just a Guest
Just a Guest

Reputation: 173

A little bit late but still wouldn't this be a better alternative?

Invite::whereRaw("BINARY `token`= ?", [$token])->first()

Upvotes: 17

Mark Baker
Mark Baker

Reputation: 212402

You'll need to use DB::raw(), perhaps something like

Invite::where(DB::raw('BINARY `token`'), $token)->first();

Upvotes: 45

Related Questions