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">
<!--
Displaying error messages for forms
Author: Elena Machkasova
Last Modifed: 4/6/2010
-->
<?php
require("../../../1101_includes/wordpress.inc");

$text = $_POST["text"];
$submit = $_POST["submit"]; // to check if the user got here through a form

function display_form() {
                print "<form  method=\"post\"
                action=\"filter_data.php\">
                <table style=\"border: none\">
                <tr>
                <td>Enter some text:</td>
                <td>
                <input type = \"text\" name = \"text\" />
                </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>
Preparing data for the database
</title>
</head>
<body>
<?php
if (!isset($submit)) {
  display_form(); // display the form
}
 else {
   // connect to the server using login name and password given in
   // database.inc file
   if (! ($connection = @mysql_connect("localhost",$user,$passwd))) {
     die ("connection to the database failed");
   }

   // select a database
   if (!@mysql_select_db("wordpress", $connection)) showerror();

   $text = mysql_real_escape_string($text, $connection);

   print "This is safe to add to the database: <p>$text</p>\n";
   // code to store the data to the database goes here:
 }
?>

</body>
</html>
http://csci1101sp10.morris.umn.edu/~elenam/1101_spring10/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://csci1101sp10.morris.umn.edu/~elenam/1101_spring10/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://csci1101sp10.morris.umn.edu/~elenam/1101_spring10/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.