1

I have an application in which I'd like to accept a user supplied SQL query from a front-end query builder (http://querybuilder.js.org/). That query eventually needs to make it's way to running in a postgres database to return a subset of data.

The query builder linked above can export SQL or a mongo query. I imagine using the mongo query is relatively safe, since I can add to it simply on the server:

query.owner_of_document = userId

to limit results (to documents owned by the user).

Whereas the SQL statement could potentially be hijacked in an injection attack if someone attempts to store a malicious string of SQL for execution.

Is directly accepting SQL from a client bad practice? How can I ensure the supplied SQL is safe?

Thanks!

Patrick DeVivo
  • 755
  • 1
  • 9
  • 13
  • Try to define *safe*, and you'll find it hard to ascertain it in SQL (think of common table expressions, subselects, etc.) – Laurenz Albe Oct 16 '16 at 04:05

1 Answers1

3

Why do you need to accept an entire SQL statement?

Can you accept only parameters and then run a pre defined query?

There are loads of questions/answers on SO relating to SQL injection and using parameters is a first step in avoiding injection attacks, such as "Are Parameters really enough to prevent Sql injections?"

But I think this answer to a different question sums things up well:

Don't try to do security yourself. Use whatever trusted, industry standard library there is available for what you're trying to do, rather than trying to do it yourself. Whatever assumptions you make about security, might be incorrect. As secure as your own approach may look ... there's a risk you're overlooking something and do you really want to take that chance when it comes to security?

Community
  • 1
  • 1
Tony
  • 9,672
  • 3
  • 47
  • 75