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

Resolved Best Practice to prevent SQL injection question

  1. How-to
0
Votes
Undo
  1. Jeff Hamilton
    Jeff Hamilton
  2. PowerBuilder
  3. Thursday, 14 April 2022 13:59 PM UTC

Hello All,

I am a .Net c# developer who took over a PowerBuilder application. I have been doing some of the tutorials and had a question about the SQL connection part.

In going through the tutorial for PB (https://docs.appeon.com/pb2019/getting_started/ch06s05.html) they use the following for a connection param: 

SQLCA.dbparm = ls_database + "UID=" + &
ls_userid + ";PWD=" + ls_password + "'"

This seems open to SQL injection and is encouraging new PB developers to follow this as a standard. Is there a better way to do this in PB?

 

In .Net you could :

using (SqlConnection connection = new SqlConnection(ConnectionString))
{
SqlCommand sqlCommand = new SqlCommand()
{
CommandText = "SELECT ProductId FROM Products WHERE ProductName = @productName",
CommandType = CommandType.Text,
};

sqlCommand.Parameters.Add("@productName", SqlDbType.NVarChar, 128).Value = name;

SqlDataReader reader = sqlCommand.ExecuteReader();
}

Is there a way to do something similar in PB so SQL strings are not open to SQL injection?

 

Thanks

SetItemStatus does not change to NotModified
Cannot load the Rich Text Edit Control
Responses (6)
Jeff Kandt
Jeff Kandt Accepted Answer Pending Moderation
  1. Thursday, 14 April 2022 21:41 PM UTC
  2. PowerBuilder
  3. # 1

Hi Jeff- 

I agree that you probably chose a bad example from the tutorial because that code is just setting values in a dbparm before connection, and I don't think there's any way to trick the database into interpreting part of it as a new statement -- even if someone typed their password as "password123!; drop table products;"

SQLCA.dbparm = ls_database + "UID=" + &
ls_userid + ";PWD=" + ls_password + "'"

 

And I believe Chris when he says you're also pretty safe using datawindows with proper arguments, although you could certainly shoot yourself in the foot by modifying the SQL behind a datawindow to, for instance, append your own where clause that concatenated user-entered data into the select statement.

Your .Net example is more like dynamic SQL. If you look at the PowerBuilder help for "Using dynamic SQL" you'll see that it supports defining placeholder parameters in your sql statement much like your .Net example, except that the placeholders are question marks and are substituted positionally rather than by name. This is an example from the Help: 

INT Dept_id_var = 156
 INT Mgr_id_var    
 String      Dept_name_var
 Dept_name_var = "Department"
 SetNull(Mgr_id_var)
 PREPARE SQLSA 
       FROM "INSERT INTO department VALUES (?,?,?)" ;
 EXECUTE SQLSA 
        USING :Dept_id_var,:Dept_name_var,:Mgr_id_var ;

Hopefully we can trust that PowerBuilder is plugging those values into the SQL statement in a way (bind variables) that ensures strings are evaluated by the database as a single literal value so you can't fool the database into thinking the string ended prematurely and the rest of the input was a command.

Obligatory xkcd:

xkcd: Exploits of a Mom

 
Comment
Jeff Hamilton
  1. Jeff Hamilton
  2. Thursday, 14 April 2022 21:47 PM UTC
Question. In .Net you could set the properties of the connection string (example Connect.UserName = blah blah, Connect.Password = Bl4h2), is that possible in PB?

Also .Net allows string intropolation, for example:

string _something = $"Todays Date is: {DateTime.Now.ToString}";

which did proper formatting for you, is there something in PB that does the same?



thanks for the info on the prepare and execute I will look into that more.
  1. Helpful
Roland Smith
  1. Roland Smith
  2. Friday, 15 April 2022 03:54 AM UTC
It depends on what DBMS you are using. SQLCA has ServerName, LogId, and LogPass properties that some types of connections use.
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 14 April 2022 14:11 PM UTC
  2. PowerBuilder
  3. # 2

Hi Jeff;

  • FWIW: SQL Injection when using the DataWindow object to access any DBMS is almost impossible.
  • Depending on the DBMS, you can have the DB driver secure the connection using TLS 1.2. TLS 1.3 support will be added in PB2022.
  • Depending on the DBMS, You can have the DB driver use "Windows Authentication" instead of a UserID + PWD.
    • For example ... SQLCA.DBParm = "TrustServerCertificate=1,TrustedConnection=1"
  • You can use the new PowerServer feature of PB2021 to remove all SQL from your PB App and have the middle tier only access the DBMS.

HTH

Regards ... Chris

 

Comment
There are no comments made yet.
Roland Smith
Roland Smith Accepted Answer Pending Moderation
  1. Thursday, 14 April 2022 15:01 PM UTC
  2. PowerBuilder
  3. # 3

In your example SQLCA.dbparm is the equivalent of ConnectionString in .Net. It is setting up the connection string to be used by the CONNECT; SQL command.

Comment
There are no comments made yet.
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Thursday, 14 April 2022 15:37 PM UTC
  2. PowerBuilder
  3. # 4

Welcome to PowerBuilder and to the Appeon Community, Jeff!

Comment
There are no comments made yet.
Armeen Mazda @Appeon
Armeen Mazda @Appeon Accepted Answer Pending Moderation
  1. Thursday, 14 April 2022 16:21 PM UTC
  2. PowerBuilder
  3. # 5

How about deploying your project as a cloud app with PowerServer?  https://www.appeon.com/products/powerserver-tour

PowerServer is Web API architecture running on .NET Core... the SQL is stored and executed on the server-side.  Client-side files are encrypted and no direct connection to the database.

Comment
Load more comments
Andreas Mykonios
  1. Andreas Mykonios
  2. Friday, 15 April 2022 05:48 AM UTC
Powerserver can be "servicing" on premises. There is no need to really be on the cloud. The architecture is designed to support cloud...

Andreas.
  1. Helpful
Roland Smith
  1. Roland Smith
  2. Wednesday, 20 April 2022 14:13 PM UTC
Isn't SQL Injection a problem for web apps only? PowerBuilder is a desktop application development tool.
  1. Helpful
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Wednesday, 20 April 2022 15:17 PM UTC
Not only Web apps. If you search Stack Overflow you will see lots of discussion about this. This is a good post that outlines most common reason Windows apps gets the problem: https://stackoverflow.com/questions/40602427/can-windows-app-be-vulnerable-to-sql-injection
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 20 April 2022 15:30 PM UTC
  2. PowerBuilder
  3. # 6

Hi Roland;

   Jeff & Armeen are correct. There is nothing stopping me from writing an MS-Windows App that locates / changes any SQL left in string format. That is why my STD framework can encrypt any SQL or even the Transaction object datum to ensure that SQL injection (or theft) and DBMS credentials are not stolen.  FYI ...

Regards ... Chris

 

 

Comment
Roland Smith
  1. Roland Smith
  2. Wednesday, 20 April 2022 18:32 PM UTC
From what I can tell only application object properties are in the exe string table. Those can be viewed and updated by ResourceHacker.

String literals are embedded in in the PBD files.
  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.