0

I have an application (Crystal Reports) that inserts a users parameters directly into the query it is running.

The nature of the queries I need to run means I can't change this behaviour.

I've found that this allows users to run any SQL commands they like via the report.

For example, my SQL query is something like:

declare @text varchar(100)
set @text = '{users parameter}'

select *
from blah
where blah.field = @text
-- rest of query

So if a user sets the parameter to '; select * from blah; -- it will run this query.

I am already running the command via a very limited user but still want to try to prevent injections.

Is there any way to prevent or at least mitigate them via the query I am running?

Something like:

declare @text varchar(100)
set @text = NeverEscape('{users parameter}')

select *
from blah
where blah.field = @text
-- rest of query

Edit

To clarify how there is a vulnerability, the application passes a single text query to the server, in the example I gave, the following query would be passed to the server:

declare @text varchar(100)
set @text = ''; select * from blah; --'

select *
from blah
where blah.field = @text
-- rest of query
bendataclear
  • 3,802
  • 3
  • 32
  • 51
  • To which degree of dynamism do you build your query? Do you use dynamic objects like tables and columns, or just filter values? Your example can be made safe with `sp_executesql` and passing the appropriate parameters. – EzLo Feb 28 '19 at 10:56
  • The code you've posted here is safe from SQL Injection attacks. If this is your actual code, I don't see how any input can ever actually inject SQL. For further reading - [How can prepared statements protect from SQL injection attacks?](https://stackoverflow.com/questions/8263371/how-can-prepared-statements-protect-from-sql-injection-attacks). Please note that this doesn't mean that any time you are using parameters you're safe - it's still possible to be vulnerable to SQL Injection even when using parameters - [Read this for more information](https://stackoverflow.com/questions/51655239). – Zohar Peled Feb 28 '19 at 10:57
  • @EzLo `sp_executesql` won't prevent this injection, the query is just using `field = @param`, the injection is at the point of setting the variable. – bendataclear Feb 28 '19 at 11:03
  • @ZoharPeled I've expanded the injection which should show how this is a problem. prepared statements aren't a solution as the injection point is at the point of setting the variable. – bendataclear Feb 28 '19 at 11:05
  • So this is a dynamic SQL - you are concatenating strings with user input to generate the SQL that's being sent to the server. Than why use parameters in the first place? The entire point of parameters is to avoid string concatenations... In what language is the application written? – Zohar Peled Feb 28 '19 at 11:09
  • Like the others, there's nothing about the queries we have that are injectable; which means the SQL isn't the problem. You state that the aopplication you are using is Crystal Reports. i suggest tagging that and asking a question on *How to make parametrised queries in Crystal Reports*, what we have here is actually misleading as it makes it seems that the SQL is the problem; not the application. – Thom A Feb 28 '19 at 11:22
  • A [google](https://www.google.com/search?client=firefox-b-d&ei=i8R3XKbpIsqxgwe6g4X4Ag&q=Parametrised+query+Crystal+Reports+without+injection&oq=Parametrised+query+Crystal+Reports+without+injection&gs_l=psy-ab.3..33i160.7280.10400..10808...0.0..0.154.1676.15j3......0....1..gws-wiz.......0i71j0i22i30j33i22i29i30j33i21.F5Pk3t9yReo) of the problem led me to this [thread](https://archive.sap.com/discussions/thread/3721138), which (to me), gives a "we don't really care it's a problem" attitude from the people at SAP. – Thom A Feb 28 '19 at 11:26
  • @Larnu Yes the issue is inherent to Crystal reports, and as SAP don't care I'm just trying to make injection as difficult as possible. I've already spent days looking for ways to get around this within Crystal Reports to no avail. – bendataclear Feb 28 '19 at 11:38
  • @ZoharPeled The application generating this SQL query is Crystal Reports, it's very inflexible. The purpose of using parameters is for re-use as well as to make the passing in of variables easier to read/maintain. – bendataclear Feb 28 '19 at 11:40
  • the fact that it replaces the parameter with a literal value actually does the opposite of making the query reusable. SQL Server will treat the queries `SELECT * FROM [Table] WHERE ID = 1;` and `SELECT * FROM [Table] WHERE ID = 2;` and 2 completely different queries; where as `SELECT * FROM [Table] WHERE ID = @ID;` would be treated as "the same" for any given value of `@ID`, because it can use the cached query plan. I've edited the subject and tags to try and get this question some attraction from users familiar with Crystal Reports. – Thom A Feb 28 '19 at 11:43
  • The Crystal Reports tag is really important here @bendataclear. The problem is because of Crystal Reports, not SQL Server. Those with familiarity with that application are going to be the users with the greatest benefit to you. – Thom A Feb 28 '19 at 11:44
  • Well, fortunately for me the last time I had to work with Crystal Reports was 3 or 4 jobs ago - and even then it was only for a short while. On the other hand, this means that I can't provide and good answer to this question since as Larnu pointed out, the problem is clearly on the client side (client here being the Crystal Reports application). – Zohar Peled Feb 28 '19 at 11:48
  • @Larnu Normally I would agree however I've spent years trying to deal with this problem from the Crystal side and I think those familiar with Crystal will suggest one of the 20 work-arounds I've already tried and discounted. – bendataclear Feb 28 '19 at 11:51
  • Then maybe it's time to get rid of Crystal Reports and use a *well designed* product? Apart from limiting the permissions of the user(s), there's nothing SQL Server can do to help you here. If an application is open to injection, it's open to injection. You can *limit* the exposure on the SQL Server side with permissions but when the vulnerability is the application, it's the application that needs fixing. If you have a slow puncture in your tyre, filling it back up doesn't "solve" the problem, it just hides it (for a short time). Fixing the tyre is the solution; the same logic applies here. – Thom A Feb 28 '19 at 11:55
  • @Larnu Agree with you, ditching Crystal is always the goal, however there's not a suitable replacement sadly. – bendataclear Feb 28 '19 at 12:01
  • I still, however, suggest you leave that tag there. The responses you're going to (likely) get from the SQL community is fix the application; SQL Server is **not** the problem here. I'd added that tag twice, and I've given my view. There's little we can do to help you here though, as the problem is the application, but that's the one thing you don't want to address. Thus, we have come to an impasse I'm afraid. – Thom A Feb 28 '19 at 12:08
  • @Larnu SQL server isn't the problem no, however it's the only variable I have control over. The big reason for removing the CR tag is that those unfamiliar with it will avoid the question for that reason. – bendataclear Feb 28 '19 at 12:13
  • @ZoharPeled Understand that Crystal is the problem however there's no Crystal solution, the only control I have is via SQL server – bendataclear Feb 28 '19 at 12:14

0 Answers0