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.