Case-sensitive query on sql server with laravel 5.4

Question

Case-sensitive query on sql server with laravel 5.4

Navigation

#1 by (1 votes)

2

I am querying the database (sql server) for user and password validation with laravel 5.4 but the query is being run as case-insensitive.

In the database it is:

pwd = Aa12A

Input receives:

password = aa12a

The result is:

pwd == password

Inquiry:

DB::table('users')
    ->where('acc', $request->input('account'))
    ->where('pwd', $request->input('password'))
    ->first();

How to change this?

php sql-server laravel-5 laravel-5.4

asked by anonymous 15.03.2017 / 16:56

1 answer

Excel VBA check the contents of two cells Problem with running EventListener

score 1 · Accepted Answer

If it is complicated to compare passwords by SQL server, you can do it by PHP.

$result = DB::table('users')
->where('acc', $request->input('account'))
->first();

if ($result && $result->pwd === $request->input('password')) {
      // Senhas conferem
}

Still, I think it's possible to define it in the database configuration. For example, I will set two equal settings, however with% collation different.

[
    'sqlserver' => [
        'driver'    => 'sqlsrv',
        'host'      => 'host',
        'database'  => '',
        'username'  => 'username',
        'password'  => 'password',
        'prefix'    => '',
        'collation' =>  'SQL_Latin1_General_CP1_CI_AS',
        'charset'   =>  'latin1'
    ],

    'sqlserver-case-sensitive' => [
        'driver'    => 'sqlsrv',
        'host'      => 'host',
        'database'  => '',
        'username'  => 'username',
        'password'  => 'password',
        'prefix'    => '',
        'collation' =>  'SQL_Latin1_General_CP1_CS_AS',
        'charset'   =>  'latin1'
    ]
]

To use collation of the second connection configuration, just do the following:

DB::connection('sqlserver-case-sensitive')->table('users')
    ->where('acc', $request->input('account'))
    ->where('pwd', $request->input('password'))
   ->first();