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.