Mitigating SQL Injection
Input Sanitization
Sanitiye this by using mysqli_real_escape_string
:
As expected, the injection no longer works due to escaping the single quotes. A similar example is the pg_escape_string() which used to escape PostgreSQL queries.
Input Validation
User input can also be validated based on the data used to query to ensure that it matches the expected input. For example, when taking an email as input, we can validate that the input is in the form of ...@email.com
, and so on.
Validate "port_code":
User Privileges
As discussed initially, DBMS software allows the creation of users with fine-grained permissions. We should ensure that the user querying the database only has minimum permissions.
The commands above add a new MariaDB user named reader
who is granted only SELECT
privileges on the ports
table.
Web Application Firewall
Web Application Firewalls (WAF) are used to detect malicious input and reject any HTTP requests containing them. This helps in preventing SQL Injection even when the application logic is flawed. WAFs can be open-source (ModSecurity) or premium (Cloudflare). Most of them have default rules configured based on common web attacks. For example, any request containing the string INFORMATION_SCHEMA
would be rejected, as it's commonly used while exploiting SQL injection.
Parameterized Queries
Another way to ensure that the input is safely sanitized is by using parameterized queries. Parameterized queries contain placeholders for the input data, which is then escaped and passed on by the drivers. Instead of directly passing the data into the SQL query, we use placeholders and then fill them with PHP functions.
Last updated