Optimize query to work for Lower Case and Upper Case

Question

I want to optimize my query to return result either user insert New York or NEW YORK or new york I have around 10000 record in db and some record start with upper later some with lower latter, and my query doesnt return result if I insert like New York.

 [WebMethod]
        public string GetAkontasByMjesto(string Mjesto)
        {
            OracleConnection conn = new OracleConnection("DATA SOURCE=test-1:1521/fba;USER ID=test;PASSWORD=test");
            OracleDataAdapter dr = new OracleDataAdapter("Select * from AKONTAS where MJESTO='" + Mjesto + "'", conn);
            DataSet ds = new DataSet();
            ds.Tables.Add("AKONTAS");
            dr.Fill(ds, "AKONTAS");
            DataTable tt = ds.Tables[0];
            return ds.GetXml();
        }

Is there any solution for this problem ?

The real problem is the string concatenation that allows SQL injection attacks and conversion errors. What would happen if `Mjesto` contained `';drop table AKONTAS;//` ? — Panagiotis Kanavos, Sep 27 '19 at 12:00
In any case the case sensitivity of queries depends on the *column's* collation. Typically, databases use case-insensitive collations and that query should return the results you want, no matter the casing. If it doesn't, check what's happening - did someone create the table with a case-sensitive collation? Why? You should probably change it to be case-insensitive, you'll have to change the collation to a case-insensitive one — Panagiotis Kanavos, Sep 27 '19 at 12:03
Yes, I know check database and table and since I am not sys admin I can not change anything to database and table. However thank you for you advise. — , Sep 27 '19 at 12:05
Collation *matters a lot* because it affects indexes too. If you use `UPPER` on the field, you'll prevent the server from using any indexes on `MJESTO` to speed up the query and the server will have to scan the entire database. The query will be slow even for 10K rows — Panagiotis Kanavos, Sep 27 '19 at 12:05
Doesnt matter. Speed in my case is not so important. I only care about data :) — , Sep 27 '19 at 12:09
Is this not a duplicat of this question? https://stackoverflow.com/questions/5391069/case-insensitive-searching-in-oracle | You basically just want to make it case-insensitive, right? — Christopher, Sep 27 '19 at 12:11
Does this question help you? https://stackoverflow.com/questions/5391069/case-insensitive-searching-in-oracle — The Betpet, Sep 27 '19 at 12:11
@Xerrox not you maybe, but what about outer people's code that has to wait because your code is scanning the entire table? Never mind the DBAs that see the IO spikes. In any case, Dmitry Bychenko explained in a comment that you can create an index on a function in Oracle. In that case you could use `UPPER(MJESTO)` without a performance penalty. Adding an index is a small change and probably easier to get accepted. — Panagiotis Kanavos, Sep 27 '19 at 12:11

score 0 · Accepted Answer · edited Sep 27 '19 at 15:50

0

You can lower both of Mjesto(parameter name) and MJESTO(column name)

[WebMethod]
public string GetAkontasByMjesto(string Mjesto)
{
    OracleConnection conn = new OracleConnection("DATA SOURCE=test-1:1521/fba;USER ID=test;PASSWORD=test");
    OracleDataAdapter dr = new OracleDataAdapter("Select * from AKONTAS where lower(MJESTO) ='" + Mjesto.ToLower() + "'", conn);
    DataSet ds = new DataSet();
    ds.Tables.Add("AKONTAS");
    dr.Fill(ds, "AKONTAS");
    DataTable tt = ds.Tables[0];
    return ds.GetXml();
}

edited Sep 27 '19 at 15:50

Wai Ha Lee

8,598
83
57
92

answered Sep 27 '19 at 12:01

Gürol Mehmet Çetin

224
2
10

2

This will prevent the server from using any indexes. The query will have to scan the entire table. That's not a viable solution for production systems – Panagiotis Kanavos Sep 27 '19 at 12:02
1

*Counter example*: `Mjesto = "cap d'antibes"`; do not *hard code* apostrophes – Dmitry Bychenko Sep 27 '19 at 12:02
1

Will `toLower()` work with turquish "I" ? (rethorical question) – Fildor Sep 27 '19 at 12:04
2

@Fildor the Turkish `i`! Ouch! – Panagiotis Kanavos Sep 27 '19 at 12:07
@Fildor IDK I can't check now this cause of my job maybe later :( – Gürol Mehmet Çetin Sep 27 '19 at 12:08
2

@PanagiotisKanavos Yes, yes. Once you ran into it, you'll _always_ remember. – Fildor Sep 27 '19 at 12:10
2

@Fildor it's a [well known problem](https://en.wikipedia.org/wiki/Dotted_and_dotless_I) that explains why just using UPPER or LOWER is *not* enough – Panagiotis Kanavos Sep 27 '19 at 12:14

VBoka · Answer 2 · 2019-09-27T12:02:21.057

0

You will need to use UPPER

For example:

select * 
from yourtable 
where upper(column) = upper('nEw York');

edited Sep 27 '19 at 12:02

answered Sep 27 '19 at 12:01

VBoka

8,995
3
16
24

2

This will prevent the server from using any indexes. The query will have to scan the entire table. That's not a viable solution for production systems – Panagiotis Kanavos Sep 27 '19 at 12:01
@Panagiotis Kanavos: in case of *Oracle* we can build index on *function*, in this case `Upper(column)` – Dmitry Bychenko Sep 27 '19 at 12:04
1

@DmitryBychenko or indexed computed columns in other databases. That's a viable idea if the OP can modify the database, and probably easier to get through a DBA than changing the collation – Panagiotis Kanavos Sep 27 '19 at 12:08

Optimize query to work for Lower Case and Upper Case

2 Answers2