I'm doing a prototype, based off the MOVIE file, where I'd like a single *.fex to have dynamic WHERE conditions (at least I think I do). I want to make multiple *.html pages with different search widgets.

Ultimately, for production, – I want a single sales report that will have one *.html page that filters off sales agent – then a different *.html page that will filter off customer – and a 3rd *.html page that filters of product. For scalability and maintenance reasons – I don’t want to have 3 different reports.

FYI: We are on 8.0.0.5 (new customer)

I have something working based on the following code. This does seem to work – and it may be the route I go. It’s possibly open to SQL Injection – however – my app will be for internal people – so I’m not overly concerned with security.

Since I’m new to webFOCUS – I’d like to get some feedback to see if this is the smartest way to make a single *.fex with multiple OPTIONAL parameters – so I can make multiple *.html pages that deal with only 1 of the filters.

I also read about adding FILTER FILE - but I wasn't digging that - as we'll add new sales agents and I don't want to have to add a new FILTER for each new person that starts.

Thoughts?

-DEFAULT &H2O_TITLE = '';
-DEFAULT &H2O_CATEGORY = '';
-DEFAULT &H2O_RATING = '';


-SET &H2O_TITLE = LJUST(&H2O_TITLE.LENGTH,&H2O_TITLE,'A&H2O_TITLE.LENGTH');
-SET &H2O_TITLE = UPCASE(&H2O_TITLE.LENGTH,&H2O_TITLE,'A&H2O_TITLE.LENGTH');
-SET &H2O_TITLE = TRUNCATE(&H2O_TITLE);
-SET &H2O_daTITLE_FILTER = IF &H2O_TITLE NE '' THEN 'WHERE MOVIES.MOVINFO.TITLE LIKE ''%' | &H2O_TITLE.QUOTEDSTRING | '%''' ELSE 'WHERE 1=1';



-SET &H2O_CATEGORY = LJUST(&H2O_CATEGORY.LENGTH,&H2O_CATEGORY,'A&H2O_CATEGORY.LENGTH');
-SET &H2O_CATEGORY = UPCASE(&H2O_CATEGORY.LENGTH,&H2O_CATEGORY,'A&H2O_CATEGORY.LENGTH');
-SET &H2O_CATEGORY = TRUNCATE(&H2O_CATEGORY);
-SET &H2O_daCATEGORY_FILTER = IF &H2O_CATEGORY NE '' THEN 'WHERE MOVIES.MOVINFO.CATEGORY LIKE ''%' | &H2O_CATEGORY.QUOTEDSTRING | '%''' ELSE 'WHERE 1=1';


-SET &H2O_RATING = LJUST(&H2O_RATING.LENGTH,&H2O_RATING,'A&H2O_RATING.LENGTH');
-SET &H2O_RATING = UPCASE(&H2O_RATING.LENGTH,&H2O_RATING,'A&H2O_RATING.LENGTH');
-SET &H2O_RATING = TRUNCATE(&H2O_RATING);
-SET &H2O_daRATING_FILTER = IF &H2O_RATING NE '' THEN 'WHERE MOVIES.MOVINFO.RATING EQ ''' | &H2O_RATING.QUOTEDSTRING | '''' ELSE 'WHERE 1=1';



TABLE FILE MOVIES
PRINT
MOVIES.MOVINFO.TITLE
MOVIES.MOVINFO.CATEGORY
MOVIES.MOVINFO.RATING
&H2O_daTITLE_FILTER
&H2O_daCATEGORY_FILTER
&H2O_daRATING_FILTER
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
-? &

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

The approach works fine. Don't worry too much about SQL injection as you are really not dealing directly with SQL but WebFOCUS language, so although it's true that users may find their way into adding an 'OR 1=1' condition there so see more records than what their entitled too, they can't really update or delete any rows, which is in my opinion one of the biggest risks of SQL injection.

In any case, if you have users trying to tamper with your security internally you have a bigger issue at hand and using tools such as Resource Analyzer may help you identify the different types of 'filters' being used by your requests and potentially help you spot "questionable" attempts.

You can however change your code a bit to make it easier to maintain and debug:

-DEFAULT &H2O_TITLE    = 'FOC_NONE';
-DEFAULT &H2O_CATEGORY = 'FOC_NONE';
-DEFAULT &H2O_RATING   = 'FOC_NONE';

-IF &H2O_TITLE EQ 'FOC_NONE' THEN GOTO :ENDTITLE ELSE CONTINUE;
-SET &H2O_TITLE = TRUNCATE(&H2O_TITLE);
-SET &H2O_TITLE = LJUST(&H2O_TITLE.LENGTH,&H2O_TITLE,'A&H2O_TITLE.LENGTH');
-SET &H2O_TITLE = UPCASE(&H2O_TITLE.LENGTH,&H2O_TITLE,'A&H2O_TITLE.LENGTH');
-SET &H2O_TITLE = '%' || &H20_TITLE || '%';
-:ENDTITLE


-IF &H2O_CATEGORY EQ 'FOC_NONE' THEN GOTO :ENDCATEG ELSE CONTINUE;
-SET &H2O_CATEGORY = TRUNCATE(&H2O_CATEGORY);
-SET &H2O_CATEGORY = LJUST(&H2O_CATEGORY.LENGTH,&H2O_CATEGORY,'A&H2O_CATEGORY.LENGTH');
-SET &H2O_CATEGORY = UPCASE(&H2O_CATEGORY.LENGTH,&H2O_CATEGORY,'A&H2O_CATEGORY.LENGTH');
-SET &H2O_CATEGORY = '%' || &H20_CATEGORY || '%';
-:ENDCATEG


-IF &H2O_RATING   EQ 'FOC_NONE' THEN GOTO :ENDRATING ELSE CONTINUE;
-SET &H2O_RATING = LJUST(&H2O_RATING.LENGTH,&H2O_RATING,'A&H2O_RATING.LENGTH');
-SET &H2O_RATING = UPCASE(&H2O_RATING.LENGTH,&H2O_RATING,'A&H2O_RATING.LENGTH');
-SET &H2O_RATING = TRUNCATE(&H2O_RATING);
-:ENDRATING


TABLE FILE MOVIES
PRINT
MOVIES.MOVINFO.TITLE
MOVIES.MOVINFO.CATEGORY
MOVIES.MOVINFO.RATING

WHERE MOVIES.MOVINFO.TITLE    LIKE &H2O_TITLE.QUOTEDSTRING;
WHERE MOVIES.MOVINFO.CATEGORY LIKE &H2O_CATEGORY.QUOTEDSTRING;
WHERE MOVIES.MOVINFO.RATING   EQ   &H2O_RATING.QUOTEDSTRING;

ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
-? &

Hope this helps.

Thanks for the feedback.

I implemented all the suggestions you had - but I had issues with the FOC_NONE. It seemed like those statements were staying in there where - and no rows were being returned if I left the parameters blank.

After some digging through the documentation, Developing Reporting Applications, I found the _FOC_NULL - and that seems to do the trick. If I leave the parameter blank – that where seems to drop out of the query – which is what I want.

I really think that's a nifty trick. What I had was working -but your _FOC_NULL suggestion makes the TABLE FILE more clear and readable. Thanks for the pointer!

Jeremy

I'm somewhat old school and tend to feel more comfortable with the good ol' and tried FOC_NONE, but _FOC_NULL will have a similar effect in this particular case.

If you're passing your parameters from an HTML Composer-built page, _FOC_NULL is usually the default when no value is specified for a parameter but that can be changed to FOC_NONE in the Document properties of the HTML page.

In any case, I'm glad the trick worked for you.