- Input sanitization and validation are pivotal in reducing SQL injections - Sanitization: - The [mysqli_real_escape_string()](https://www.php.net/manual/en/mysqli.real-escape-string.php) function can be used to escape characters such as single and double quotes - Validation: - The [preg_match()](https://www.php.net/manual/en/function.preg-match.php) function can be used to check if the input matches a given patter - For example, if the pattern is `[A-Za-z\s]+`, only strings containing letters and spaces will match - Enforce least privilege - Superusers and users with administrative privileges should never be used with web applications - Use a web application firewall (WAF) - WAFs 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) - For example, any request containing the string `INFORMATION_SCHEMA` would be rejected, as it's commonly used while exploiting SQL injection - Parameterized queries - Instead of directly passing user input data into the SQL query, parameterized queries use placeholders and then fill them with PHP functions - The below php code contains two placeholders, marked with `?` where the username and password will be placed - Username and password are bound to the query using the [mysqli_stmt_bind_param()](https://www.php.net/manual/en/mysqli-stmt.bind-param.php) function, which safely escape any quotes and place the values in the query ```php <SNIP> $username = $_POST['username']; $password = $_POST['password']; $query = "SELECT * FROM logins WHERE username=? AND password = ?" ; $stmt = mysqli_prepare($conn, $query); mysqli_stmt_bind_param($stmt, 'ss', $username, $password); mysqli_stmt_execute($stmt); $result = mysqli_stmt_get_result($stmt); $row = mysqli_fetch_array($result); mysqli_stmt_close($stmt); <SNIP> ```