special chars escapen in parameter van .net oracle query

[BasieP]

Beste mensen,

Misschien wat wazige titel, maar ik wilde alle woorden noemen

Mijn probleem is als volgt.

Ik heb een tabel 'table' die een kolom 'column' heeft met daarin o.a. de tekst: 'aaa_bbb'
Ik wil alles terug dat met 'aaa_' begint

mijn query zou iets zijn als

SQL:

SELECT * FROM table WHERE column LIKE 'aaa\_%'

Nu is alleen die like een input van een gebruiker, en zodoende wil ik dit 'safe' doen, en dus met de daarvoor gemaakte parameters.

Ik heb dan dus een query

SQL:

SELECT * FROM table WHERE column LIKE :p

Daarvan zet ik de parameter naar het volgende

C#:

param.Value = @"aaa\_%";

resultaat is alleen altijd 0 rows.

Oracle versie 10g
De default escape char van onze oracle installatie is '\'


Nu heb ik al zitten zoeken, maar kan hier donders weinig over vinden (vooral doordat er erg veel andere gerelateerde onderwerpen zijn waar ik hits op krijg)
Ik ben er al achter dat in parameters het hele escape karakter als literal wordt gezien.
Wanneer ik in diezelfde tabel een veld maak met de tekst 'aaa\_' en ik voer dan mijn query uit krijg ik die ook niet terug.
Maar dit is ook niet wat ik wil...

Heeft iemand hier ervaring mee?

[Verwijderd]

Gebruik een dubbele slash of doe iets als dit:

C#:

param.Value = @"aaa\_%";

^ Hiermee onderdruk je dat C# zelf de string gaat escapen.

[ Voor 26% gewijzigd door Verwijderd op 09-02-2012 14:45 ]

[BasieP]

Verwijderd schreef op donderdag 09 februari 2012 @ 14:44:
Gebruik een dubbele slash of doe iets als dit:

C#:

param.Value = @"aaa\_%";

^ Hiermee onderdruk je dat C# zelf de string gaat escapen.

euh ja dat doe ik eigenlijk al (zal TS ff aanpassen)

[Verwijderd]

Misschien dan dubbel escapen, of dit:

C#:

param.Value = @"'aaa\_%'";

Ik weet niet precies hoe Oracle hiermee omgaat.

[BasieP]

Verwijderd schreef op donderdag 09 februari 2012 @ 14:53:
Misschien dan dubbel escapen, of dit:

C#:

param.Value = @"'aaa\_%'";

Ik weet niet precies hoe Oracle hiermee omgaat.

mm das beetje onzin. alles wat tussen de double quotes staat is gewoon tekst, en dus gaat oracle dan zoeken op de waarde inclusief de single quotes. Dan vind ie sowieso niets.

[Rotterdammertje]

Is het misschien dat het prepared statement de hele parameter escapet, en wat dus uiteindelijk aan de database wordt doorgegeven iets is als "aaa\\\_\%" (dus met alle 'speciale' tekens geescaped)?

Je kan proberen je query te herschrijven naar:

SQL:

SELECT * FROM table WHERE column LIKE :p || '%'

en dan je parameter zonder %-teken door te geven:

C#:

param.Value = @"aaa_"

[Remus]

Rotterdammertje schreef op donderdag 09 februari 2012 @ 16:47:
Is het misschien dat het prepared statement de hele parameter escapet, en wat dus uiteindelijk aan de database wordt doorgegeven iets is als "aaa\\\_\%" (dus met alle 'speciale' tekens geescaped)?

Je kan proberen je query te herschrijven naar:

SQL:

SELECT * FROM table WHERE column LIKE :p || '%'

en dan je parameter zonder %-teken door te geven:

C#:

param.Value = @"aaa_"

Die underscore is juist hetgeen escaped wordt (_ is een wildcard voor een enkel character).

@TS: Wat gebeurt er als je de escape explicitiet in de query vermeldt? Dus:

SQL:

SELECT * FROM table WHERE column LIKE :p ESCAPE '\'

[Rotterdammertje]

Remus schreef op donderdag 09 februari 2012 @ 16:58:
[...]

Die underscore is juist hetgeen escaped wordt (_ is een wildcard voor een enkel character).

Dat die underscore escaped wordt is toch ook goed? TS wil zoeken op een letterlijke underscore. Ik denk dat het procentteken ook escaped wordt, en dat het daarom niet goed gaat.

[BasieP]

Remus schreef op donderdag 09 februari 2012 @ 16:58:
[...]

Die underscore is juist hetgeen escaped wordt (_ is een wildcard voor een enkel character).

klopt,
ik wil inderdaad niet dat je wildcards kunt gebruiken in de parameter. Dat lukt wel. Ik wil juist zoeken op de literal '_' en daarnaast (op een andere plek) ook wildcards kunnen gebruiken in dezelfde param.

