More Thoughts on SQL Injection Attacks

As I discussed in an earlier post, my sites are frequently attacked by malicious actors trying to get into my database by using SQL injection techniques. I stopped most of them by doing input validation and if the input fails validation, returning a 404 not found. I didn’t do this on all of my pages so from time to time they find a page that does”t fail with bad input and send thousands of page requests to it.

I recently updated my sites and took the opportunity to familiarize myself with the latest best practices for mitigating attacks. I added a line to my database initialization script, sanitize all inputs, and use either prepared statements or queries that use whitelisted terms. Here’s my current initialization script.

<?php

$user = 'mysql_user';
$pass = 'supersecurepasswors';
$host = 'localhost';
$db_name = 'website_database';

try {
    $dbWG = new PDO("mysql:host=$host;db_name=$dbname", $user, $pass, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8") );
    $dbWG->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
    $dbWG->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    $dbWG->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
}
catch(PDOException $e) {
    $e->getMessage();

The only way I found to get attacks to stop is to return a 404 not found message to the bot. After a few tries they usually give up. Otherwise they will try thousands of times.

So first I sanitize the input. Here’s a simple one where the input has to be a digit:

$number_of_letters = (isset($_GET['n']) ? $_GET['n'] : 1);
if ( !is_numeric($number_of_letters) ) {
    include_once('dieInAFire.inc');
}

I have a $showError variable in my header that lets me turn off and on information on attacks. I usually leave it on and peruse the error log to make sure legitimate requests aren’t accidentally blocked. I wrote this include file to use after sanitizing inputs fails.

<?php
$url = isset($_SERVER['REQUEST_URI'])  ? $_SERVER['REQUEST_URI']  : '';
$ref = isset($_SERVER['HTTP_REFERER']) ? $_SERVER['HTTP_REFERER'] : '';
$ip  = isset($_SERVER['REMOTE_ADDR'])  ? $_SERVER['REMOTE_ADDR']  : '';

if ($showError == TRUE) {
    error_log("Long string in $calledFileName: URL is $url and IP is $ip & ref is $ref");
}
header("HTTP/1.0 404 Not Found");
die();
?>

Note that this has to be called before the statement.

If it is a legitimate request, I make then use a prepared statement to execute it.

$qry.=  "HAVING COUNT(sorted_letters) > :number_of_letters ";

$stmt = $dbWG->prepare($qry);
$stmt->bindParam(':number_of_letters', $number_of_letters);
$stmt->execute();