I have a Python client program (which will be available to a limited number of users) that fetches data from a remote MySQL-DB using the pymysql-Module.
The problem is that the login data for the DB is visible for everyone who takes a look at the code, so everyone could manipulate or delete data in the DB. Even if I would store the login data in an encrypted file, some still could edit the code and insert their own MySql queries (and again manipulate or delete data).
So how can I access the DB from my program and still SELECT, DELETE or UPDATE data in it, but make sure that no one can execute his own (evil) SQL Code (except the ones that are triggered by using the GUI)?
You can prevent injections just by parameterising arguments, for example:
"SELECT * FROM Users WHERE name=\"".name."\";"
Will read
SELECT * FROM Users WHERE name="AlecTeal";
But an be "injected" with:
name="\" or UserType=\"Admin"
Then it will read
SELECT * FROM Users WHERE name="" or UserType="Admin";
That's bad, you can prevent that with stuff like:
SELECT * FROM Users WHERE name=?
and binding your variables, then the SQL server doesn't actually parse any data from the user, it sees the ? and just reads from the parameters.
You cannot hide the SQL statements yourself
You can obscure them sure, but they'll be in your .pyo or .py code somewhere!
You have two options:
MySQL supports users - and this is what users are for and assume "Only fairly trustworthy people will get my program" so you can have a user like Debbie_From_Accounts" who can select from theUserstable, and update/delete/insert/select fromFinancial` Tables say.
OR!
You can use some sort of API on the server and have like a set of PHP scripts that do the DB work and you just http get the pages.