A few database security tips – things to do to effectively protect MySQL databases

I’d like to share with you some tips about hardening the database part of your application. Here are a few things you can do in protecting the databases from being compromised in security:

1. Create separate users with ONLY necessary privileges (as few as possible) to connect to the database for common daily tasks. Never use the database owner / creator or even MySQL root user in your PHP scripts to perform routine tasks.
2. Protect against SQL injection attacks by escaping ALL incoming input after ensuring data types with a variety of PHP variable type and character type validation functions.
3. The sprintf() function is both useful and secure in constructing SQL queries because of the built-in type checking. Better yet, use PDO.
4. Turn off error messages MySQL or PHP outputs when things go wrong so crackers know nothing about the technical details of your build such as database schema. As a matter of fact, a good rule of thumb in web application security is that the less people know about your application’s internal structure, the better.
5. For advanced SQL developers, extra abstraction layer in SQL in the form of stored procedures can benefit security because you implement yet another depth of defense and hide the schema of the database from the outside world.
6. For mission critical applications, it goes without saying that custom logging of database accesses can help a lot in identifying security risks.
7. If the database contains very sensitive data such as credit card information, you are strongly recommended to encrypt these tables or fields. Just use PHP cryptography extensions such as Mcrypt to encrypt any data that are to be stored and decrypt them when they are being retrieved.