Report Data Selection 'Injector' (Project)

Report Data Selection \'Injector\' (Project)

OK, I need to offer a bounty....

I have a personal vendetta/project that requires the ability to 'inject' a Report's Data Selection with some file-data driven Data Selection.

I am only looking for the 'function' to do the DS Injecting.

Users will be able to update an Application Grid with the Data Selection for a specific UBE | Version and have that 'Complex' DS 'Injected' into the Report's Data Selection.

Concept works like this:

User updates Application with prescribed Report-Level Data Selection:
Application contains:
Header: UBE / Version
Grid: UBE / Version / Field / DS Type / Value01 / Value02 /... / Value24
**Note -
Field: is pre-defined as one of the fields in the V8300001 BSVW
DS Type: Is pre defined similar to
JDEDB_CMP_LE = 0, /* <= */
JDEDB_CMP_GE = 1, /* >= */
JDEDB_CMP_EQ, /* == */
JDEDB_CMP_LT, /* < */
JDEDB_CMP_GT, /* > */
JDEDB_CMP_NE, /* != */
JDEDB_CMP_IN, /* IN */
JDEDB_CMP_NI, /* NOT IN */
JDEDB_CMP_BW, /* BETWEEN */
JDEDB_CMP_NB, /* NOT BETWEEN */
JDEDB_CMP_LK, /* LIKE */

*** NOTE - Each line of the Grid would be an "AND" syntax for the DS Injector.


User Runs UBE.

In the Initialize Section of the UBE, the DS Injector is called:
1. The Version's Data Selection is captured (probably using the ubeSection_GetDataSelection).
2. The Injector part of the function reads each line from the file, as defined from the grid, and injects.
2.a. Each Grid Line is an 'AND' - of its own.
3. The original Data Selection is then appended to the front of the 'injected' data selection.
4. JDB_SetSelection is kicked and the UBE's Data Selection is overridden. Based on research - I believe the JDB_SetSelection can be used to override a UBE's Data Selection (Please correct me if is a confirmation that this cannot be done)...

When the DS is reviewed in Debugger, the Where clause would look like:
Where (Version DS) and (
(Grid Line 1 DS) and
(Grid Line 2 DS) and
...
)

If anyone is interested in this project - contact me directly.

Yes, I am looking to contract/pay$$ for this solution.
Yes, I do intend to make the template available as a proof of concept - to anyone that needs an example...

Anyone familiar with World - can look at the F8303 as an example table layout.

Contact me at: [email protected] or at 208.991.2595 if interested in becoming famous!

(db)


I know that there are C folks that have already done this

Re: Report Data Selection \'Injector\' (Project)

To retrieve a UBE's Data Selection - the following can be used (stolen / tweaked from B31B9570.c
========
/* Get Report Name */
jdeStrcpy(lpDS->szProgramId, ubeReport_GetName(lpBhvrCom));
pszRptName = ubeReport_GetName(lpBhvrCom);

/* Get Report Version */
lpObj = lpBhvrCom->lpObj;
pGlblApp = lpObj->lpGlobalApp;
jdeStrcpy(lpDS->szVersion, pGlblApp->szVersion);
pszVersionName = pGlblApp->szVersion;

/* Get Report Section ID */
ubeSection_GetID(lpBhvrCom, &mnSectionID);
MathNumericToInt(&mnSectionID, &nSection);
lpDS->idSection = (ID)nSection;


ubeSection_GetDataSelection(lpBhvrCom, pszRptName, pszVersionName, lpDS->idSection, &lpSelect, &lpSelectType, &uCount);

ubeSection_FreeDataSelection(lpBhvrCom, &lpSelect, &lpSelectType, uCount);

====

&lpSelect 'should' contain the UBE/Version/Section's Data Selection - when run in the Initialization Section. Yes, it is CRYPTIC....

(db)

Re: Report Data Selection \'Injector\' (Project)

Daniel,

[ QUOTE ]
I believe the JDB_SetSelection can be used to override a UBE's Data Selection (Please correct me if is a confirmation that this cannot be done)...

[/ QUOTE ]

I believe this is where your theory falls apart. I have spent quite a bit of time in the past trying to figure out a way to do what you're trying. The biggest roadblock is the fact that you need the HREQUEST (open table/view handle) of the already-open business view of the report.

As far as I know, there is no 'good' means of obtaining this. We all know it exists somewhere in the UBE's process memory, but I don't believe there is a BSFN or api that retrieves it. If anyone out there knows otherwise, please share!!

Unfortunately, all the JDB_ apis require that HREQUEST... you MUST have the request of the view that is being used in your report AFTER it has already been opened!

[ QUOTE ]
I know that there are C folks that have already done this


[/ QUOTE ]

Do you know names? I tend to disagree unless they've figured out how obtain that HREQUEST.

Please prove me wrong...good luck!

Re: Report Data Selection \'Injector\' (Project)

