Disclaimer that this article talks about the best practices that you can use to decrease the likelihood of preventing SQL/MySQL injection when writing code in PHP. This, however, does not guarantee that you will never be victim to MySQL injection. You will always need to be cautious when coding and test appropriately.

1. Us PHP Data Objects (PDO Extension)

PDO is an extension for PHP that provides a common interface for accessing databases. It was introduced in PHP 5.1 and is now the recommended way to interact with databases in PHP. PDO provides a consistent, object-oriented interface for working with databases, regardless of the underlying database system (e.g., MySQL, PostgreSQL, SQLite, etc.).

In summary it can:

  1. Support for multiple database systems: PDO supports a wide range of databases, including MySQL, PostgreSQL, SQLite, MS SQL Server, Oracle, and others.
  2. Consistent interface: PDO provides a consistent interface for performing common database operations, such as executing queries, fetching data, and managing transactions.
  3. Prepared statements: PDO supports prepared statements, which allow you to execute the same statement multiple times with different parameters. Prepared statements can help prevent SQL injection attacks.
  4. Error handling: PDO provides an exception-based error handling mechanism that makes it easier to handle and debug errors in your database code.
  5. Transactions: PDO supports transactions, allowing you to execute a series of database operations as a single, atomic transaction.
  6. Easy data binding: PDO supports data binding, which allows you to bind values to placeholders in your SQL statements. This makes it easy to write secure and efficient database code.

By using PDO, you can write database-agnostic code that works with multiple database systems, and you can take advantage of its advanced features, such as prepared statements and transactions, to write secure and efficient database code. If you are developing a new PHP application, you should consider using PDO to interact with your database. If you are upgrading an existing application, you may want to consider migrating your database code to PDO to take advantage of its improved features and security.

2. Mysqli extension

Mysqli is an improved version of the MySQL extension for PHP. It provides an improved, object-oriented interface for accessing and manipulating databases in PHP. Mysqli stands for MySQL Improved.

In short it can provide you with the following.

  1. Support for both procedural and object-oriented programming styles.
  2. Support for prepared statements, which allow you to execute the same statement multiple times with different parameters. Prepared statements can help prevent SQL injection attacks.
  3. Improved security features, including support for SSL-encrypted connections and improved error handling.
  4. Improved performance, as mysqli is optimized for modern hardware and software environments.
  5. Support for transactions, allowing you to execute a series of database operations as a single, atomic transaction.
  6. Support for multiple statements, allowing you to execute multiple SQL statements with a single call to mysqli.

Mysqli is the recommended database extension for PHP, as it provides a number of improvements over the older MySQL extension. If you are developing a new PHP application, you should use Mysqli instead of the older MySQL extension. If you are upgrading an existing application, you should consider migrating from MySQL to Mysqli to take advantage of its improved features and security.

3. Limit Privileges on the database side

Limit the privileges of the database user to only the minimum required to perform its intended tasks. This way, even if an attacker is able to inject malicious code into an SQL statement, they will be limited in the actions they can take on the database.

4. Always update

Keep your PHP and database software up-to-date with the latest security patches. New vulnerabilities are often discovered and fixed in software updates, so keeping your software up-to-date is an important part of securing your application.

You should also keep the habit of subscribing to new changelogs and security newsletters. They will usually announce when there is a new vulnerability.

5. Enable error logs

If you have not done that yet you should make sure that you have error logs available. Logs take a significant space but you might think about enabling them one in a while to do an audit and check if everything is running as expected.

6. Use a web application firewall

A WAF can be used to monitor and block malicious requests, including those that attempt to exploit SQL injection vulnerabilities. You could take a popular one from Cloudflare for example. Or if cost is too high then you can also go with an open source WAF like ModSecurity.

There are other obvious ways like validating user input but this should be common sense by now. Let me know if you know any other means to secure against SQL Injection or actually any other database vulnerability in the comments!