A new problem, a new stack

Time to Rewrite

In the past year, my employer decided to rewrite our Internal Application.     It's not something we took lightly, the current Delphi Application is over 5 Million lines of code.     So as we rewrite portions of that existing application we will need to maintain our existing Delphi application so I will continue to use Delphi for at least 4-5 more years.   Our system was released in 1999 as a Y2K project.   Most of the original code was had the business rules right in button events, and a majority of that still exists.     With my employer, it is unheard of to have an application survive 20 years, and it will most likely be 25 years old when we turn it off.      It says a lot about our staff and Delphi.     

User Devices are always changing

However, I have always believed we should use the right tools for the job at hand.    Our infrastructure is greatly changing, we now have a few telecommuters, with a push to support more.     This is not friendly to our existing VCL Client Server Application.    We have a mixed environment of Desktop PC, Macs, Tablets and have to be prepared to support the ever-changing device form factors.   Our user's desktops are locked down with no administrative rights.    Implementing Continuous Deployment with a desktop application has been increasing more difficult to manage.

We decided it was best to create a browser-based application.    That is really not something Delphi is a market leader in so a change was in order.  Having now spent several months of development using our new stack I am very pleased with this decision.   

New Development Stack

Front end

We are using Angular 7+ with strict compiler settings on the TypeScript compiler.    This Angular CLI provided a huge selling point, it provided much of the front end stack without having to build it up.  Angular provided a component model and TypeScript syntax that reminds me of Delphi helped with the learning curve on the transition.   

We looked at the ever changing component lanscape with Angular.  We spent a few weeks and decided to write own library that provides a facade around the PrimeNg Controls.   This allows a majority of our front end to not care about the underlying library, if control is deprecated or problematic we can swap it out without impacting the rest of the application.

The browser world is always changing,    It's a major moving target, things like web assembly may really change the landscape in this space.     As such it's critical to avoid tightly coupling the various parts of our application.   We are using Visual Studio Code as the editor of all our front end work and I highly recommend it.

Back end

The backend is a series of REST endpoints written using C# and ASP.NET Web API.   We have no user-facing ASP.NET pages.  We now also provide a real-time client to client communication channel implement SignalR, which has increased our ability for our users to collaborate in our application.  

Our data layers are abstracted and loosely coupled to our business rules which has greatly enhanced our ability to unit test, and we chose XUnit to help us with that.  Which has in turn increase our quality.   

We are using Entity Framework to managed our database access, which came with a bit of a learning curve as someone who has used SQL for some many years.    But now after using LINQ, I have found it really easy to use and so far as produced performant access to our data.   

We are using Visual Studio 2017 Professional for our back end code, and we will eventually be upgrading to Visual Studio 2019 which was released today.

The Value of Code Reviews

I have recently seen code that that looked like this.

 
try
  ... Lines of Code here ...
except
end;

or

 
procedure TCustomClass.MethodDoThis()
begin
  ... 20 Lines of code ...
end; 

procedure TCustomClass.MethodDoThis2()
begin
  ... Same 20 Lines of code with slight modification ...
end;

Both produced code that may have passed end user testing. But both cause long-term problems.

• The first example was just hiding all exceptions, a bad practice. After the code review, it was determined that the try | except block was not even needed.
• The second example was refactored to reduce the need for duplicate code. The duplicate code causes problems when someone has to make changes that would apply to both methods but does not notice the 2nd method and therefore neglects to change it.

My point is simple, code reviews have value.

Sidenote: Looking forward to reviewing Tokyo release of Delphi soon. With 500+ Bug Fixes with many in areas that impact our system is definitely worth investigating. Security and Quality go hand in hand, and I appreciate work to improve security. Typically, security issues are related to bugs in code that are exploited by hackers.

Ideara / Embaracdero is flushing away user trust in their ability to do secure computing.

In today's computing society you have to think of security first in the design of everything you do.

It's costly to retrofit security. In my job I have think about security every day. The costs of sensitive data escaping is too big to ignore. Trust is a huge word in security.    I have to trust the vendors we use, that they will act quickly to resolve security issues.   So we are not left vulnerable.    A good vendor realizes there will be security problems and has a way to publicly communicate that has does so in an open and transparent method.

Over 10 months ago I sent Embarcadero management several problems with Embarcadero's websites. I finally saw a bit of action after the main website was hacked quickly after it went live.   I even talked with Atanas Popov the General Manager of Embarcadero.   But after that they went dark and have not communicated with me regarding security since.      It's clear they have no plans to create a place on where we can go learn about security issues and the fixes that may have occurred in the product.  

I also called for them to hire a CSIO and empower them, which clearly has not happened.  Even if they did not have a CSIO, they need security to be monitored and prioritized.

Today I received a plaintext password in email for an Idera Community website.   Have they not heard about hashing passwords?   A hashed password can not be sent in clear text.    In email they claimed it was stored securely, and they did it this way to prevent spam.    But what they did was give passwords out  insecurely which could enable more spam instead of reduce it.   They defended this action and clearly thought the practice was ok.     They offered to delete my account.   I did not request the account so I took them up on the offer.   Regardless maybe they should watch the password portion of my session I did for them at CodeRageX.    

Idera and Embarcadero are clueless when it comes to website security.    Maybe just websites in general since Embarcadero's website is now serving content to me in Portuguese even when I select English.

I have NO TRUST in SECURITY from Idera or Embarcadero at this point.

Moving too slowly on a security vulnerability is like using and flushing a clogged toilet, bad things will happen.    Being vulgar and swearing is not in my character, however, it was very very hard not to be that way in this post.   I have invested decades in a company and product that I can see clogged in their own toilet water.

Code in Application or the database?

My employer has a fairly large Delphi application with ~5 Million lines of pascal code.

They have a large database and use Oracle 12c to manage it.

I have seen many methodologies on how to manage your database access code.
On the edges I see two extremes are:

• Do everything in Stored Procedures 
• Do everything in Code outside the database, (Typical ORM)

I believe you need to use the best tool for the job.   So sometimes we are doing massive amounts of data processing that never leaves the database.     For that I use Packages/Stored Procedures in Oracle.     

Years ago we had a process that was 100% in code, and it took 20+ hours in code to execute, due to the round trips to the database.   When we looked at optimization, it was clear that network time was a huge part of the equation.    This Delphi process was converted to Oracle PL/SQL and was further optimized and our time was was reduced to <30 minutes

A simple example using the best tool for the job.    I have been a advocate of unit testing in Delphi.    Several months ago we had do some major PL/SQL work that resulted in about 50k lines of PL/SQL code.     Way too many lines of code to write and trust without sometime of unit testing framework.    We had been testing PL/SQL code from Delphi using DUnitX, but it was one too many steps away from the code under test.    I started working with utPLSQL a user testing framework for PL/SQL.   

In the past few weeks: I finished the move of utPLSQL from SourceForge to GitHub, and have released version 2.3.1 of utPLSQL.

Still love and use DUnitX but I use it from my Delphi Code not my PL/SQL code.

Regardless of the technology you find yourself using for development you need to unit test.

Rad Studio Berlin 10.1 - First Look via Twitter

Today I installed Rad Studio 10.1 Berlin.    This was my first chance to look at it.  
Here are some the tweets are shared earlier today.

Just installed Rad Studio 10.1 Berlin. Loved the new install experience.

— Robert Love (@DelphiDev) April 21, 2016

Rad Studio 10.1 Berlin has a new welcome page. It's very clean, easy to use. Thank you RSP-11183 can now be closed pic.twitter.com/3gzSLXKw3F

— Robert Love (@DelphiDev) April 21, 2016

Rad Studio 10.1 Berlin - Cool New Feature. Press Ctrl-W to expand syntax selection. pic.twitter.com/FsgkCWnO3c

— Robert Love (@DelphiDev) April 21, 2016

Rad Studio 10.1 Berlin, supports a great list of Z-Wave and Bluetooth IOT devices. pic.twitter.com/KNULTKfzLB

— Robert Love (@DelphiDev) April 21, 2016

SQL Injection - Stored Procedures

Security needs to be multiple levels deep. At one point in time several years ago there was a trend to have all websites code use stored procedures. I heard developers say it prevented SQL Injection.

If the database you use allows you to build SQL statements with a string dynamically at run time in a stored procedure then you can have SQL injection in a Stored Procedure.  

Each Database slightly different syntax and guidelines:

• Oracle  (See Page 12)
• PostgesSQL 
• SQL Server

In short: If your doing dynamic SQL inside your Stored Procedures you will have validate the input otherwise you can be impacted.

SQL Injection

I just wrote a blog posts on security assumptions. I realized there was a huge missing assumption. (This has since been updated.)

•  Assume that all input can be malicious 

Today I want to show the most common mistake, and exploit that I have ever seen. It impacts all languages and all SQL Based databases. It's called SQL Injection. I have heard developers make the incorrect assumption that this only impacts web based applications. I have seen this problem in all types of applications that touch a database.

Today's example is VCL Desktop application uses FireDAC to access a SQLite database.

The example code used here can be found in the security-demo GitHub repository.

SQL Injection occurs when you don't use Parameters, which allows input to modify your SQL statement.

Here is the unsafe way to do it:

   
  FDQuery1.SQL.Add('select * from tbl1 where name like ''' + edtSearchTerm.Text +'%''');
  FDQuery1.Open;

With this example if I entered the following into edtSearchTerm.text

a ' union select name, sql from sqlite_master -- 

I will get a list of tables in the database, with that it's simple to see that there another table called salary in the database. Where I can then enter the following to get all the salary information.

a ' union select name, value from salary --

Here is the safe way to do it:

  FDQuery1.SQL.Add('select * from tbl1 where name like :name');
  FDQuery1.ParamByName('name').asString := edtSearchTerm.Text + '%';
  FDQuery1.Open;

Now I can enter both strings that attempt SQL Injection and they will fail. This is because they end up inside the parameter and thereby can't modify the sql. Simple to avoid, but still a common mistake that that is made when it comes to security. Testing for SQL Injection is pretty simple you enter try terminating the input with single and double quote. If you get an unexpected error odds are you have a possibility of SQL injection.

We tend to focus on user input, but API's like SOAP or REST end points are also common targets for SQL Injection attacks. That is why I have been very careful to say all input, and not all just user input.