Not to expose SQL queries while passing queries using REST

[OmkarPathak]

SQL Queries are exposed while passing payload to https://api-fberry-demo.geex-arts.com/jet_api/sql/. The right way should be to build queries on the go in the backend only. Any specific reason why queries (literally) are passed in payload? Due to this table names in the database are exposed to the world which is a security risk.

[f1nality]

The reason for it now – is that you can create any query on frontend (data analytic for example) without need to address developers for it. Do you suggest to write queires on backend side?

Also if you are using HTTPS than data can't be sniffed.

[OmkarPathak]

@f1nality Yes I strongly recommend building queries on backend. Although data cannot be sniffed due to HTTPS, people can still see the database names and table names which opens the door for other attacks

[f1nality]

@OmkarPathak yes, but admin panel is not something public. only those you give access will see it. SQL analytics services like https://redash.io/ works the same way. i think a better solution will be to add SQL permission for configuration. so that only who you will give this permission will see table names. SQL queries saved by admins can be encrypted so that thoose who don't have such permission won't see queries text. what do you think?

[OmkarPathak]

@f1nality No you didn't get me. See the below highlighted part in the image:

We can easily see what query is being passed. This shouldn't be the case

[f1nality]

@OmkarPathak
yes, that's what i've understood. my idea is to encrypt this query, so only backend can decrypt it. users that will have sql permission will be able to see these queries though.

building queries on backend will eliminate advantage of building queries on frontend, its very handy.

[do-rtk]

I agree with @OmkarPathak you should get rid of this.

While your usecase of allowing users to build queries might be cool, having no way to gate what queries can be constructed is really dangerous. What if I construct a query deleting everything? Also, this creates a workaround for some control measures e.g. Query manager only allows user to see data in their org, but with sql queries, user can simply go around that.

If you really want users to be able to construct something, I'd suggest using building blocks that you can easily control in the backend not SQL. It's incredibly difficult to sanitize and guard SQL. Totally not worth it for the HUGE security loopholes it creates.