I am working on a mobile APP that requires query & send data from/to a PostgreSQL database(running in a RHEL Docker).
My requirements is:
Unity APP can send commands to the PostgreSQL database to query or update data.
Everything except the APP itself should be running in docker.
So my first question is: Is it possible (in Unity code) to create connection directly to the PostgreSQL database and then send CRUD command?
I did some search and reading others code, it seems a “middleware” (like node.js) is often used to connect front end to a database. If such kind of “middleware”/server/API is necessary -
I already have a websocket class in my Unity code, is it sufficient to send CRUD command to the “middleware”? or I have to create another webrequest class to send http requests?
I saw a lot of codes required to implement and dockerize a node.js server. Is there any alternatives other than Node.js which is easier to do the same job?
I am really new to this area so I am looking for some advises about how to achieve this.
Thanks in advance!
This would be a major security risk! Never, ever execute SQL transactions from a publicly accessible client directly. Instead, you will need to send a (WebRequest) command that the remote server interprets, validates, and then sanitizes the input to protect against SQL injection attacks, and then creates a SQL transaction and returns the result.
If you allow the client to directly connect to a SQL database, anyone can hack the client to perform any action on your SQL database. It’s not just the SQL that’s available to them but also the credentials. So any permissions that the credentials included in the client can be used by a user to perform actions on your SQL server - and they can do so without your app! They just need to extract the server IP and the credentials (not even considered a challenge for hackers).
I hope that “often” in reality means “always”. But I’m afraid this isn’t the case.
In simple terms, here’s what you need to do:
Client: send webrequest ie “read or write player data” to Server
Server: has the SQL user credentials, processes webrequests (your API), verifies requests, sanitizes user input, creates the SQL command, issues the transaction, returns the result in the response body
Whatever means you use to implement the Server-side doesn’t really matter as long as you follow best practices, respect and implement any security recommendations.
Hi CodeSmile,
Thanks for the reply. I understand the risk now.
Is there any recommendations for technology on how to implement this “Server-side”?
Thank you very much!
Your question to me is asking “How do I store data”, so I would suggest just using basic serialization on device or something like sqlite if you’re looking to use a DB.
But assuming you want to have a server store your data you would set up a API that controls the data that is sent to it. I would just as easily recommend a cloud based storage (bucket, queue, or some other tech), but regardless you need a API with an endpoint to send that data to to make sure you sanitize it so someone doesn’t do SQL injection and drop all your tables.
I’d have to go looking for one myself. Since there are many options I’d recommend to stick with something that you’re at least remotely familiar with or which has plenty of documentation/tutorials available and a large community for support.
