0

The application in question is integrating with BigImportantThing (BIT). Part of our application's job is to provide a better interface, so this is something to resolve within our application. The application is written in Java.

We need to fetch some data from BIT's database. The data structure is complicated, so the only interface we can access it with that provides all we need is a generic query one (BIT team provided a convenient view that collects all the parts we need in the way we need them).

The only parameter it accepts is a SQL query, which BIT will run against it's own DB and return the results. The message we sent is built using a library provided by BIT, so appropriate handling of whatever SQL we generated is taken care of by that. Going by the naming, I'm pretty sure it is executed as read-only at least, however there is no place for me to put query parameters as I would want to. Thus I see no way to use a prepared statement or anything similar.

Compounding on the problem, some of the search parameters are supplied by our client or other services, and do contain special characters. For now I've at least added escaping for the string delimiter, to at least block the obvious hole, however I'm still not satisfied with this.

Everything I find about dynamically building SQL queries says to use prepared statements or something equivalent and that it's always possible. I'd love to do so, but I feel like I've found a genuine exception to the rule. Is there any advice available for this sort of situation?

EDIT: When I mention the BIT interface, it's more or less a library to build requests which get sent over a HTTP connection, the library then parses the response. The library thus provides the structure for valid messages.

Mr Redstoner
  • 109

2 Answers2

3

  1. As long as you are the one who is generating all parts of the SQL query (with no parameter values coming from a 3rd party), and as long as you are going to play this nice with the BIT team, this should not be much of a problem. When you have all parts of the query under your control, just do not create a malicious one.

  2. When parts of the SQL parameters com from an external source, there is probably no way around escaping those by yourself. Maybe you are lucky and find a Java library which does this for the SQL dialect of the BIT, otherwise, you may start at this SO question to find some resources. Look specificially at the not-so-high voted answers and the end.

Still, you should ask yourself - is this really your problem? Or shouldn't it be first and formerly the problem of the BIT team?

The BIT maintainers may themselves have an interest to secure their system against SQL injections, hence maybe they are willing to provide you a safer interface when you just ask them kindly. Maybe their interface is more secure as it looks like, or maybe they have tips for you how to use the interface in a secure way. Maybe the responsible devs haven't heared of Bobby tables before and just need a 10 minute introduction into SQL injections. Or maybe - if nothing else helps - you have to get sneaky on them and demonstrate how their interface can be prone the SQL injections by making a life demonstration. But beware, if you are not careful, such an action can destroy good working relationships when it is not properly communicated.

So part of your options depend on your relationship to the BIT team, of which you did not tell us much.

Doc Brown
  • 218,378
0

Then it is up to you, to ensure parameter safety. This is not difficult, just needs much care. Some aspects touching limitations, such as the Unicode tricks: right-to-left, multibyte hacks, overflows, attempts to explode the resulting data, may need some overly restrictive measures. Furthermore logging, and things like JMeter checking the state are needed.

I would advise to be able to extract out of the software (doclets, annotations) all measures implemented. Not only helping in a critical review, post mortem analysis, but also averting blame should still something happen. Most importantly documenting the effort otherwise safety gaps will happen in future.

P.S. One always can make ones own Prepared Statement creating the plain query.

Joop Eggen
  • 2,629