Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved SQL sentences present in memory

  1. How-to
0
Votes
Undo
  1. Erick Siliezar
    Erick Siliezar
  2. PowerBuilder
  3. Thursday, 18 July 2019 21:05 PM UTC

Hi everyone, currently I'm using PowerBuilder 2017 connected to Oracle 19C. During some audit test of my app I received some notifications about my tool.

When I have SQL scripts embedded in my code, if I run the tool and test the memory, those scripts appear in the memory and that will be a risk because the query appear in plain text. I tried to move those scripts and use a datastore components instead of my query. But unfortunately the script remains in memory.

Something like this:

My old script in the code

SELECT sys_context('userenv','client_identifier') INTO :client_id FROM dual; 

New code

lds_data = Create n_ds
lds_data.DataObject = 'd_sys_context'  //Datastore with the SQL sentence
lds_data.SetTransobject(SQLCA)


ll_Row = lds_data.retrieve()
client_id = lds_data.getitemstring(ll_Row,"data")

if isvalid(lds_data) then destroy(lds_data)

 

Can you please help me to solve my issue. How can I avoid this issue. Should I change my code to connect securely, because I use a simple code to connect to the database? I'm using machine code compilation 32 bits.

sqlca.logid = "My user"

sqlca.logpass = "My pwd"

connect using sqlca;

 

  1. Security
Animated GIFs and transparency...
Refresh PBLs from Git with OrcaScript does not del...
Accepted Answer
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Friday, 19 July 2019 13:36 PM UTC
  2. PowerBuilder
  3. # Permalink

Hi Erick;

  FWIW: I have always had the same issues around applications that leave sensitive datum around in memory.  However, I used my STD Framework's Encode/Decode features to immediately encode sensitive data as soon as my App retrieved it from the DBMS. Then the sensitive data was only decoded into a local variable when needed. This technique has always satisfied very stringent EDP auditors (IT security people) for many decades.

  FWIW: In PB2017R3, Appeon introduced a new object class named the "CoderObject". You could adopt the use of this object like the technique I have in my framework and encode the datum as soon as you retrieve it into your PB app and leave the data that way until needed. Then only decode the secure data when needed into local variables which are then automatically destroyed an the end of the process.

  Alternatively, sensitive data can always be encrypted & stored into the DBMS. So retrieving that secure datum is always safe as it is transmitted & comes in encoded. Now you only need to decode to a local variable when required leaving the long term storage secure.

Food for thought.

Regards ... Chris

PS: Same problem for secure datum in App "temporary" (work) files.

Comment
Erick Siliezar
  1. Erick Siliezar
  2. Friday, 19 July 2019 23:03 PM UTC
That sounds good, it is possible to get an example of this component
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Saturday, 20 July 2019 00:24 AM UTC
Hi Erick;

The "CoderObject" is now a built-in class into PB2017R3.

Regards ... Chris
  1. Helpful
There are no comments made yet.
Responses (3)
Armeen Mazda @Appeon
Armeen Mazda @Appeon Accepted Answer Pending Moderation
  1. Friday, 19 July 2019 01:51 AM UTC
  2. PowerBuilder
  3. # 1

Hi Erick,

This is an inherent issue of client/server architecture and not specifically a PowerBuilder problem.  Client/server apps built with other languages/tools should have the same issue.

Generally speaking client/server apps are deployed on-premise where access to the app is restricted.  So we encourage customers to evaluate how much security they really gain practically speaking vs. the cost to change the architecture of their apps.

But if you do want to avoid this, you can move to an n-tier architecture by deploying your data access logic (including SQLs) as C# Web APIs (to a server rather than client).  This way no SQL would be executing on the client side, and the client would simply be making REST/JSON calls to the C# Web APIs.

PowerBuilder 2019 Cloud Edition provides a .NET DataStore, C# IDE, utility to migrate existing DataWindows to .NET DataStore, and a new RESTClient object to allow you to hook a DataWindow control into a C# Web API with just a few lines of code.

Here are some relevant links to look at:

Another option to consider if you need an immediate or temporary solution is to deploy your app with Citrix.  If the Citrix is properly locked down then on the user's actual machine you shouldn't be able to see the SQL because the SQL is actually executing on the Citrix server and what is on the user's machine is essentially a picture of your app.

Regards,
Armeen

Comment
There are no comments made yet.
David Peace (Powersoft)
David Peace (Powersoft) Accepted Answer Pending Moderation
  1. Monday, 22 July 2019 11:01 AM UTC
  2. PowerBuilder
  3. # 2

Hi Erick

Using your example you could do the following:

Instead of this:

SELECT sys_context('userenv','client_identifier') INTO :client_id FROM dual; 

do this:

string ls_userenv

ls_userenv = 'userenv'

ls_client = 'client_identifier'

SELECT sys_context(:ls_userenv, :ls_client) INTO :client_id FROM dual; 

destroy ls_userenv

destroy ls_client

I appreciate that is does not work for all selects, but is a good way to start securing the content of the SQL.

I'm guessing that having the SQL code visible is your problem not the values retrieved. Unless they are retrieved into global or instance variables they will be destroyed at the end of the script.

Hope this helps

David

Comment
Erick Siliezar
  1. Erick Siliezar
  2. Monday, 22 July 2019 14:20 PM UTC
Hi David, You are right the issue is the SQL statement present in memory, not the data retrieved. I think the Chris recommendation can help, I just need to understand how to use the "CoderObject"



Thanks
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.