Monday, April 18, 2016

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 +'%''');

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 + '%';

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.


  1. This comment has been removed by the author.

  2. what if the data field i am searching for is an address and has characters like ' , ; embedded?
    i get an sql error. is there a way to encapsulate these characters?

    1. That's exactly why you want to use parameters. For parameters it doesn't matter which characters they contain.

  3. Thank you for this series of articles. I believe it is important to raise the level of security awareness among developes who may be used to a far lower level.

    I would suggest to adopt a "defensive programming" approach today, which could be renamed "secure programming". Code should be able to foresee and identify unexpected situations and react properly. One rule, as a colleague of mine often repeats, is "never trust input" (and rule zero could be "never hide errors/exceptions"...)

    Especially when you have no control over what produces the input. Never assume the input will be "correct". One widely employed technique to find exploitable bugs is to "fuzzy" inputs and see what happens - and it can be automated. Input is whatever can be fed to your application from outside, using any technique.

    Thereby always check and "sanitize" inputs. Beyond SQL Injection, bad inputs can trigger buffer overflows, can be used for heap sparying, and so on.

  4. Would this also work?
    FDQuery1.ParamByName('name').asString := edtSearchTerm.Text + ' -- a comment to block any other SQL';

    1. That would not work. The parameter would contain the comment. Parameters are not using string concatenation. They are place holders send to the database engine. The parameter values are send separately and can not change the SQL being sent.

  5. Using queries with parameters are recommended by SQL servers producers as main part of SQL will be already compiled on second call and only parameters will be passed. So this should decrease time of execution. Also recompiled queries allow SQL Server to collect statistics and optimize the query.