Ik vind het concept echter wel grappig.
Ik wist niet dat je parameters kunt concatineren..
dat zou betekenen dat ik ook dit kan doen:

SQL:

SELECT * FROM table WHERE column LIKE :p1 || '\_' || :p2

Enige nadeel is dat ik dan de userinput moet gaan splitten op de karakters % en _ en dan de delen die niet wildcard zijn in verschillende parameters duwen, en de wildcards als literals met een escape char ervoor in de query plakken.
Niet echt een ideale oplossing...

@TS: Wat gebeurt er als je de escape explicitiet in de query vermeldt? Dus:

SQL:

SELECT * FROM table WHERE column LIKE :p ESCAPE '\'

niets...
of hetzelfde.. of hoe zeg je dat?
geen verschil in ieder geval.


Ik vraag me eigenlijk af of iemand kan verifieren dat dit een probleem. Zoals ik het nu zie lijkt het in de driver te zitten. Hoewel ik dit probleem ook met die standaard oracle sql tool kan reproduceren.

Echter, als je vanaf de commandline dit doet:

SQL:

SQL> exec :p := 'aaa\_%';
SQL> SELECT * FROM table WHERE column LIKE :p

geeft ie wel rows terug.

[BasieP]

Voor wie nog geintereseerd is naar mijn testcase:

C#:

OracleConnection conn = new OracleConnection("hier dus de connection string");
try
{
    conn.Open();

    OracleCommand command = new OracleCommand("SELECT * FROM REGISTRATIE WHERE NAAM LIKE :p AND rownum <= 10", conn);
    OracleParameter param = new OracleParameter(":p", "aaa\\_%");
    command.Parameters.Add(param);

    OracleDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        //debug here
        Console.WriteLine(reader["NAAM"]);
    }
    reader.Close();
}
finally
{
    conn.Close();
}

Ik zou zeer graag horen of iemand dit probleem kan reproduceren...

[Rotterdammertje]

Zoals ik al zei: ik vermoed dat bij het doorgeven van de parameter aan Oracle, alle speciale karakters al voor je worden escaped -- extra-gratis service! Dit betekent dat de parameter-waarde "aaa_%" bij de database aankomt als "aaa\_\%"; de waarde "aaa\_%" komt aan als "aaa\\\_\%"; etcetera. Dit ter voorkoming van SQL injecties en andere narigheden. Dit heeft als neveneffect dat LIKE queries op parameters met wildcards niet werken..

Je kan dit testen door in je query het procentteken aan je parameter-waarde te concatten, en in je .Net code je parameter de waarde "aaa_" te geven. Ik stel voor dat je dat eerst test; als je weet dat dit echt het probleem is, kan je gerichter nadenken over een oplossing.

Test of het bovenstaande alleen records teruggeeft die beginnen met "aaa_", en niet records die beginnen met "aaab", "aaac" etc.

[ Voor 8% gewijzigd door Rotterdammertje op 10-02-2012 12:25 ]

[CodeCaster]

@Rotterdammertje, volgens mij niet:

quote: http://msdn.microsoft.com/en-us/library/k33f0ec7.aspx

When querying an Oracle database using the Microsoft OLE DB Provider for Oracle (MSDAORA) and the ODBC.NET Framework Data Provider, using the LIKE clause to query values in fixed-length fields may not return all expected matches. The reason is that when Oracle matches values for fixed-length fields in a LIKE clause, it matches the entire length of the string, including any padding trailing spaces. For example, if a table in an Oracle database contains a field named "Field1" that is defined as char(3), and you enter the value "a" into a row of that table, the following code will not return the row.

C#:

string queryString = "SELECT * FROM Table1 WHERE Field1 LIKE ?";
OleDbCommand command = new OleDbCommand(queryString, connection);
command.Parameters.Add("@p1", OleDbType.Char, 3).Value = "a";
OleDbDataReader reader = command.ExecuteReader();

This is because Oracle stores the column value as "a " (padding "a", with trailing spaces, to the fixed field length of 3), which Oracle does not treat as a match for the parameter value of "a" in the case of a LIKE comparison of fixed-length fields.

To resolve this problem, append a percentage ("%") wildcard character to the parameter value ("a%"), or use an SQL = comparison instead.

Maar in dat geval moet param.Value = "aaa\_%" dus gewoon werken...

[ Voor 4% gewijzigd door CodeCaster op 10-02-2012 12:31 ]

[Rotterdammertje]

Tsja.. als het volgens de documentatie gewoon moet werken, maar in de praktijk werkt het toch niet, dan moet er toch ergens een verschil zitten. Interessante vragen zijn:

- wat is het type van de kolom? CHAR of VARCHAR2?
- werkt het wel zonder de underscore in de parameter waarde (dus "aaa%")?
- werkt het wel met een exacte match (dus "aaa_bbb")? Of moet je hier expliciet de underscore escapen?

