As part of my site rewrite and migration to MySQL PDO I am making sure that all of the input is sanitized before using\u2014which has the side effect of stopping injection attempts, as discussed in the previous post\u2014and either using prepared statements or whitelisted inputs.<\/p>\n
Here\u2019s the sanitizing portion of a crossword solver page. The input is the number of letters in the word and up to 14 letters. There should only be one letter in each space, the space can be empty, and the numbers can be from 1 to 14. I haven\u2019t had any attacks on my forms yet, so I\u2019ll assume any invalid input is due to fat fingers and make reasonable changes.
\n
\n<?php\n$MAX_LETTERS = 14;\n$letters = array();\n\n\/\/ Read in the letters and number of letters first so you can repopulate the fields.\n\/\/ If it\u2019s not a single letter in the letter field, or a valid number in the number field,\n\/\/ don\u2019t let it into the query.\n\/\/ Log it in case we get lots of injection attempts.\nif(isset($_POST)) {\n $submitType = $_POST['submitType'];\n\n if ($submitType != 'Clear') {\n \/\/ Get and validate letters\n for($i = 1; $i <= $MAX_LETTERS; $i++) {\n $letters[$i] = $_POST['letter' . $i];\n $letters[$i] = str_replace(" ","",$letters[$i]);\n\n if (!preg_match("\/^[a-zA-Z]$\/",$letters[$i]) && ($letters[$i] <> '') ) {\n if ($showError) error_log("Not a letter {$letters[$i]} in $calledFileName");\n $letters[$i] = "";\n }\n }\n \/\/ Get and validate the number of letters\n $num_letters = (integer)$_POST['num_letters'];\n if (!is_integer($num_letters) ) {\n if ($showError) error_log("Not a number in $calledFileName");\n }\n $num_letters = (integer)$num_letters;\n if ( $num_letters > $MAX_LETTERS ) {\n $num_letters = $MAX_LETTERS;\n if ($showError) error_log("Too many numbers in $calledFileName");\n } else if ($num_letters < 0 ) {\n $num_letters = $num_letters * -1;\n if ($showError) error_log("Negative number in $calledFileName");\n }\n }\n}<\/code><\/pre><\/p>\nNote that I don\u2019t use htmlspecialchars or mysql_real_escape_string when getting input because I explicitly allow only letters or numbers when validating the output. I don\u2019t think that they would hurt anything, but they aren\u2019t necessary.
\n
\n$letters[$i] = htmlspecialchars($_POST['letter' . $i]);\n$letters[$i] = mysql_real_escape_string($_POST['letter' . $i]);<\/code><\/pre><\/p>\nSome of my pages allow more than one letter in each input space. I just add .* to the pre_match to allow more than one letter. I also allow a wildcard, *, in the web page so I need to escape it in the pre_match.
\n
\nif (!preg_match("\/[a-zA-Z\\*].*\/",$letters[$i]) && ($letters[$i] <> '') ) {<\/code><\/pre><\/p>\nThe safest way to sanitize input is to whitelist the query. There are lots of ways to do this. One way is to construct the query based on the input.
\n
\nswitch ($loc) {\n case "I":\n case "i":\n $location = "Initial";\n $searchString = "^{$letters}[A-Z ]*";\n break;\n case "M":\n case "m":\n $location = "Medial";\n $searchString = "[A-Z ]+{$letters}[A-Z ]+";\n break;\n case "F":\n case "f":\n $location = "Final";\n $searchString = "[A-Z ]*{$letters}\\$";\n break;\n.....<\/code><\/pre>
\nThe user provides a location\u2014initial, medial, or final\u2014and I construct the search string based on their input. No injection is possible because the user input is not seen by the search query.<\/p>\nAnother example of whitelisting is to only allow certain values. It works if the list is small and not changed often.
\n
\nif ($input == 'first value' || $input == 'second value' || $input == 'third value') {\n -- do stuff with the database\n} else {\n include_once('dieInAFire');\n}<\/code><\/pre><\/p>\nYou can do something similar by checking whether the input is part of a hard-coded array. The problem with the last two approaches is that they only work well if the list of acceptable values doesn\u2019t change often. They can work to sanitize user input for things like states and provinces, occupation, and taxable status.<\/p>\n","protected":false},"excerpt":{"rendered":"
As part of my site rewrite and migration to MySQL PDO I am making sure that all of the input is sanitized before using\u2014which has the side effect of stopping injection attempts, as discussed in the previous post\u2014and either using prepared statements or whitelisted inputs. Here\u2019s the sanitizing portion of a crossword solver page. The … Continue reading Sanitizing Database Query Input<\/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-2463","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts\/2463","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=2463"}],"version-history":[{"count":0,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts\/2463\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2463"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2463"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2463"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}