Everyone,

I have a reporting object in a collaborative portal. The reporting object
uses DEFAULTH and five dynamic parameters, all of which pull from a list of available values found only in my .mas file. The parameters include QUOTEDSTRING in them. In prior versions of WebFocus, a separate validation fex include the DEFAULTH and quotedstring.

My question is this: How do I prevent a SQL injection error in which users could potentially insert some text and see more records than they are supposed to see?

I looked a post from Dec. 4, 2013 (referenced below), and I think it might be the right direction, but since it was so long ago, I thought I'd ask the question anew. Here is the post I looked at:

[SOLVED] Different HTML - Same *.fex - Dynamic where

This message has been edited. Last edited by: FP Mod Chuck,

Could you use ACCEPT= in the master files? See page 194 of this doc: https://webfocusinfocenter.inf...fs6/wf8206ddlang.pdf

I'm not sure. It's not something we've tried previously.

So, to provide a little more information, my portal is a public-facing portal. It's important we get this right.

As mentioned previously, in past versions, we had a separate validation fex that checked any parameters we used anywhere in our portal. We kept adding to it as we established new parameters.

I reviewed the documentation about ACCEPT, but I must admit it's not entirely clear to me.

Has anyone done anything like this previously? If so, how does it work? Does using ACCEPT in the mas file (synonym) eliminate the need for a separate validation fex?

Have you considered using DBA row-level security? We have a public-facing portal too, and various customers run the same reports. We use row-level security so one customer can't see another customer's data, even if they select ALL records on a report. Works great.

Row-level security would make sense if the portal were set up in a different way. I really need to understand how to use the ACCEPT method as I think it's probably the best way to proceed.

Does the list of values change with each user or is it a static list that's enforced so no other values are allowed? The former is a little more complicated than the latter. Either way, ACCEPT= will help you enforce what you will allow as values displayed on a selection.

A very simple test is this: Edit ibisamp/car.mas to add an ACCEPT= to the COUNTRY. It'll look something like this:

 FIELDNAME=COUNTRY, ALIAS=COUNTRY, USAGE=A10, FIELDTYPE=I,
      ACCEPT='ENGLAND' OR 'CHINA', $
 

Then create a simple report like this to test how the autoprompt will behave:

  
TABLE FILE ibisamp/car
SUM DEALER_COST
BY COUNTRY
BY CAR
BY MODEL
WHERE COUNTRY EQ &COUNTRY.(FIND COUNTRY IN IBISAMP/CAR |FORMAT=A10,SORT=ASCENDING,REQUIRED=TRUE).COUNTRY.QUOTEDSTRING;
ON TABLE PCHOLD FORMAT HTML
END

When you run this report, you'll get prompted for only ENGLAND and CHINA.

The ACCEPT=SYNONYM will allow you to do the same thing by creating a table described by a SYNONYM that contains a list of values. The idea being that you store your acceptable values in a much smaller table and your drop down will populate much faster by going through 5 or 10 unique values and the user can't pick anything other than those values in the table described by the synonym.

This message has been edited. Last edited by: BabakNYC,

The list of values does not change with each user. All users have access to the same list of values. In other words, each filter is a static list that is enforced so no other values are allowed.

Then I suggest you use ACCEPT=SYNONYM. Create an LOV table in your database and use that for the acceptable list of values. It's more efficient and less coding. The LOV table can also be updated and maintained more efficiently when it changes and will always display the correct values when users get prompted.

I got the CAR to work out appropriately, and I understand the concept of creating a table of lookup values. It makes sense, and it seems it would be efficient, less coding involved and easier to maintain over time.

So, I'll try the technique you suggest and see if I run into any difficulties.