[BasieP]

Rotterdammertje schreef op vrijdag 10 februari 2012 @ 12:19:
Zoals ik al zei: ik vermoed dat bij het doorgeven van de parameter aan Oracle, alle speciale karakters al voor je worden escaped -- extra-gratis service! Dit betekent dat de parameter-waarde "aaa_%" bij de database aankomt als "aaa\_\%"; de waarde "aaa\_%" komt aan als "aaa\\\_\%"; etcetera. Dit ter voorkoming van SQL injecties en andere narigheden. Dit heeft als neveneffect dat LIKE queries op parameters met wildcards niet werken..

Je kan dit testen door in je query het procentteken aan je parameter-waarde te concatten, en in je .Net code je parameter de waarde "aaa_" te geven. Ik stel voor dat je dat eerst test; als je weet dat dit echt het probleem is, kan je gerichter nadenken over een oplossing.

Test of het bovenstaande alleen records teruggeeft die beginnen met "aaa_", en niet records die beginnen met "aaab", "aaac" etc.

Als oracle al mijn parameters zou escapen zou ik uberhaupt niet met wildcards kunnen zoeken dmv parameters.
Dit werkt gewoon dus oracle escapet mijn parameters niet.

CodeCaster schreef op vrijdag 10 februari 2012 @ 12:29:
[...]
Maar in dat geval moet param.Value = "aaa\_%" dus gewoon werken...

Rotterdammertje schreef op vrijdag 10 februari 2012 @ 12:43:
Tsja.. als het volgens de documentatie gewoon moet werken, maar in de praktijk werkt het toch niet, dan moet er toch ergens een verschil zitten. Interessante vragen zijn:

Ik wist niet dat oracle zijn parameters als fixed size zet. Echter snap ik correlatie met mijn probleem nog niet helemaal.

wat is het type van de kolom? CHAR of VARCHAR2?

Het veld is een varchar2, en momenteel zet ik bij mijn parameter niet de lengte van het veld. (Zie hierboven ergens)

werkt het wel zonder de underscore in de parameter waarde (dus "aaa%")?

yup, dan krijg ik netjes alles wat begint met 'aaa'

werkt het wel met een exacte match (dus "aaa_bbb")? Of moet je hier expliciet de underscore escapen?

met een like werkt aaa_bbb wel, maar dan krijg ik ook records terug waarin 'aaaxbbb' staan. (omdat een underscore een wildcard is)
met een = krijg ik hem netjes terug. (maar bij een = werkt een wildcard niet als wildcard)
dat is opzich wel interessant.

[ Voor 18% gewijzigd door BasieP op 10-02-2012 14:11 ]

[CodeCaster]

using the LIKE clause to query values in fixed-length fields

Dat is dan dus niet op jouw situatie van toepassing.

[MikeN]

BasieP schreef op donderdag 09 februari 2012 @ 14:49:
[...]

euh ja dat doe ik eigenlijk al (zal TS ff aanpassen)

Dat doe je niet in je testcase?

