Sanitizing data to avoid SQL injection attack

http://xkcd.com/327/:

A solution: adding backslashes to quote symbols

Quotation symbols and escape symbols (such as \) depend on the database settings so you have to use mysql_real_escape_string function that gets this information directly from the database.


<!DOCTYPE html
PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<!--
Preparing data for database submission
Author: Elena Machkasova
Last Modifed: 4/14/2009
-->
<?php
$name = $_POST["name"];
$text = $_POST["text"];
$submit = $_POST["submit"]; // to check if the user got here through a form

function showerror()
{
        die("Error ". mysql_errno(). " : " .mysql_error());
}


function mysqlclean($data, $maxlength, $connection) {
	// In a real application you should check for length 
	// in a separate function and display a meaningful error message:
        $data = substr($data, 0, $maxlength); // chop off extra characters

        // automatically inserting escapes where needed
	// Note: this is database-dependent, that's why 
	// $connection variable is needed
        $data = mysql_real_escape_string($data, $connection);
        return $data;
}

function display_form($style1 = "", $style2 = "", $name_value="", 
		      $text_value = "") {
                print "<form  method=\"post\"
                action=\"filter_data.php\">
                <table style=\"border: none\">
                <tr>
                <td $style1>Enter your name:</td>
                <td>
                <input type = \"text\" name = \"name\" $name_value />
                </td>
                </tr>
                <tr>
                <td $style2>Your opinion:</td>
                <td>
                <textarea name=\"text\" rows=\"5\" cols=\"30\" $text_value></textarea>
                </td>
                </tr>
                <tr>
                <td>
                <input type=\"submit\" name = \"submit\" value=\"submit\" />
                </td>
                </tr>
                </table>
                </form>";

}
?>
<html  xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
<title>
Displaying errors
</title>
</head>
<body>
<?php

$style1 = "";
$style2 = "";
$error_style = "style = \"color: red\"";
$message = "";
$name_value = "";
$text_value = "";
$errors = false;

if (!isset($submit)) {
  display_form(); // display the form
}
else {
	// start validation
        // usually some sort of regular expression: 
        // if (!eregi("^[a-z ]+$", $name))
        // For simplicity we only check for empty values
       if (strcmp($name, "") == 0) {
		$style1 = $error_style;
		$message = $message."The name is empty<br/>";
		$errors = true;
	} else {
		$name_value = "value = \"$name\"";	
	}
       // Also usually regular expression:
       // if (!eregi("^([a-z0-9]+)@([a-z._]+)$", $email))
       // For simplicity just checking for an empty field 
	if (strcmp($text, "") == 0) {
		$style2 = $error_style;
		$message = $message."The text is empty<br/>";
		$errors = true;
	} else {
		$text_value = "value = \"$text\"";	
	}

	// handling the results
	if (!$errors) {
	  // connect to the database
	  // note: the actual password was given to you in class 
	  $password = "";

	  // connect to the server
	  if (! ($connection = 
		 @mysql_connect("localhost","1101readwrite",$password)))
	    die ("connection to the database failed");

	  // select a database
	  if (!@mysql_select_db("1101spr09", $connection)) showerror();
	  
	  // 100 is the max length of a post. In real life should be longer
	  $text = mysqlclean($text,100,$connection);
	  print "This is safe to add to the database: <p>$text</p>\n";
	  // code to store the data to the database goes here: 
	
	} else {
		// priniting the form
	  display_form($style1, $style2, $name_value, $text_value);
	  // printing the error message
	  print "<p $error_style>$message You need to resubmit the form</p>";
	}
}
?>
</body>
</html>
http://csci1101sp09.morris.umn.edu/~elenam/1101_spring09/filter/filter_data.php

Some already defined php filters


<!DOCTYPE html
PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<!--
Preparing data for database submission
Author: Elena Machkasova
Last Modifed: 4/14/2009
-->
<html  xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
<title>
Examples of filtering and sanitizing data
</title>
</head>
<body>
<h2>Use of <code>filter_var</code> function for data filtering</h2>
<p>
<h3>Validating e-mail</h3>
<?php
$email = "hi there!";

if (filter_var($email,FILTER_VALIDATE_EMAIL)) {
  print "$email is a valid e-mail<br /> \n";
} else {
  print "$email is not a valid e-mail<br /> \n";
}

$email = "someone@somewhere.com";

if (filter_var($email,FILTER_VALIDATE_EMAIL)) {
  print "$email is a valid e-mail<br /> \n";
} else {
  print "$email is not a valid e-mail<br /> \n";
}
?>
</p>
<h3>Validating URL</h3>
<?php
$url = "http://us.php.net/manual/en/filter.filters.valiate.php";

if (filter_var($url,FILTER_VALIDATE_URL)) {
  print "$url is a valid url<br /> \n";
} else {
  print "$url is not a valid url<br /> \n";
}

$url = "http://csci1101sp09.morris.umn.edu/~elenam/1101_spring09/first/input.ph\
p?input1=5&input2=3";

if (filter_var($url,FILTER_VALIDATE_URL)) {
  print "$url is a valid url<br /> \n";
} else {
  print "$url is not a valid url<br /> \n";
}

$url = "http://www.why.not";

if (filter_var($url,FILTER_VALIDATE_URL)) {
  print "$url is a valid url<br /> \n";
} else {
  print "$url is not a valid url<br /> \n";
}

$url = "www.whoknows.net";

if (filter_var($url,FILTER_VALIDATE_URL)) {
  print "$url is a valid url<br /> \n";
} else {
  print "$url is not a valid url<br /> \n";
}
?>

<h3>Removing all tags from text</h3>
<?php

$string = "<strong>Hi there!</strong><br />Welcome to my page\n";

print "$string<br />\n";

$result = filter_var($string, FILTER_SANITIZE_STRING);

print "$result<br />\n";

?>

</body>
</html>
http://csci1101sp09.morris.umn.edu/~elenam/1101_spring09/filter/filter_var.php

UMM CSci 1101

The views and opinions expressed in this page are strictly those of the page author. The contents of this page have not been reviewed or approved by the University of Minnesota.