Charset Problem

[sciracioglu]

Summary of problem or feature request

after update laravel project 7 to 8 charset is not working correctly.

SELECT * FROM STUDENTS WHERE GRADE = "İ"

query results get "I" and "İ" chars together

ORACLE_CHAR=AL32UTF8

System details

• Operating System debian
• PHP Version 7.4
• Laravel Version 8.16
• Laravel-OCI8 Version 8

[melisozmen]

Same problem in Oracle 11g, php 7.4, laravel 8.16

queries from db: select field from table where col like '%İ%' => 10 records select field from table where col like '%I%' => 3 records

queries from laravel: Table::where("col","like","%İ%")->get(); => 13 records Table::where("col","like","%I%")->get(); => 13 records

[yajra]

As of the latest version, the package now performs case insensitive searches. See https://github.com/yajra/laravel-oci8/pull/598 for details. It's a security patch and I think a must have.

Anyways, if you need the case sensitive version, you can manually override the default NLS_COMP to BINARY. There are several options, so use them as needed. Thanks!

[sciracioglu]

Our case insensitive searches return to case sensitive. Which configuration effects this?

[yajra]

I think the NLS below are for case sensitive. I'm a bit new to this configuration so better check the oracle docs and see what fits your requirement best.

        DB::setSessionVars([
            'NLS_COMP' => 'BINARY',
            'NLS_SORT' => 'BINARY',
        ]);

[sciracioglu]

thank you

[Jon4t4n]

Can you elaborate on why case sensitive searching is a security issue? I don't see how?

[yajra]

This is how I discover it, imagine a registration form with unique email validation:

users: id, email (unique validation) User registers with: admin@example.com => SUCCESS Another user registers with: Admin@example.com => SUCCESS

Given this example, you can register same email address by just changing a single letter case. Laravel's unique validation assumes that it is case insensitive but oracle is not.

[Jon4t4n]

Okay, I see.

But isn't this an issue in the validation engine? Seems a bit backwards to enable case insensitive searching for all queries to fix a problem with the validation engine? Because casing matters in a lot of cases. A change like this will break a lot of applications.

I haven't looked into how the Laravel validation engine generates the query. But case sensitive/insensitive searching seems like something that it should be able to handle.

[yajra]

Because casing matters in a lot of cases. A change like this will break a lot of applications.

Good point, this is the solution that works for me during those times.

But isn't this an issue in the validation engine?

Yes, it is. I also tried overriding the database presence verifier of Laravel but I went ahead with this NLS solution. Still open to a better solution if someone can provide it.

[yajra]

I will also try to revisit the database presence verifier. I think that is a better solution given this feedbacks. Thanks!