on 03-31-2015 9:58 PM
We have discovered an injection attack in Crystal Reports. Both 2013 and XI R2 were tested and are affected.
1. Create a new report using, and report against a SQL command using a parameter. eg:
SELECT * FROM customers WHERE cust_no='{?cust_no_param}'
2. The user can provide arbitrary SQL during parameter selection. eg:
'; DELETE FROM customers; SELECT '
I've confirmed that the parameter tag is substituted verbatim for the text entered by the user. This results in the following query:
SELECT * FROM customers WHERE cust_no=''; DELETE FROM customers; SELECT ''
I would have brought this directly to the attention of the Crystal Reports team at SAP, but I can't actually find any contact info for them.
hello James,
Command objects were new to version 9 and the intent was to allow any syntax that the database driver would allow through its layer. This means that the CUD of CRUD operations would work through many odbc drivers. This was done with consultation with major customers' DBA's who agreed that the onus was on the DBA to ensure that the appropriate user rights would be assigned. These days there are only a handful of drivers that do allow the CUD of CRUD.
Another choice other than database level rights and security is to create the command object in such away as to prevent sql injection. In your case you may be able to use a numeric prompt/parameter and then to_text() it in the command or ensure there is a length mask on the prompt or ensure that in the command that the value does not include the lowercase "delete".
-jamie
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
While the goal of providing more power to DBAs is certainly laudable, I still have serious misgivings with this particular feature.
Such a feature should be implemented so that it escapes the parameter by default, but can be disabled to grant the report author further flexibility. Enabling this 'unsafe mode' should come with a warning that the report may be vulnerable to injection attacks.
CUD is not the only issue either; I was able to formulate an attack that would retrieve arbitrary values from the database by moving the parameter to the field list and executing a subselect.
There is also a simple usability problem as well; in my original example a value passed to the parameter such as "AL's" would cause a database error. There appears to be no way to escape quotes from within the command object. Furthermore, since this is inconsistent with the handling of parameters I cannot simply escape incoming parameters in my application without having knowledge of how the parameter is being used in the report!
commands are wide open and one of the intentions is to allow dynamic sql. again, its really up to the report developer and the dba to limit what they want to happen in a command object. if a report developer is not familiar with sql (both security and performance) they should probably use table based reports instead. it's also up to the report developer to ensure that they don't allow run away queries.
see Dell's suggestion below as the first step to create a reporting user with read only access on the db. then see your online help for crystal on edit masks. the edit mask can be used to ensure only certain characters get written into the prompt dialogue. it can also be used to control the length of the entry...if you've got a customer id of 9 characters or less then you can ensure that no more than 9 characters are entered.
also anything that you wish to do inside the command object itself must be written in your database syntax as there is no crystal functions etc. that will work inside there. if you require crystal functions base your report off of tables and then use the record selection formula. the warning there though is that certain syntax in your record selection may not be passed to the database for processing there. so if you're not too bad at sql then the command object is usually best for performance.
Jamie Wiseman wrote:
also anything that you wish to do inside the command object itself must be written in your database syntax as there is no crystal functions etc. that will work inside there.
The very fact that command objects are passed verbatim to the database and crystal functions are not available means that injection attacks aside, if I want to pass a parameter containing an apostrophe the ONLY means at my disposal is to tell users that in this case they must escape the apostrophe.
I maintain that this sort of verbatim substitution needs to be opt-in. In any other case the parameter values should be escaped or use driver/database level parameter binding. eg:
SELECT * FROM customers WHERE cust_no=@cust_no_param
suggestion would be to use a display value and a value you wish to pass in the parameter. i.e. you display the customer name and pass the customer numeric id. then you can avoid having to escape or use like etc. there are other workarounds of course that don't involve escapes etc. but having the display and then the passed value is the easiest.
p.s. i see that you just joined scn today...welcome. if you see anything that you think should be added to crystal reports please see the idea place. https://cw.sdn.sap.com/cw/community/ideas/businessanalytics/crystalreports
pretty sure that there's already a suggestion to prevent errors related to characters. if it's there you can always vote it up.
personally i'd like to see a lot more added to commands as well.
-jamie
I have to same issues as you do. It's a bit scary actually.
I wonder why they didn't use bound parameters which would have solved the problem. In other words, if you have a query:
select * from SomeTable where field = {?ParameterName}
this would get translated to:
select * from SomeTable where field = : ParameterName or select * from SomeTable where field = @ParameterName.
and then the library would bind the parameter values to the parameters.
How hard can it be to do it?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Another way to prevent this type of SQL Injection from causing harm would be to run all of your reports using a generic "reporting" user ID in the database. If that ID is only given rights to select data and, potentially, run functions or stored procedures, then the database security will prevent that kind of attack from succeeding.
-Dell
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.