 |
|||||
|
As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Join the TIBCO Community
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community. | |||||
Read-Only TopicGo | Search | Notify | Tools |
| URL encoding and SQL | Login/Join |
| Member |
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 WebFOCUS 7.6.x Windows Output: Excel, PDF, HTML | ||
|
| Expert |
Can you give me examples of "malicious characters"? Francis  Give me code, or give me retirement. In FOCUS since 1991 Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server | |||
|
| Member |
Sure: ; ' < > ( ) etc. Basically stuff that would make SQL unhappy. WebFOCUS 7.6.x Windows Output: Excel, PDF, HTML | |||
|
| Expert |
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 Give me code, or give me retirement. In FOCUS since 1991 Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server | |||
|
| Member |
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! WebFOCUS 7.6.x Windows Output: Excel, PDF, HTML | |||
|
| Expert |
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;". Francis Give me code, or give me retirement. In FOCUS since 1991 Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server | |||
|
| Expert |
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. Ginny --------------------------------- Prod: WF 7.7.01 Dev: WF 7.6.9-11 Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google | |||
|
| Member |
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? WebFOCUS 7.6.x Windows Output: Excel, PDF, HTML | |||
|
| Expert |
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. Francis Give me code, or give me retirement. In FOCUS since 1991 Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |