cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Injection in Crystal 2014

Former Member
0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

JWiseman
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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!

JWiseman
Active Contributor
0 Kudos

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.

Former Member
0 Kudos

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

JWiseman
Active Contributor
0 Kudos

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.

JWiseman
Active Contributor
0 Kudos

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

Answers (2)

Answers (2)

costa-b
Explorer
0 Kudos

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?

DellSC
Active Contributor
0 Kudos

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