1

I have a Mysql database where one table is meant to store user complaints/comments/suggestions. I am using Mysqli to connect to the database with a 

    $con=mysqli_connect("myhost","myuser","mypassword","mydb");

At this time, the "myuser" and "mypassword" are the same as the id and password I use as the admin to log into the phpmyadmin backend. Is this the correct way to do this? Does this give out more power to the user than I want to? Does it imply that the users will be able to run DELETE and ALter statements on the table/database?

My concern is that since the id and password are the admin's, users actually sending the feedback could also use it to do something that could harm the database. Is it necessary to use the admin id and password in this code?

My shared hosting does not allow me to create additional users - at least that is what I have understood from my chat with their customer care (they say I need to take a dedicated server plan to be able to add more users. I don't have the budget to take on the extra cost of the dedicated virtual server). There is no Users table or User Privileges table in the installation. Does this mean that I cannot create my own tables to manage users and assign privileges? Would any such tables that I create work exactly like those that come with the installation? Is there a restriction to table names - i.e. that any such tables that i might like to create cannot have certain names?

Improve this question
1
  • Yeah, this is super-crappy, but fairly typical of shared hosting and probably not the most insecure thing about the setup; it's common for the boundaries between different customers to not be watertight. If you're doing a serious webapp you will generally have to go the virtual-server route sooner or later.
    – bobince
    Commented Jul 13, 2013 at 11:40

2 Answers 2

Reset to default
2

It is not the safest way to do this. A safer way, as you state, would be to have a unprivileged user.

It is unlikely that you can do so by hand; any "User" table that you created would probably be just that, a table, without any effect on actual database users. I guess that your "admin" user is actually a normal user with full privileges on his own database, that can't, say, issue GRANT ALL PRIVILEGES ON yourdb.* TO newuser@host IDENTIFIED BY 'password'; without getting a ERROR 1044 (42000): Access denied for user...,

As it is, you need to implement security at the application level instead of at the database level. However, the MySQLi interface is quite solid (barring shooting yourself in the foot) and it isn't too difficult to prevent possible SQL injections.

Barring that, the users will have admin privileges, but will be unable to use them.

Improve this answer
4
  • Thanks @Iserni ... My knowledge in all this is very rudimentary - so could you elaborate a little on what you meant by "barring shooting yourself in the foot"? And when you say "implement security at the application level", what should I do other than using mysqli? So far, I have got to parameterized queries - but that is all! What else should I do?
    – vinaya
    Commented Jul 13, 2013 at 9:03
  • Not much more than that. Unless you build some "imprudent" queries where unchecked user content may find its way into the final MySQL statement (e.g. you parameterize the table the query is going to act upon; this sort of things), which is what I called shooting yourself in the foot, and as long as you validate the logic of the application and ensure that access rules are enforced in PHP (the application level), you'll do fine.
    – LSerni
    Commented Jul 13, 2013 at 11:15
  • Wow! I think I WAS about to shoot myself in the foot! I parameterized all the inputs but I had no idea that we can parameterize the table! How do we do that? Can you give me an example? (forgive me if it sounds dumb that I can parameterize the query but not the table - green, green green....)
    – vinaya
    Commented Jul 13, 2013 at 11:43
  • You could for example assemble the query (I'm making this up) as UPDATE $table SET ... where $table is Preorder, Processing or Shipped - which in some contexts might be better than a single table Orders with a status column. This would work, except that if you retrieved $table from user input and did not validate it properly, one could send, instead of Processing, something like USERS SET password='xyzzy' WHERE user='admin' -- causing another kind of SQL injection that might bypass any further validation. You'd have to work to achieve this; hence shooting in the foot.
    – LSerni
    Commented Jul 13, 2013 at 13:51
1

if you (or your hoster) cannot create an aditional user that has select-right only for the whole db and write-right only to given tables, then

Is this the correct way to do this?

this is the only way for you.

the correct way would be:

GRANT ALL  ON yourdb.* TO adminuser@host IDENTIFIED BY 'password';
GRANT SELECT  ON yourdb.* TO webuser@host IDENTIFIED BY 'password2';
GRANT INSERT  ON yourdb.complaints TO webuser@host IDENTIFIED BY 'password2';
...

see: GRANT Syntax

Improve this answer

You must log in to answer this question.

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