Link to home
Start Free TrialLog in
Avatar of JanjaNovak
JanjaNovak

asked on

How to ensure the maximum level of data table security and its critical data using a database web user?

I wrote a PHP web application that is of course connected to MySQL database. Servers (Apache & PHP) are physically separated and protected by firewall. Between mentioned servers only communication through 3306 is possible while external users can access web application (server) only through internet using port 80 and 443. On the database server side, a schema ‘testprevozi’ has been created along with two data tables named ‘clan’ and ‘napacna_prijava’. There are also bunch of other data tables created here but are irrelevant for this discussion. Besides a general data stored in table ‘clan’ (name, username, address,…) the table contains very critical data of application users (username, password, application security settings…). And that table along with its general and critical data should remain hidden from anyone (except to a database user named ‘webupo’).

Mentioned PHP web application is of course using the table ‘user’ and it is one of the main data tables for application to run (user authentication and other procedures). Now, the question is how to ensure the maximum level of security of data table ‘user’ so there will be no theoretical and practical possibilies for unauthorized person through the internet using PHP web application to see or access the table data individually? The database user ‘webupo’ is only used for web application to communication with database and has the following rights (BTW: DELETE grant on `testprevozi`.`napacna_prijava’ is needed due to application requirement):

MySQL [testprevozi]> show grants for webupo;
+-------------------------------------------------------------------------------------------------------+
| Grants for webupo@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'webupo'@'%' IDENTIFIED BY PASSWORD '********' |
| GRANT SELECT, INSERT, UPDATE ON `testprevozi`.* TO 'webupo'@'%'                                       |
| GRANT DELETE ON `testprevozi`.`napacna_prijava` TO 'webupo'@'%'                                       |
+-------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)


On the web application level I secured the database against SQL injections on each input field using the following function:

function ocisti_vnos_predSQL($data) {
    $data = trim($data); //Strip whitespace (or other characters) from the beginning and end of a string
    $data = stripslashes($data); // Un-quotes a quoted string
    $data = htmlspecialchars($data); //Convert special characters to HTML entities
    $data = filter_var($data, FILTER_SANITIZE_STRING); //strips or encodes unwanted characters
    return $data;
}

In your opinion, is there anything else I could do to ensure the highest level of data protection when external user accesses the database through written PHP web application? Any suggestions & comments are much appreciated.
ASKER CERTIFIED SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial