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.

Security assumptions

When it comes to application security there is lots of discussion on the Internet about web applications and network security.   I however found far less information when it comes to desktop applications and other applications that typically reside behind a firewall.

For example the initial version of App Tethering communication was clear text.    It has a password but it was sent in the clear.    The reasoning I was given that the original was sent in clear text was that it was intended to be used behind the firewall.    This design flaw has since been resolved.  
By default your data in the communication is still clear text, although your passwords now use a HMAC method for authentication with each other.    You now have optional hooks where you can encrypt the data.     Not as nice as communicating over TLS, but is far more secure that the original version.

I tend to take a different approach; I make the following assumptions when it comes to security when I code something.

• Assume there is no firewall and hackers can hit any server you publish.
• Assume a server, network switch or router on your network will be compromised and will be collecting your data.
• Assume the machine your code is on will be compromised and/or stolen.
• Assume that all input can be malicious 

Every day computers around the world are infected with virus, malware, etc...  

These are sometimes these might have been caught by a scanner and sometimes they can be slip past the scanner.  So although a virus scanner is a good idea, it's not perfect. Given that you have to assume that someone on your network is infected, and a hacker now has access to a machine behind the firewall.   

If an application takes these assumptions into account during its design, it will help prevent a data breach from occurring. 

Data Breaches are costly just ask Target and Home Depot.  So spending some time upfront can go a long way in reducing potential unexpected downstream costs.

New Rad Studio Coming - Security?

On April 21st & 22nd there will be a Webinar that goes over the Highlights of RAD Studio.

 Embarcadero presenters will discuss among other things the following topics:

•  A new installation tool with GetIt technology and the choice of what you want to install 
•  Extended support for Bluetooth LE on Windows 10 and a framework for IoT components 
•  FireUI App Preview - Preview your forms on any target device (desktop or mobile)

It looks interesting,  I have had many complaints with installer over the years for taking up too much disk space.   I am also working on IoT devices (usually building them) every week now.   So I am excited to any improvements in this area. 

I also wonder how much of it will have improvements in the security areas that I have concerns with.  Granted most of my concerns deal with the websites more than the product.   I suspect I won't get the information I want in this webinar, but will only come through a review of the product.  

I started contacting Embarcadero regarding several specific security issues on  Aug 30,  2015 right before the Idera purchase.    Some of these were addressed: for example the community toolbar in Rad Studio no longer uses an unencrypted session when you log in.    AppAnalytics uses HTTPS instead of HTTP.

But nothing appeared to change on the websites then on March 12th Embacardero website was hacked,  After a couple of "I told you so" emails, problems were escalated.    I produced a multi page security report detailing issues with every Embarcadero website.   This finally generated some action.    I had a very good call with Atanas Popov the General Manager of Embarcdero's Developer Tools we discussed how they can improve security on both the websites and products.      I now know they are listening, I am now watching to see if the listening turns to action.     

I love Delphi it's a great tool I want to see it succeed and has had a very positive impact on my career.  Given that it's been really difficult to be positive for the past several months, so I have chosen instead to be quiet, but I believe it's time to be publicly vocal.    In my job, I have to deal with security concerns all the time.   My employer is constantly under attack.   So harding systems and software is always a concern.    So I am required to demand that from my from the vendors we use.  If a vendor fails to take security seriously it has a huge negative impact my ability to use that vendor.

So here is to hoping we get some greater transparency and action on security issues.     

Raspberry Pi 3 - Arrived and setup.

After a bit of waiting my new Raspberry Pi 3 Model B just arrived.  

I wanted to share a few of my steps I used in getting started and paired up with my Mac for easy development.

Having played with both Raspbian and Microsoft IOT Core with my Pi 2.  I currently
prefer to use Raspbian, so this article will focus on that.

1.  Download & Install the Raspbian on an SD Card.
    If you're not familiar with dd then you should consider using the NOOBS install method as it's painless.

    Raspbian is based on Debian Jessie or Jessie Lite (Depending on the choice you made)
    I used the one based on Jessie.
 
2.  Insert SD Card, Hook up Monitor, Mouse, Keyboard, and a network cable.

3.  By default the Raspbian installation ships with pi as the default user and raspberry as the default password.   Unless you plan on keeping your Pi off of any network you need change this.   It's just a matter of time until a worm will be developed looking to infect a Pi with a default password.  So change the default password and if desired create and use a different user all together.

4.  As with all new OS installations you should perform updates right after installation.
    This is very good security practice.   It's also the good to do on regular basis.  
   
    To update Raspbian you use apt-get
    The following two commands will get you updated.
 
    sudo apt-get update
    sudo apt-get dist-upgrade
 
    The first updates the list of packages.
    The second performs all of the upgrades and manages the dependencies that they require.
 
    You will want to do this on a regular basis to keep your machine updated.
    If you want you can setup cron to automatically do this.    After reading the article check out the comments for a good bash script, that will email you when updates are available.
 
5.  Change the hostname if you want.   Since I have multiple pi's I rename each image to be unique.
     If you have the graphical interface you can select the following menu to rename the hostname.

• Menu (With the raspberry image on it)
• Preferences
• Raspberry Pi Configuration

    If you don't have the gui you can edit two files to change the hostname.
       
6.  If you have Pi 3 or an earlier Pi with a USB Wifi Adapter you may want to setup wireless.

7.  Now I set up Fuse for OSX on my Mac so I can make my PI a mount point on mac.   This article I found covers how to set this up.    This allows me to use editors on my Mac to edit files on my machine.   I then use SSH to execute that code on the machine.    I then can disconnect the keyboard, monitor, and mouse from the Pi as it's no longer required as I can use my main machine to easily control it.
 
8.  If you need access to the graphical interface from your mac you can do that as well using VNC, due the nature of what I want to do next I am not installing this at this time.

9.   Beware although this setup works.   You are storing your code directly on the Pi.   So when coding you do run this risk of losing your code if something bad happens.   So since git is preinstalled on Raspbian, I tend to initialize a repository in my code directory, setup remote origin and push the changes to a remote repository.     That way you don't lose your work that is on the pi.  I love BitBucket for this as you can as many free private repositories as you want.