Hi,

We are using LLBLGEN Pro V5.5. Our SAAS APP Monitoring tool is masking fields names because of single quotes. If it is possible i want to remove single or double quotes. How can i do it?

I have a query like below;

Generated Sql query: 
    Query: SELECT "YNA"."OPR_SEFLOK_BKG_YUK_VW"."ARK_DISI_KUMP_KONT_ADET" AS "ArkDisiKumpKontAdet", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."SEFER_GEMI_ADI" AS "GemiAdi", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."GERCEKLESEN_GELIS_TARIHI" AS "GerceklesenGelisTarihi", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."GERCEKLESEN_GIDIS_TARIHI" AS "GerceklesenGidisTarihi", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."GOP_SEFER_ID" AS "GopSeferId", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."GOP_SEFER_LOKASYON_ID" AS "GopSeferLokasyonId", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."ILGILI_KISI_KULLANICI_ID" AS "IlgiliKisiKullaniciId", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."KALKIS_TARIHI" AS "KalkisTarihi", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."KUMPANYA_ID" AS "KumpanyaId", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."KUMPANYA_KOD" AS "KumpanyaKod", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."PLANLANAN_GELIS_TARIHI" AS "PlanlananGelisTarihi", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."SEFER_NO" AS "SeferNo", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."STANDART_CUT_OFF_TARIHI" AS "StandartCutOffTarihi", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."YUKLEME_LIMANI_LOKASYON_ID" AS "YuklemeLimaniLokasyonId", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."YUKLEME_LIMANI_LOKASYON_KOD" AS "YuklemeLimaniLokasyonKod", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."KONS_CEVAP" AS "KonsCevap", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."NOT_CEVAP_ID" AS "NotCevapId", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."KONTRAT_STATU_ID" AS "KontratStatuId", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."BEYAN_EKP_ID" AS "BeyanEkpId", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."SON_STOK_DURUM_KODU_ID" AS "SonStokDurumKoduId", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."SON_HRKT_BIT_LOKASYON_ID" AS "SonHrktBitLokasyonId", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."BOOKING_ID" AS "BookingId", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."KONSIMENTO_ID" AS "KonsimentoId", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."KONT_YUK_ID" AS "KontYukId", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."KPK_KONTEYNER_TARIHCESI_ID" AS "KpkKonteynerTarihcesiId", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."YUKLEME_EKIPMANI_BOYUTU_ID" AS "YuklemeEkipmaniBoyutuId", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."YUKLENMEYEN_KONTEYNER_MI" AS "YuklenmeyenKonteynerMi", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."TASMA_VAR_MI" AS "TasmaVarMi", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."TEHLIKELI_MAL_VAR_MI" AS "TehlikeliMalVarMi", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."SICAKLIK_GIRILMELI_MI" AS "SicaklikGirilmeliMi", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."TALIMAT_STATU_ID" AS "TalimatStatuId", "YNA"."KULLANICI"."AD" AS "Ad", "YNA"."KULLANICI"."SOYAD" AS "Soyad", "YNA"."LOK_SEHIR_BOLGE_ULKE_MV"."ULKE_ID" AS "UlkeId", "YNA"."DOK_KONS_AKTARMA_LIMANI"."LIMAN_LOKASYON_ID" AS "LimanLokasyonId" FROM ((("YNA"."KULLANICI" RIGHT JOIN "YNA"."OPR_SEFLOK_BKG_YUK_VW" ON "YNA"."KULLANICI"."ID"="YNA"."OPR_SEFLOK_BKG_YUK_VW"."ILGILI_KISI_KULLANICI_ID") LEFT JOIN "YNA"."DOK_KONS_AKTARMA_LIMANI" ON "YNA"."DOK_KONS_AKTARMA_LIMANI"."DOK_DENIZ_KONSIMENTOSU_ID"="YNA"."OPR_SEFLOK_BKG_YUK_VW"."KONSIMENTO_ID") LEFT JOIN "YNA"."LOK_SEHIR_BOLGE_ULKE_MV" ON "YNA"."LOK_SEHIR_BOLGE_ULKE_MV"."LOKASYON_ID"="YNA"."DOK_KONS_AKTARMA_LIMANI"."LIMAN_LOKASYON_ID") WHERE ( "YNA"."OPR_SEFLOK_BKG_YUK_VW"."PLANLANAN_GELIS_TARIHI" >= :p1 AND "YNA"."OPR_SEFLOK_BKG_YUK_VW"."PLANLANAN_GELIS_TARIHI" <= :p2 AND ( "YNA"."OPR_SEFLOK_BKG_YUK_VW"."KUMPANYA_ID" IN (:p3) OR ( "YNA"."OPR_SEFLOK_BKG_YUK_VW"."KUMPANYA_ID" IN (:p4) AND ( "YNA"."OPR_SEFLOK_BKG_YUK_VW"."CUT_OFF_KUMP_ID" NOT IN (:p5) OR "YNA"."OPR_SEFLOK_BKG_YUK_VW"."CUT_OFF_KUMP_ID" IS NULL))) AND "YNA"."OPR_SEFLOK_BKG_YUK_VW"."YUKLEME_LIMANI_LOKASYON_ID" IN (:p6, :p7, :p8, :p9, :p10) AND ( "YNA"."OPR_SEFLOK_BKG_YUK_VW"."KALKIS_TARIHI" > :p11 OR "YNA"."OPR_SEFLOK_BKG_YUK_VW"."KALKIS_TARIHI" IS NULL))
    Parameter: :p1 : Date. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.07.2019 00:00:00.
    Parameter: :p2 : Date. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 30.07.2019 00:00:00.
    Parameter: :p3 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: :p4 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: :p5 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: :p6 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 82218.
    Parameter: :p7 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 282.
    Parameter: :p8 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 76436.
    Parameter: :p9 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 89797.
    Parameter: :p10 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 76445.
    Parameter: :p11 : Date. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 23.03.2020 12:10:21.

And SAAS APP Monitoring tool output like below;

    select "YNA"."OPR_SEFLOK_BKG_YUK_VW"."ARK_DISI_KUMP_KONT_ADET" as "ArkDisiKumpKontAdet", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."SEFER_GEMI_ADI" as "GemiAdi", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."GERCEKLESEN_GELIS_TARIHI" as "GerceklesenGelisTarihi", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."GERCEKLESEN_GIDIS_TARIHI" as "GerceklesenGidisTarihi", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."GOP_SEFER_ID" as "GopSeferId", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."GOP_SEFER_LOKASYON_ID" as "GopSeferLokasyonId", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."ILGILI_KISI_KULLANICI_ID" as "IlgiliKisiKullaniciId", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."KALKIS_TARIHI" as "KalkisTarihi", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."KUMPANYA_ID" as "KumpanyaId", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."KUMPANYA_KOD" as "KumpanyaKod", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."PLANLANAN_GELIS_TARIHI" as "PlanlananGelisTarihi", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."SEFER_NO" as "SeferNo", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."STANDART_CUT_OFF_TARIHI" as "StandartCutOffTarihi", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."YUKLEME_LIMANI_LOKASYON_ID" as "YuklemeLimaniLokasyonId", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."YUKLEME_LIMANI_LOKASYON_KOD" as "YuklemeLimaniLokasyonKod", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."KONS_CEVAP" as "KonsCevap", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."NOT_CEVAP_ID" as "NotCevapId", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."KONTRAT_STATU_ID" as "KontratStatuId", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."BEYAN_EKP_ID" as "BeyanEkpId", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."SON_STOK_DURUM_KODU_ID" as "SonStokDurumKoduId", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."SON_HRKT_BIT_LOKASYON_ID" as "SonHrktBitLokasyonId", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."BOOKING_ID" as "BookingId", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."KONSIMENTO_ID" as "KonsimentoId", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."KONT_YUK_ID" as "KontYukId", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."KPK_KONTEYNER_TARIHCESI_ID" as "KpkKonteynerTarihcesiId", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."YUKLEME_EKIPMANI_BOYUTU_ID" as "YuklemeEkipmaniBoyutuId", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."YUKLENMEYEN_KONTEYNER_MI" as "YuklenmeyenKonteynerMi", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."TASMA_VAR_MI" as "TasmaVarMi", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."TEHLIKELI_MAL_VAR_MI" as "TehlikeliMalVarMi", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."SICAKLIK_GIRILMELI_MI" as "SicaklikGirilmeliMi", "YNA"."OPR_SEFLOK_BKG_YUK_VW"."TALIMAT_STATU_ID" as "TalimatStatuId", "YNA"."KULLANICI"."AD" as "Ad", "YNA"."KULLANICI"."SOYAD" as "Soyad", "YNA"."LOK_SEHIR_BOLGE_ULKE_MV"."ULKE_ID" as "UlkeId", "YNA"."DOK_KONS_AKTARMA_LIMANI"."LIMAN_LOKASYON_ID" as "LimanLokasyonId" from ((("YNA"."KULLANICI" right join "YNA"."OPR_SEFLOK_BKG_YUK_VW" on "YNA"."KULLANICI".'*****'="YNA"."OPR_SEFLOK_BKG_YUK_VW".'*****') left join "YNA"."DOK_KONS_AKTARMA_LIMANI" on "YNA"."DOK_KONS_AKTARMA_LIMANI".'*****'="YNA"."OPR_SEFLOK_BKG_YUK_VW".'*****') left join "YNA"."LOK_SEHIR_BOLGE_ULKE_MV" on "YNA"."LOK_SEHIR_BOLGE_ULKE_MV".'*****'="YNA"."DOK_KONS_AKTARMA_LIMANI".'*****') where ( "YNA"."OPR_SEFLOK_BKG_YUK_VW".'*****' >= :p1 and "YNA"."OPR_SEFLOK_BKG_YUK_VW".'*****' <= :p2 and ( "YNA"."OPR_SEFLOK_BKG_YUK_VW".'*****' in (:p3) or ( "YNA"."OPR_SEFLOK_BKG_YUK_VW".'*****' in (:p4) and ( "YNA"."OPR_SEFLOK_BKG_YUK_VW".'*****' not in (:p5) or "YNA"."OPR_SEFLOK_BKG_YUK_VW".'*****' is null))) and "YNA"."OPR_SEFLOK_BKG_YUK_VW".'*****' in (:p6, :p7, :p8, :p9, :p10))

We'd really appreciate it if you could help. Regards, Hazal

You want to have all double quotes removed from the query which is executed on the DB or from the string that's e.g. emitted to the tracers? As the quotes are needed to be sure a name is valid as it might be a case sensitive name: "Foo" is different than "FOO", but if it's specified without quotes it's seen as FOO no matter what, so a field which is called 'Foo' will then not match and you'll get an error.

So I'm not sure what it is you want.. the source query you post doesn't have any single quotes, the "ID" string is replaced with '****' but that's done in that monitoring tool, and I don't know what we can do about that, isn't that a change for the monitoring tool ?

Hi Frans,

actually, we contacted dynatrace first. They stated that the source of the single quotation mark cannot be a database monitoring tool. They pointed us to the database settings.

Our database specialist, ORACLE, also said that it works directly without interfering with this query. He suggested that we evaluate the code that generated the query.

We are aware that LLBLGEN normally only produces double quotes. The query we sent you shows this. Just because we no longer know what / where this change was made ("ID" -> 'ID "), we thought that if we remove these quotes from the query, we can get rid of this error somehow.

Now, we understood the reason for the double quotation mark very well, thanks to your explanation

Thank you very much for your support. Serkan

Is the weird '****' replacements only happening with some fields, like "ID" ? Not that we do anything, the text in the tracer as posted in the first post, first query below 'Generated Sql query: ' is the exact SQL command that's generated.

Could you enable the tracer 'ORMQueryExecution' as well? that's the tracer which will show which SQL statement is executed using the OracleCommand. It might be your application has code which transforms the Sql statement before executing and after generating.

You can also see which statements are executed by using the ORM Profiler, the statements captured there are the statements sent to the DB. If these don't contain any '****' strings, then the transformation happens after that.

(I also couldn't determine from your posts whether the query causes errors because of the '****' or that the query works fine but the logged queries captured by the saas app monitoring software contained them.

Hi Frans,

ORMQueryExecution trace shows that generate only double quotation.

By the way even i replace '***' with the field name 'ID', the query can not be executed because of the syntax error

i'll open an app monitoring case.

thanks!

Serkan

If they need info from us, let me know

Hi Frans,

bad news

the original statement from the application monitoring tool vendor is follow

This is me verifying to you that you will not be able to capture anything within double quotations in the WHERE clause of an SQL statement. This is designed purposefully in order to protect personal data by not capturing it at all as mentioned in our Levels of data protection docs.

So it would be great we had had an option in the code generation process that omit these double quatos.

upper/lower case issue would not be problem, because all objects in the oracle database is named upper case.

can i achieve this by manipulate the templates?

Did you notice that they have only masked the field names in the WHERE clause and the JOINs? Same fields in the select list were left as is.

So if they are doing this for privacy, then most probably they re mistaking the field names for parameter values.

In the "Where" part, they do not risk and mask them where they see the quotation marks. We have opened a "change request" for this situation, but it is a very long process to be accepted, approved and made. I have to take care of myself. Is there a risk you see in removing quotes from the query created in llblgen with the change of template?

Holy crap this is some serious stupidity on their part. they simply think any "somestring" is a literal string and needs masking? this doesn't make things less secure as you can determine the fields from the projection anyway...

Anyway, you can alter this but it's not that trivial and it gives you a maintenance burden, which I'm not happy with as who'll know in 1-2 years that this was changed and runs into an issue and we have to spend a lot of time figuring out what's wrong.

But, as they're inflexible idiots and we're not, here's how to change this behavior:

The method which wraps everything in "" is the CreateValidAlias method in OracleSpecificCreator.cs. The 'easiest' is the following.

• Create a derived class of OracleSpecificCreator and override the CreateValidAlias method. In the override you check for whether the field has already "" around it and if so, strip them out (happens with some procs) and if not, simply return the string as-is.
• Create a derived class of the DynamicQueryEngine class for Oracle. Override the CreateDbSpecificCreator method and return an instance of your custom OracleSpecificCreator subtype.
• in the DataAccessAdapter class a method called CreateDynamicQueryEngine() is present, it will create a new instance of the DynamicQueryEngine class. This is a bit of a problem, as deriving from DataAccessAdapter and overriding CreateDynamicQueryEngine() requires you to alter all code you have (Unless you have a factory method). You can also alter the template but that too is perhaps a bit problematic.

This will take care of the "" around any name in the queries.

An alternative is to change the OracleSpecificCreator code of the oracle DQE from the runtime sourcecode we provide (see website: extras section), compile the DQE yourself and reference that assembly instead. The downside of this is that you have to migrate your changes every time you want to upgrade the runtime libs. The DQEs are pretty stable but still it might cause maintenance problems.

Hope this helps with this bizarre situation. (but alas... the whole world is in a bizarre situation now... )

we will consider both options.

Thank you for your kind support. 👍

Hope to get rid of quarantine days as soon as possible ...

Hi,

CreateValidAlias() supports removing quatos

public override string CreateValidAlias(string rawAlias) { if(DynamicQueryEngine.CaseInsensitiveNames) { return rawAlias.Trim('"'); } Adding oracleCaseInsensitiveNames removes quatos. <add key="oracleCaseInsensitiveNames" value="true"/>

However, some of our tests failed after this change. I would like to share one of these. Maybe you want to rate it as BUG.

IF you set an alias longer than 30 characters tlb.addFields(DokKonsimentoMusteriFields.YukTeslimDurumu.SetObjectAlias("ALICI_MUSTERI"), "ALICI_MUSTERI_YUK_TESLIM_DURUMU");

the field name is re-genarated. SELECT "LPA_A2"."YUK_TESLIM_DURUMU" AS "F__-691417033", "LPA_A2"."AD" AS

when i set to oracleCaseInsensitiveNames=true, the query becomes SELECT "LPA_A2"."YUK_TESLIM_DURUMU" AS F__-691417033, "LPA_A2"."AD" AS

because the field name containing the "-" sign is not surrounded by double quotes unit test fails. (the form of query is broken)

Regards Serkan

The hashcode results in a negative int and that results in the - character in the name. That's indeed a small issue for us to fix, and this went unnoticed as it's always wrapped within "" or [] or other wrapping character.

We'll look into fixing this asap.

(in 5.5.7 / 5.6.3)

I would like to inform that, Today we pushed one one time and finally App.Mon. Vendor agreed to open this case as bug. This has reduced the pressure on us for now. ( we keep on oracleCaseInsensitiveNames = false)

I hope this case will be resolved by the problem owner ( app. mon) Othervise ( if we have to make the value of oracleCaseInsensitiveNames true) some problems still need to be solved by refactoring our project.

Thanks anyway..👍 Serkan

Fixed (5.5.7 and 5.6.3) hotfix builds

Well, glad they came to their senses You can use the fixed runtimes on our part to avoid the alias issues with the '-' character.