SQL LIKE operator returns everything from table even if string is empty.

I'm making a search system and I'm encountering an bug where if $search string is empty, DB returns every user.

If I enter first letter of username, everything works normal. Shouldn't it be if string is empty, no user is returned.

$search = '';

$users = DB::table('users')
  ->where('username', 'like', "{$search}%")
  ->get();

return $users;

Is this a bug or it works like intended? How can I fix this, so empty string returns nothing.

MichalOravec

6 years ago

Best Answer

Level 75

@grunburg You can do something like this

$search = '';

$users = DB::table('users')->when($search, function ($query, $search) {
    return $query->where('username', 'like', "{$search}%");
}, function ($query) {
    return $query->whereNull('username');
})->get();

return $users;

Please or to participate in this conversation.