I am trying to understand how WebFOCUS handles URL encoding and decoding. It seems like when variables are sent to a focexec through the url (from form fields), all of the characters are being properly encoded (%27, for example), but by the time the variables are process in the focexec, these values are decoded again. This means that potentially malicious characters are being processed through our .FEXs and into SQL statements. Is there an option to prevent this decoding from taking place?

Much thanks,
Kyle

Can you give me examples of "malicious characters"?

Sure: ; ' < > ( ) etc.

Basically stuff that would make SQL unhappy.

I don't understand why these characters would make SQL unhappy. If they're used in a WHERE statement, they're most likely within quote characters so they would be treated as an alpha string.

-SET &TEST1 = 'D<T';

SET SQLENGINE  = DB2
-RUN

SQL
SELECT * 
FROM BASEL.TIME_D
WHERE NOT (PERIOD_TYPE LIKE '&TEST1')
FETCH FIRST 100 ROWS ONLY
END
-RUN

This works for all the characters you mention, except the single quote character.

Handling that character is most likely a WebFOCUS issue, not a SQL one and can be resolved by using the QUOTEDSTRING suffix. With this method, all the characters you mention would work without any problem:

-SET &TEST1 = 'D'T';

-*-- or -SET &TEST1 = 'D)T';

SET SQLENGINE  = DB2
-RUN

SQL
SELECT * 
FROM BASEL.TIME_D
WHERE NOT (PERIOD_TYPE LIKE &TEST1.QUOTEDSTRING )
FETCH FIRST 100 ROWS ONLY
END
-RUN

By the way, my guess is that the temporary encoding you describe is normal browser behaviour, the characters are encoded while in the URL, but are automatically decoded when they get passed to whatever program receives them.

Francis, thanks for the reply.

If we have an SQL statement like

SELECT * FROM owners WHERE username = '" &NAME "'

The problem is when something like this gets inserted into the url:

&NAME=junk';DROP TABLE owners; SELECT * FROM data WHERE info LIKE '%

Even when using MODIFYs instead of direct SQL statements, if these characters don't remain safely escaped they could cause serious damage to the data.

Does that make sense? It could very well be default browser behavior; I'm just trying to understand how to prevent these SQL injections in WebFOCUS.

Thanks again!

If you're worried about somebody passing this kind of stuff to WebFOCUS then I imagine there are issues with your network/database/WebFOCUS security. I don't see how an encoded character would protect you from "DROP TABLE owners;".

Or you may have to examine the incoming data for unwanted characters and removing them before executing the SQL.

Or javascript on the page itself to evaluate the data before sending it to WebFOCUS.

I think you are right about the encoding stuff being the wrong way to handle it... Instead of filtering out bad characters (or allowing good ones), it'd be good to know how WebFOCUS translates TABLE FILEs and MODIFY FILEs into SQL.

Do &vars get inserted into the SQL request and evaluated as SQL, or does WebFOCUS use prepare statements where the SQL is evaluated and THEN &variables are inserted?

Have you tried turning the SQL Trace on? You can see the SQL that gets generated by WebFOCUS.

See how to turn traces on here: Report never seems to stop running

amper variables get inserted into the WebFOCUS code as alphanumeric strings and become part of the code. The generated SQL is a SELECT statement translated from the WebFOCUS code, after the amper variables are inserted into the code.