Volgens mij moet je gewoon expliciet je escape karakter vermelden (en dan je query natuurlijk ook weer prefixen met @, hetzij via ESCAPE '\' zoals eerder genoemd, of de ODBC syntax { escape '\' }.

Kun je niet met een profiler of packetlogger oid kijken wat de uitgevoerde query is?

[BasieP]

MikeN schreef op vrijdag 10 februari 2012 @ 14:43:
[...]

Dat doe je niet in je testcase?

scherp.
Ik heb daar zoveel mee zitten spelen dat ik hem hier fout geplakt heb.
Ik doe het nu ook in mijn testcase

Volgens mij moet je gewoon expliciet je escape karakter vermelden (en dan je query natuurlijk ook weer prefixen met @, hetzij via ESCAPE '\' zoals eerder genoemd, of de ODBC syntax { escape '\' }.

beide dus al geprobeerd (en alle mogelijke combinaties)
helaas geen verschil kunnen zien.

Kun je niet met een profiler of packetlogger oid kijken wat de uitgevoerde query is?

Dat is de volgende stap. Ik kan niet zoveel in oracle, en zou dus niet weten hoe dit moet. Onze DBA heeft ook geen idee hoe dit kan, en geeft de .net driver de schuld. (ik ook momenteel) maar we hebben nog niet gekeken wat er daadwerkelijk afgevuurd wordt.
In 10g zit nog niet bepaald een handige session logger.
In 11g kan je makkelijker een specifieke sessie van een specifieke gebruiker loggen/volgen. Ik ga eens kijken of we het op 10g voor elkaar kunnen krijgen.

[The Eagle]

Zowel; op 10g als op 11g kun je volgens mij een escapecaracter ook zelf opgeven, maw je zou dat kunnen switchen binnen de sessie.
Misschien niet de meest mooie, maar wellicht wel een werkende oplossing.
Uiteraard wel weer terugswitchen nar het gebruik van de query, aangezien je vermoedelijk niet weet of de connectie op zichzelf staat. En uiteraard goed testen of het niet mer andere functionaliteiten in de knoei loopt.

Overigens kun je ook escapecaracters bij Oracle gewoon escapen. Zie ook http://www.orafaq.com/wik...en_writing_SQL_queries.3F en http://www.orafaq.com/faq..._when_writing_sql_queries

Wat ook nog een omweg zou kunnen zijn is de boel eerst converteren naar string en dan naar character oid, zodat de ODBC driver er ook mee om kan gaan

[MikeN]

Heb je ook al een ander escape karakter geprobeerd? Bv. / en ESCAPE '/' in je query? Dan heeft het iig in C# geen speciale betekenis.

[BasieP]

Ik heb # en @ als andere escape chars geprobeerd, maar het punt is, dat ik wel escape karakters kan zetten door achter mijn query ESCAPE '@' te doen, of door SET ESCAPE @ oid, maar dit heeft totaal geen invloed op de parameters (bind vars) die ik gebruik in mijn query.

Als ik @ zet als escape char, en in mijn parameter dit doe:

C#:

OracleParameter param = new OracleParameter(":p", "aaa@_%"); 

werkt het exact hetzelfde als wanneer ik \ gebruik als escape char

Dit is voor beide methodes zo (het zetten van escape char in query, en in sessie)

[justmental]

Zou gewoon moeten werken, tenzij .net iets met die parameter doet voor het doorgeven.
In de db kijken wat er uitgevoerd is:

SQL:

select * from v$sqlarea where lower(sql_text) like '%aaa%'

Maar dan zie je niet de waarden van parameters, die kun je volgens mij pas vanaf 11g inzien.

[ Voor 18% gewijzigd door justmental op 13-02-2012 13:34 ]

[BasieP]

justmental schreef op maandag 13 februari 2012 @ 13:33:
Zou gewoon moeten werken, tenzij .net iets met die parameter doet voor het doorgeven.
In de db kijken wat er uitgevoerd is:

SQL:

select * from v$sqlarea where lower(sql_text) like '%aaa%'

Maar dan zie je niet de waarden van parameters, die kun je volgens mij pas vanaf 11g inzien.

0 rows
(ook niet bij queries die wel resultaat geven in mijn testcase)

die view lijkt niet helemaal goed te werken

[The Eagle]

Misschien niet de allermooiste oplossing, maar als al het andere faalt zou je ook nog een trigger kunnen overwegen. Evaluate op het statement, en als het aan een bepaalde string voldoet de boel escapen. Niet de mooiste oplossing, maar het werkt vermoedelijk wel

Ergo: Dat zou dus een BEFORE insert, update, delete trigger worden

[BasieP]

The Eagle schreef op maandag 13 februari 2012 @ 16:34:
Misschien niet de allermooiste oplossing, maar als al het andere faalt zou je ook nog een trigger kunnen overwegen. Evaluate op het statement, en als het aan een bepaalde string voldoet de boel escapen. Niet de mooiste oplossing, maar het werkt vermoedelijk wel

Ergo: Dat zou dus een BEFORE insert, update, delete trigger worden

ik heb inmiddels een workaround die geen bind vars gebruikt (parameters) en mijn eigen str-replace op quotes implementeert, dus het probleem is niet meer zo urgent.

Echter blijf ik het idee hebben dat het aan oracle (drivers) ligt, en hoewel ik steeds meer het idee krijg dat oracle gelijk staat aan workarounds wil ik dit probleem eigenlijk getackelt hebben op de juiste manier.

Heeft iemand dit probleem al kunnen reproduceren?

[The Eagle]

Heb je hem eigenlijk al bij Oracle Support aangemeld? Die zouden je eigenlijk gewoon moeten kunnen helpen
En wellicht heb je hier nog wat aan: http://asktom.oracle.com/...QUESTION_ID:2320123769177
Google trouwens ook eens op "oracle parameter bind variable"

[BasieP]

The Eagle schreef op maandag 13 februari 2012 @ 20:39:
Heb je hem eigenlijk al bij Oracle Support aangemeld? Die zouden je eigenlijk gewoon moeten kunnen helpen
En wellicht heb je hier nog wat aan: http://asktom.oracle.com/...QUESTION_ID:2320123769177
Google trouwens ook eens op "oracle parameter bind variable"

Onze dba heeft nog geen tijd gehad om mee te kijken aan de db kant..
Als we het er over een zijn of het probleem in de .net driver of de database zit gaan we hem inderdaad bij support neerleggen.

(i'll keep you posted)