3 ms·
Is it a good to write an app that accepts sql at an authenticated endpoint?
- andrewvijay 10y agoIm 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?
- gcatalfamo 10y ago1) Is this over SSL? 2) Why not prepared statements?
- andrewvijay 10y agoYup it is over SSL. Prepared statements? What does it mean? You mean building the query in the server?
- chatmasta 10y agoWhat 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.
- andrewvijay 10y agoYes 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?
- davelnewton 10y agoWhere can it go wrong? Just about anywhere, through malice or ignorance. If you provide the users with restricted DB access privileges, maybe.
- tom_b 10y agoNo. That's the safe, short answer. 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-give-me-death/ https://blog.codinghorror.com/give-me-parameterized-sql-or-g...) which are a much better design choice.
- brudgers 10y agoCurious what problem it solves.
- MichaelBurge 10y agoI 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.
- flukus 10y agoWhat happens when they miss a where clause?: delete from table foo