The importance of database security cannot be overestimated. The planning of a database’s security may seem trite, but one must think of all the possible scenarios in which a potential hacker could access a database’s contents. There are many ways that unauthorized users can access content of a database. And it behooves the developer/programmer to implement strategies to create robust database access. That is, anything entered in a form should not be able to stop the PHP from executing or cause an unintended SQL query to take place. This means first checking for valid user input. The following illustration indicates a scenario that could possibly happen if the code did not check for valid user input.
Some things to avoid when designing a database:
• Avoid using common names for tables especially with open-source template databases.
• Avoid allowing error messages to display database-specific information to the user. Instead, send the data to a secured file perhaps one level above the root level on the production host where only an authorized user could access.
• Verify that the input data has the expected data type.
• Consider using stored procedures and previously defined cursors to abstract data access so that users do not directly access tables or views.
• Never connect to the database as a superuser or as the database owner to simply access data. Instead, always use a customized user login with very limited privileges.
The following example illustrates a secure way to compose a query for paging. The settype function will force the contents of the $offset variable to integer even if it is a string. For instance, if the value is “5bar,” its new value will 5. And if the value of $offset is a Boolean “true,” then the new value will be “1” as a string. This can add protection in that a user could not accidentally (or intentionally) put in a statement that could compromise database with a faulty $offset value.