JDB_SetSelection can not be used in this way. I think there should be a “ubeSection_” API call than can be used here. But, If anyone knows this answer it would be Brian, if he is watching this thread.

Re: Report Data Selection \'Injector\' (Project)

so... the real pot at the end of the rainbow is finding the HREQUEST that the BSVW is originally opened with. If that is identified, we get the move forward?

(db)

Re: Report Data Selection \'Injector\' (Project)

LOL Scott... I hope you meant me or this post is going to be embarassing. Anyway, I can only say for certain what I DON'T know. And as far as I know, there is not any C api that I am aware of or is there any way to change the WHERE clause on a UBE from within a BSFN. That doesn't mean there isn't a way... I just have not found it.

And I have tried, including trying to change the contents of the LPNEWSELECTSTRUCT pointer returned by ubeSection_GetDataSelection (btw, this is just a COPY of the UBE data selection in NEWSELECTSTRUCT form).

When you think about it, a C API to do this probably just isn't feasible for several reasons (off the top of my head):

1. The CNC architecture. If the BSFN was mapped to a different batch server than the UBE was running on... it just wouldn't work... the memory space that it would need to act upon would be in a completely different process on a completely different server.


2. The UBE engine. While the database IO for a UBE probably goes through the same low level JDEBASE layer, the HREQUEST handle returned by JDB_OpenTable is probably some what different than the underlying "table handle" used by the UBE, which is probably different than the one used by ER table I/O (btw, in Xe I tried passing an ER code table handle into a C BSFN and calling JDB_SetSelectionX against it and couldn't get that to work right either).


2a. By the time the UBE engine has the equivalent of a "table handle" it has probably all ready performed the SQL Select statement so any "handle" you passed to a C BSFN would all ready have its result set for the UBE section.


3. Probably countless other reasons I have not thought of.

Re: Report Data Selection \'Injector\' (Project)

[ QUOTE ]
so... the real pot at the end of the rainbow is finding the HREQUEST that the BSVW is originally opened with. If that is identified, we get the move forward?

[/ QUOTE ]

I contend the answer to your question is...yes.

Way back when, when Xe was the big boy on the block. I spent time trying to find that darned HREQUEST in memory (yeah, I'm a geek!) on DEMO. I was actually able to somewhat reverse engineer the lpReportSection memory in the lpBhvrCom to find a linked list of open requests. I don't remember exactly how I arrived at the UBE section's HREQUEST, but I was able to apply data selection to it and change the outcome of the generated report. This whole concept is too fragile and dependent on so many things, I just gave up.

Without KNOWING the contents of that memory (via a JDE-defined c data structure) there was just no way to make it work across software releases, tools releases or platforms. The makeup of that memory could vary greatly based on those things and JDE could alter it at any time.

Re: Report Data Selection \'Injector\' (Project)

[ QUOTE ]
1. The CNC architecture. If the BSFN was mapped to a different batch server than the UBE was running on... it just wouldn't work... the memory space that it would need to act upon would be in a completely different process on a completely different server.

[/ QUOTE ]

True, but any BSFN you would write would be custom and you can make sure it isn't mapped to run on a different server. In other words, you can make sure it runs within the same process as the UBE.

[ QUOTE ]
2. The UBE engine. While the database IO for a UBE probably goes through the same low level JDEBASE layer, the HREQUEST handle returned by JDB_OpenTable is probably some what different than the underlying "table handle" used by the UBE, which is probably different than the one used by ER table I/O (btw, in Xe I tried passing an ER code table handle into a C BSFN and calling JDB_SetSelectionX against it and couldn't get that to work right either).

[/ QUOTE ]

FYI on passing an ER table handle to c bsfn:
<font class="small">Code:</font><hr /><pre>FILEIO_HANDLE_INFO dsHandleInfo = { 0 };
HREQUEST hRequest = NULL;
RTK_CER_FIOGetHandleInfo( lpDS->idTableHandle , &dsHandleInfo );
hRequest = dsHandleInfo.hReqest; </pre><hr />

Re: Report Data Selection \'Injector\' (Project)

[ QUOTE ]

FYI on passing an ER table handle to c bsfn:
<font class="small">Code:</font><hr /><pre>FILEIO_HANDLE_INFO dsHandleInfo = { 0 };
HREQUEST hRequest = NULL;
RTK_CER_FIOGetHandleInfo( lpDS->idTableHandle , &dsHandleInfo );
hRequest = dsHandleInfo.hReqest; </pre><hr />

[/ QUOTE ]

So this works to pass an ER table I/O handle from a UBE or APPL and then you can do things like call JDB_SetSelectionX? Good to know. I wonder if it is still valid for 9.0 in an HTMl client though, where I think ER table I/O in an APPL translates to JDBC calls not ODBC. I only ever really needed to do this in one very small case.... if I need to do table I/O I would just do the whole thing in a BSFN, but still might come in handy some day.

Also did I read correctly, that you actually were able to alter the data selection on a UBE section from within a BSFN? What release? If there was a way to do this it would be immensly useful.

Actually (at the risk of hijacking this thread), what I have looked for and have not found that would be even more useful would be the APPL equivelent of ubeSection_GetDataSelection for E1 9.0.

Re: Report Data Selection \'Injector\' (Project)

I Have a Similar situation where i have to pass the Report name, Version & Section ID, Can anyone suggest what goes into Section ID?

Re: Report Data Selection \'Injector\' (Project)

Hello all,

I hope that this information will be useful to everybody. I've found the HREQUEST for the report section and I'm able now to create more complex sql's from a C function. Here is what I've done.

On the "Initialize Section" with no additional selections(user selection or set selection system functions), I've called a custom BSFN and set the selection using the standard API JDB_SetSelectionX(sectionrequest,...).

The request is in the lpBhvrCom->lpReportSection;
this is the code I've used:
<font class="small">Code:</font><hr /><pre>

typedef struct tagQ480900_HackPointer7 {
void *pPtr01;
void *pPtr02;
void *pPtr03;
void *pPtr04;
void *pPtr05;
HREQUEST hSectionRequest;
} DSQ480900_HackPointer7;

typedef struct tagQ480900_HackReportSection
{
void *pPtr01;
void *pPtr02;
void *pPtr03;
void *pPtr04;
void *pPtr05;
void *pPtr06;
DSQ480900_HackPointer7 *pHackedPointer7;
} DSQ480900_HackReportSection, *LPDSQ480900_HackReportSection;

-----------------------------------------------------------------
LPDSQ480900_HackReportSection pSection ;

pSection = (DSQ480900_HackReportSection*) lpBhvrCom->lpReportSection;

JDB_SetSelectionX(pSection->pHackedPointer7->hSectionRequest,....

</pre><hr />

Any comments wellcome.

I've tested this hack locally and it works fine. In a short while I'll test it on the enterprise server.

Cheers,

Dan.

Re: Report Data Selection \'Injector\' (Project)

That's some mighty fine reverse engineering.

I guess the cats out of the bag then. Oracle should just make the real lpBhvrCom->lpReportSection struct public or give us a set of APIs to set UBE data selection from a C BSFN.

I am curious if your struct for lpBhvrCom->lpReportSection changes across releases. What tools release did you do this under?

Re: Report Data Selection \'Injector\' (Project)

Nice work Dan! We should test this on all server flavours to see if it's consistent.

Craig

Re: Report Data Selection \'Injector\' (Project)

I'm on 9.1.2.1. I'll test this on different flavors of 9.1.* to make sure that it runs. I'll post here if I encounter any problems.

Re: Report Data Selection \'Injector\' (Project)

When I get a moment I can probably test on TR 8.98. S/B a simple enough test.

Re: Report Data Selection \'Injector\' (Project)

This works on 8.98.4.2 dev client and iSeries.

Craig

Re: Report Data Selection \'Injector\' (Project)

I also verified that it works as far back as Xe. It would appear that JDE has not altered the makeup of that memory since its inception.

Re: Report Data Selection \'Injector\' (Project)

Guys,

For us that are slightly-C-Literate, can you present an example of how you would code the ability to be dynamic through the example?

Nice Find, A!

On another note - I've had to dabble in another way to do an SQL injection, via running an SQL Script, embedded in a call from a UBE.
- http://www.jderesearch.com/tips-traps/sql-injection

Thanks again for keeping this thread alive...

(db)

AlexRO said:

<font class="small">Code:</font><hr /><pre>

typedef struct tagQ480900_HackPointer7 {
void *pPtr01;
void *pPtr02;
void *pPtr03;
void *pPtr04;
void *pPtr05;
HREQUEST hSectionRequest;
} DSQ480900_HackPointer7;

typedef struct tagQ480900_HackReportSection
{
void *pPtr01;
void *pPtr02;
void *pPtr03;
void *pPtr04;
void *pPtr05;
void *pPtr06;
DSQ480900_HackPointer7 *pHackedPointer7;
} DSQ480900_HackReportSection, *LPDSQ480900_HackReportSection;

-----------------------------------------------------------------
LPDSQ480900_HackReportSection pSection ;

pSection = (DSQ480900_HackReportSection*) lpBhvrCom->lpReportSection;

JDB_SetSelectionX(pSection->pHackedPointer7->hSectionRequest,....

</pre><hr />

Click to expand...

This one just blew my mind.... nicely done sir !!! keep it up

Thanks,
Jdecoder

Hey All,

It seems there is a caveat to this technique...it only seems to work if there is no previous version/user data selection. I have been unable to make this work if the version has data selection or you enter data selection at runtime. It appears from the debug logs that, if there is version data selection, E1 is performing JDB_SetSelectionX AFTER the Initialize Section event, effectively wiping out any custom filtering via the code above.

However, if there is no data selection, any custom filtering using this technique seems to work just fine.

This has been my observation ... anyone else been able to append custom filtering to existing data selection?