1

I am building a project that requires users to be authenticated to access it. I was planning on using MySQL as a database and running PHP scripts on the client to fetch user data. E.g. SELECT * from uaccounts WHERE username = 'bob'. And then storing the rest of the info if the user authenticates in a session. Is it secure to execute SQL queries on a client or should I look into something like Firebase or build an API or look at another external API for storing and querying user data (and other SQL queries). Is running SQL on the client open to interception by attackers or is it secure as long as it is done properly?

5
  • not sure how you run php on the client, but the user can see everything stored. if that's a problem, it's a problem, and if it's not, it's not. all chrome browsers have sql built-in, so the idea itself is not abhorrent .
    – dandavis
    Nov 17, 2017 at 20:09
  • 2
    How are you running SQL queries on the client? Nov 17, 2017 at 21:16
  • @NeilSmithline not sure if I worded that correctly. I mean to run queries like $conn = "" and then execute that. Nov 19, 2017 at 14:28
  • That looks like PHP. PHP executes on the server, not client. Nov 19, 2017 at 19:27
  • You need to use prepare statements, and other techniques to avoid SQL injection attacks. Also mysql_real_escape_string($user) and data validation and etc.
    – cybernard
    Nov 20, 2017 at 14:16

2 Answers 2

1

SELECT * from uaccounts WHERE username = 'bob

Assuming Bob is only supposed to see the record for Bob then no it is not secure. Bob can pull out the connection parameters from the application and run whatever query he feels like.

Database security is in every implementation I've seen done at the table level. I.e even if you give Bob his own user account for the database he's still going to need select permission on the uaccounts table meaning he can run "SELECT * FROM uaccounts".

The only way to make this secure is to instead of giving Bob select permission on the table create parameterless stored procedures (which Bob would need execute permissions for) that use the logged in user name. And this is usually far more effort than its worth and far too easy to screw up. Not to mention it doesn't really scale. Facebook would not want a separate database username for every user of the site...

1
  • Please could you further explain what you mean by 'pull out connection parameters'. Do you mean the user will be able to change the query from mysqli_query($conn, $query); Nov 19, 2017 at 14:30
0

Without getting into a complcated discussion of how mysql authentication and data exchange works, it is secure as long as its done properly. And doing it properly means using an ssl connection. MySql supports this out of the box

However security can also mean limiting the amount of damage clients can do to yhe server. You can do this on a raw mysql connection by denying access to the tables for the user account and permitting operations by stored procedure with privilege seperation. Alternatively use https as the transport with a simple rest server in front of the dbms.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .