Ebase Xi (from ebasetech.com) 4.5.2 is a rapid application development platform I recently encountered at a client. The previous developers had left and a security audit revealed that the (many) forms they built with Ebase Xi were susceptible to SQL Injection. In this blog post I will tell how I fixed the SQL Injections and discovered some interesting things along the way.
Nowadays its common wisdom that SQL Injection is easily prevented by using prepared statements. These force you to use explicit parameters and by doing so make it impossible for the sql parser to misunderstand the sql and parameter boundaries.
Before prepared statements it was common to build sql statements by appending strings and values to each other. If one of the values contained a quote, part of the value could escape and become part of the query.
String unsafeQuery = "select * from users where username='"+name+"';"
select * from users where username='Erik';
What would happen if the string "Erik" would become "Erik' or '1'='1" ? The query would do something different altogether.
select * from users where username='Erik' or '1'='1';
Instead of returning a single named user it would return all users!
One checkbox to solve all problems..
Ebase Xi supports both prepared statements and string concatenation. Unfortunately the default one is string concatenation. Switching between the two modes is easy, its just a single checkbox that needs to be set.
Easy, just check the checkbox and you're done! Right?
Unfortunately there is another complication. The Ebase Xi forces you to predefine all column and parameters you will use in your query. This is not very flexible and so ingenious minds thought up a hack that bypasses these restrictions. It requires you to define a special non persistent parameter of type Integer, typically named &&QUERY. Its non persistent because its used as input only, but more importantly because its type Integer Ebase won't put quotes around this value. Also you can put in any string you like (no type safety here!) . And it only works because of string concatenation.
You can now build your query string in the FPL Scripts and concatenate strings and values as you like. Since they're built outside the scope of the database resource, no amount of prepared statements checkboxes will make these queries safe.
Ironically, Ebase Xi supports a safe way of doing these kind of dynamic queries by means of the Dynamic Query checkbox. This will cause the runtime to evaluate the query string twice to find any named parameters. For this to work, all parameters must be named and known in the database resource. So while its possible to make these dynamic queries safe, it does require the rewriting of the sql statement using parameters.
set QUERY="select * from users where name='"+NAME+"'";
set QUERY="select * from users where name=&&Q_NAME";
set Q_NAME = NAME;
As you can see rewriting these queries is not difficult, however if you have a great variation of parameters it can become quite a challenge to get it right. Also I've chosen to create separate (non persistent) query parameters as the original parameters may be already in use for something else. Typically making these queries safe involves the following steps:
- check the use Prepared Statements checkbox.
- check the dynamic Query checkbox on the query parameter.
- scan through all the scripts to identify the queries and which parameters are used.
- add non persistent query fields for each of the parameters.
- add form fields for each of the parameters and map them.
- rewrite the queries and assign values to the parameters.
Check, check, double check.
Checking if you've successfully fixed a query is easy if you enable the debug checkbox on the database resource and follow the logging of your web-application server. (Yes the designer also allows viewing the log, but this is easier) If the query strings contains question marks and the log lists the parameters separately you're good to go!
DEBUG Debug for database resource SYSTEEM_EMAIL_FORM - SQL statement:
DEBUG select EMAIL_ADRES from ABC_FORM where id in (select ABC_FORM_ID
from ABC_REQUESTS where id in ( (select ABC_ID from ABC_CASES
where cast(id as varchar2(10)) = ?)))
DEBUG parameter1 : value 13470
DEBUG End execution of command - fetch : 11:58:37.703
Oops: Dynamic Lists
With all queries fixed and checkboxes in place I thought I was done. Then my eye fell on another query. No question marks and a quoted value. F***P. Did I forget something? A quick investigation revealed that it was not a database resource but a query made by a dynamic list!
In Ebase Xi Dynamic Lists are used to fill comboboxes and the like based on a query that supports parameters. Unfortunately: No prepared statement checkbox here (sad) That means that if you use Dynamic Lists with user supplied query parameters you're still susceptible to SQL Injection.
It turns out that there is a non intuitive workaround for this. The Dynamic Lists support the Dynamic Query checkbox as well. If you use a dynamic query (one with a externally supplied sql statement) Ebase will use a prepared statement.
It is possible to write sql injection safe queries using Ebase Xi. In fact, its even explained in the manual. Sadly the defaults are unsafe and in my experience the form developers are simply unaware of this which results in unsafe systems. Fixing the queries is not difficult but just a lot of work.
Since internet facing Ebase Xi servers in their default configuration are easily identified using Google, I just hope the developers of those systems know what they are doing (and that the default configuration of Ebase Xi is safe - probably not judging from the above..)