Im writing an app that customers will use. Will it be a good idea to write an app that accepts sql queries directly in the request payload. But these endpoints have authentications too. Where it can possibly go wrong?
What if your "customer" is malicious? Just because they're authenticated doesn't mean they won't try anything nefarious.
If your app is marketed as directly interacting with a database using SQL language, then I can see why this would make sense. But in that case, you should definitely make sure that every customer interacts with an isolated database from a non-root user. That way they can fuck up the data all they want, but it will only be their data.
Yes I had the same thought but I just wanted to know how many vectors other than this will be possible.
Let's say the customers are not doing anything like that. Now do we have any other vectors?
Are your customers actually writing SQL for executing against a database or is your app generating SQL? If instead the app's purpose is to provide a "power interface" to access data (read only?) and generate SQL, there are better choices. Use https query parameters to map to parameterized queries (https://blog.codinghorror.com/give-me-parameterized-sql-or-g...) which are a much better design choice.
I wouldn't write an 'app' that accepts SQL, but I've given out database access to clients who had software developers on-staff. It's a quick way to give access to data, and you can use permissions to restrict them. You'll want to give them their own private database.
I wouldn't give out general access to a database to the wider internet. There are plenty of ways to cause trouble even with read-only access. Transactions and expensive queries are the first ones that come to mind.
10 comments
[ 3.7 ms ] story [ 29.3 ms ] threadIf your app is marketed as directly interacting with a database using SQL language, then I can see why this would make sense. But in that case, you should definitely make sure that every customer interacts with an isolated database from a non-root user. That way they can fuck up the data all they want, but it will only be their data.
If you provide the users with restricted DB access privileges, maybe.
Are your customers actually writing SQL for executing against a database or is your app generating SQL? If instead the app's purpose is to provide a "power interface" to access data (read only?) and generate SQL, there are better choices. Use https query parameters to map to parameterized queries (https://blog.codinghorror.com/give-me-parameterized-sql-or-g...) which are a much better design choice.
I wouldn't give out general access to a database to the wider internet. There are plenty of ways to cause trouble even with read-only access. Transactions and expensive queries are the first ones that come to mind.
delete from table foo