3 ms·

Just a pro-tip to anyone looking to persist information coming in from remote clients... please, please, please never directly expose your DB (or effectively ex

by munk-a 4y ago

Just a pro-tip to anyone looking to persist information coming in from remote clients... please, please, please never directly expose your DB (or effectively expose your DB by having SQL generated by the client or using a library to autogenerate SQL based on a query request)... Just put a dumb layer of PHP/Node/Python/whatever you like between clients and the DB. It is immensely difficult to properly secure data if uncontrolled clients can connect to the DB - it is possible - but it takes far more work than you'd like to put in. Putting a thin application layer in front of the DB means that you're effectively whitelisting what operations are legal to run on the host and that makes your security life so much easier.

agsdgasdasdg 4y ago
or, alternatively, always treat your database roles as though they can be directly manipulated by the end user, and you'll eliminate an entire class of injection vulnerabilities. my favorite example of this is SchemaVerse, a game fully implemented in pgsql. https://schemaverse.com/ https://schemaverse.com/
gkop 4y ago
Or just use NoSQL instead (eg. Mongo)
- saghm 4y ago
  There are still some database injections that are possible in MongoDB (although they definitely tend to be less common due to the query language not being text-based). The most common type is when someone directly uses the data from a quest in the query without checking the type; for example, if you have a query that says "delete the user with id ____" and then pass in the id directly from the POST request without checking the type, someone could make a request with a body of `{ "$nin: [] }` (i.e. "not in the empty array"), which would then delete every user in the database. The fix, in this case, is just to check the type of the request data before serializing it into the query; in the example above, you could just return an error if you get anything other than a single user id.
  - gkop 4y ago
    Sorry, I made a stupid joke. Thanks for teaching me something here, this is more than I deserve.
    - molyss 4y ago
      Here, take my upvote. Can’t remember the last time someone seemed to genuinely apologize, explain what act they’re apologizing for, acknowledges their knowledge gaps and seems to actually thank their corrector, all the while sprinkling the comment with a hint of self-deprecation…
    - BongoMcCat 4y ago
      This is why things like "/s" are useful. I also didn't understand that you were making a joke, and was sitting here wondering how using a different type of database would solve this kind of problem.
    - saghm 4y ago
      Similar to the sibling comments, I genuinely didn't realize you were joking either (although looking back now, it definitely seems more obvious!). Either way, I tend to be in the minority that doesn't mind jokes on Hacker News, so I'll give you an upvote as well
mjcl 4y ago
Please, yes. At a previous job, a department used a Windows accounting app that had a machine-level ODBC connection with the database credentials saved in it. A user figured out you could use Microsoft Query to pull data from the ODBC connection into Excel. This worked well until she accidentally deleted 90% of the vendor records from the database while trying to filter some data. I couldn't even really be upset at the user, a bunch of dumb product design decisions had to align like swiss cheese for this to happen.

by munk-a 4y ago

agsdgasdasdg 4y ago

or, alternatively, always treat your database roles as though they can be directly manipulated by the end user, and you'll eliminate an entire class of injection vulnerabilities. my favorite example of this is SchemaVerse, a game fully implemented in pgsql. https://schemaverse.com/ https://schemaverse.com/

gkop 4y ago

Or just use NoSQL instead (eg. Mongo)

saghm 4y ago
There are still some database injections that are possible in MongoDB (although they definitely tend to be less common due to the query language not being text-based). The most common type is when someone directly uses the data from a quest in the query without checking the type; for example, if you have a query that says "delete the user with id ____" and then pass in the id directly from the POST request without checking the type, someone could make a request with a body of `{ "$nin: [] }` (i.e. "not in the empty array"), which would then delete every user in the database. The fix, in this case, is just to check the type of the request data before serializing it into the query; in the example above, you could just return an error if you get anything other than a single user id.
- gkop 4y ago
  Sorry, I made a stupid joke. Thanks for teaching me something here, this is more than I deserve.
  - molyss 4y ago
    Here, take my upvote. Can’t remember the last time someone seemed to genuinely apologize, explain what act they’re apologizing for, acknowledges their knowledge gaps and seems to actually thank their corrector, all the while sprinkling the comment with a hint of self-deprecation…
  - BongoMcCat 4y ago
    This is why things like "/s" are useful. I also didn't understand that you were making a joke, and was sitting here wondering how using a different type of database would solve this kind of problem.
  - saghm 4y ago
    Similar to the sibling comments, I genuinely didn't realize you were joking either (although looking back now, it definitely seems more obvious!). Either way, I tend to be in the minority that doesn't mind jokes on Hacker News, so I'll give you an upvote as well

mjcl 4y ago

Please, yes. At a previous job, a department used a Windows accounting app that had a machine-level ODBC connection with the database credentials saved in it. A user figured out you could use Microsoft Query to pull data from the ODBC connection into Excel. This worked well until she accidentally deleted 90% of the vendor records from the database while trying to filter some data. I couldn't even really be upset at the user, a bunch of dumb product design decisions had to align like swiss cheese for this to happen.