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\u2019t do this on all of my pages so from time to time they find a page that does\u201dt fail with bad input and send thousands of page requests to it.<\/p>\n
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\u2019s my current initialization script.
\n
\n<?php\n\n$user = 'mysql_user';\n$pass = 'supersecurepasswors';\n$host = 'localhost';\n$db_name = 'website_database';\n\ntry {\n $dbWG = new PDO("mysql:host=$host;db_name=$dbname", $user, $pass, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8") );\n $dbWG->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );\n $dbWG->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);\n $dbWG->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);\n}\ncatch(PDOException $e) {\n $e->getMessage();<\/code><\/pre><\/p>\nThe 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. <\/p>\n
So first I sanitize the input. Here\u2019s a simple one where the input has to be a digit:
\n
\n$number_of_letters = (isset($_GET['n']) ? $_GET['n'] : 1);\nif ( !is_numeric($number_of_letters) ) {\n include_once('dieInAFire.inc');\n}<\/code><\/pre><\/p>\nI 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\u2019t accidentally blocked. I wrote this include file to use after sanitizing inputs fails.
\n
\n<?php\n$url = isset($_SERVER['REQUEST_URI']) ? $_SERVER['REQUEST_URI'] : '';\n$ref = isset($_SERVER['HTTP_REFERER']) ? $_SERVER['HTTP_REFERER'] : '';\n$ip = isset($_SERVER['REMOTE_ADDR']) ? $_SERVER['REMOTE_ADDR'] : '';\n\nif ($showError == TRUE) {\n error_log("Long string in $calledFileName: URL is $url and IP is $ip & ref is $ref");\n}\nheader("HTTP\/1.0 404 Not Found");\ndie();\n?><\/code><\/pre><\/p>\nNote that this has to be called before the
statement.<\/p>\nIf it is a legitimate request, I make then use a prepared statement to execute it.
\n
\n$qry.= "HAVING COUNT(sorted_letters) > :number_of_letters ";\n\n$stmt = $dbWG->prepare($qry);\n$stmt->bindParam(':number_of_letters', $number_of_letters);\n$stmt->execute();<\/code><\/pre><\/p>\n","protected":false},"excerpt":{"rendered":"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\u2019t do this on all of my pages … Continue reading More Thoughts on SQL Injection Attacks<\/span><\/a><\/p>\n","protected":false},"author":6,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27],"tags":[],"class_list":["post-2459","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts\/2459","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2459"}],"version-history":[{"count":0,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts\/2459\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2459"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2459"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2459"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}