There are various different methods that you can employ to prevent SQL injection attacks on your database system. You should deal with all of the components involved – the server and individual applications as well as the database management system.
Step 1: Monitor the automatic entries of the application
Test and filter the methods and parameters that the connected applications use when entering the database. The transferred data should always be in the expected data type. If a numeric parameter is required you can check it using a PHP script with the is_numeric() function. When filtering, it’s necessary to ignore corresponding special characters. Another important point is to make sure that the applications don’t output any external error messages that reveal information about the system or the structures of the database.
Meanwhile, so-called prepared statements are popular practices as they can be used with many database management systems. These pre-defined statements were originally used to perform more frequent queries but because of their structure they also reduce the risk of SQL injection. Because the parameter statements transfer the actual SQL command from the parameters separately to the database. It’s only the database management system itself that links them together and automatically masks the crucial special characters.
Step 2: Provide comprehensive server protection
The security of the server on which you run your database management system also obviously plays a large role in SQL injection prevention. The first step is the solidification of the operating system according to the established pattern:
- Install or enable only those applications and services that are relevant to the operation of the database.
- Delete all user accounts that aren’t needed.
- Make sure that all relevant system and program updates are installed.
The higher the demands of your web project’s security, the more likely you are to consider the use of intrusion detection (IDS) systems or intrusion prevention (IPS) systems. These work with various different detection systems to detect attacks on the server at an early stage, issue warnings, and, in the case of IPS, initiate corresponding countermeasures automatically. An application layer gateway, which monitors the traffic between applications and the web browser directly at the application level, can also be an effective safeguard.
Step 3: Solidify the database and utilize secure codes
Like your operating system, the database should be cleared of all irrelevant factors and updated on a regular basis. To do this, remove all the stored procedures that you don’t need and disable all unnecessary services and user accounts. Set up a special database account, designed to be accessed from the web, which has minimal access rights. Store all sensitive data such as passwords in an encrypted form in your database.
When it comes to prepared statements, it’s strongly recommended to not use the PHP module mysql and instead choose mysqli or PDO. In this way, you can also protect yourself with secure codes. For example, the mysqli_real_escape_string() function in PHP scripts prevents special characters from being passed to the SQL database in the original form and instead masks them. If you, for instance, extend the following codes lines