Tag: web page

Web page inputs and insert into database – Part 2

As from here, where I outlined the javascript, html part of the exercised here is the php and mysql parts of the problem.

Here is the table that I created within MySQL

CREATE TABLE `User` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `firstName` VARCHAR(50) DEFAULT NULL,
  `lastName` VARCHAR(50) DEFAULT NULL,
  `email` VARCHAR(50) DEFAULT NULL,
  `phoneNum` VARCHAR(20) DEFAULT NULL,
  `guid` VARCHAR(36) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

As from my other post about MySQL triggers, so to find out more information about why please check out that page, and here is the trigger that I created for the above table.

CREATE TRIGGER User_insert 
BEFORE INSERT ON USER 
FOR EACH ROW 
BEGIN 
        SET NEW.guid = uuid(); 
END

The reason why I created this for was because within the exercise they asked “would forward planning, i.e. adding a unique code to the user details that could be used to validate their email address”, which by using a uuid, you can use that as a unique code.

so the only part left is the PHP that will connect to the database and also sanitize the inputs within sql insert. So created a class that has a constructor that will open a database connection to a database

      private $dbLink;
      /* connect to the database*/
      function __construct($host = "localhost", $user = "user", $password = "password", $database = "database")
      {
	$this->dbLink = mysql_connect($host, $user, $password);
	if (!$this->dbLink) die("no database present");
	if (!mysql_select_db($database, $this->dbLink))
	  die("no database within the database");
      }

Here I try to sanitize the insert string so that it will use the mysql_real_escape_string, this will use a php function that helps with SQL injections, also I am using the stripslashes and then trim the string to make sure that there is no white spaces (or any valid text left).

      /* santize the input for a mysql database */
      public function santizeInput($theStr)
      {
	return trim(stripslashes(mysql_real_escape_string($theStr)));
      }

and then to insert the data, just use the mysql_query and the sanitize function above to insert data into the database.

      /* insert the data into the database */
      public function insertData($sqlData)
      {
	mysql_query($this->santizeInput($sqlData), $this->dbLink) or die("Error inserting data");
      }

And here is where I get the data from the form post from the web page and then double sanitize the data and then create a database link, which in-turn use to insert the data.

    // could use foreach loop $_POST inputs, but I personally prefer to pick them up.
    $firstname = $_POST['firstname'];
    $secondname =$_POST['secondname'];
    $email = $_POST['email'];
    $phonenumber = $_POST['phonenumber'];
 
    /* could do additional checks on input incase it is sent via backend POST and not via the webpage,  could do with regular expression as well ? */
    $db = new databaseAccess("localhost", "username", "password", "database");
    /* can santize the inputs to make sure that there is some data to "play" with */
    $firstname = $db->santizeInput($firstname);
    $secondname =$db->santizeInput($secondname);
    $email =$db->santizeInput($email);
    $phonenumber = $db->santizeInput($phonenumber);
 
    if (checkLength($firstname) && checkLength($secondname))
    {	
	$sql = "insert into User (firstname, lastname, email, phoneNum) values (\"$firstname\",\"$secondname\",\"$email\", \"$phonenumber\")";
	$db->insertData($sql);
	echo "Data inserted";
    }

I did write within the exercise that since someone may try and post the data to the server within using the webpage (naughty people that they are!!) you could also check the inputs again for there data validity.

Here is the full code for the web page in total.

<?php
    class databaseAccess
    {
      private $dbLink;
      /* connect to the database*/
      function __construct($host = "localhost", $user = "user", $password = "password", $database = "database")
      {
	$this->dbLink = mysql_connect($host, $user, $password);
	if (!$this->dbLink) die("no database present");
	if (!mysql_select_db($database, $this->dbLink))
	  die("no database within the database");
      }
 
      /* disconnect */
      function __destruct()
      {
	if (!$this->dbLink) mysql_close($this->dbLink);
      }
 
      /* santize the input for a mysql database */
      public function santizeInput($theStr)
      {
	return trim(stripslashes(mysql_real_escape_string($theStr)));
      }
 
      /* insert the data into the database */
      public function insertData($sqlData)
      {
	mysql_query($this->santizeInput($sqlData), $this->dbLink) or die("Error inserting data");
      }
    }
 
    function checkLength($theStr)
    {
      if (strlen($theStr) > 0) 
	return true; 
      else 
	return false;
    }
 
    // could use foreach loop $_POST inputs, but I personally prefer to pick them up.
    $firstname = $_POST['firstname'];
    $secondname =$_POST['secondname'];
    $email = $_POST['email'];
    $phonenumber = $_POST['phonenumber'];
 
    /* could do additional checks on input incase it is sent via backend POST and not via the webpage, not sure if SOAP are looking for that as well ? 
      could do with regular expression as well ? */
    $db = new databaseAccess("localhost", "User", "PW", "Test");
    /* can santize the inputs to make sure that there is some data to "play" with */
    $firstname = $db->santizeInput($firstname);
    $secondname =$db->santizeInput($secondname);
    $email =$db->santizeInput($email);
    $phonenumber = $db->santizeInput($phonenumber);
 
    if (checkLength($firstname) && checkLength($secondname))
    {	
	$sql = "insert into User (firstname, lastname, email, phoneNum) values (\"$firstname\",\"$secondname\",\"$email\", \"$phonenumber\")";
	$db->insertData($sql);
	echo "Data inserted";
    }
 
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<script language="javascript">
  /* check the length of the element, focus is none present */
  function lengthCheck(elem, thename)
  {
    if (elem.value.length> 0) 
      return true;
    else
    {
      alert("Please insert the " + thename);
      elem.focus();
    }
  }
 
  /* check a email address, using regular expression */
  function emailChecker(elem)
  {
    var reg = /^[\w\-\.\+]+\@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4}$/;
    if (elem.value.match(reg))
      return true;
    else
    {
      alert ("Please insert a valid email address");
      elem.focus();
      return false;
    }
  }
 
  /* check against a phone number. a number being between 11-15 numbers*/
  function checkPhone(elem)
  {
    var reg = /^[0-9]{11,15}$/;
    if (elem.value.match(reg))
      return true;
    else
    {
      alert ("Please insert a valid phone number");
      elem.focus();
      return false;
    }
  }
 
  function checkInputs()
  {
    // obtain inputs
    var firstname = document.getElementsByName("firstname").item(0);
    var secondname = document.getElementsByName("secondname").item(0);
    var email= document.getElementsByName("email").item(0);
    var phonenum = document.getElementsByName("phonenumber").item(0);
    /* check the inputs */
    if (lengthCheck(firstname, "first name")) 
      if (lengthCheck(secondname, "second name"))
	if (emailChecker(email))
	  if (checkPhone(phonenum))
	    return true;
    return false;
  }
</script>
</head>
<body>
<form name="input" action="insertData.php" method="post" onSubmit="return checkInputs()">
First Name :
<input type="text" name="firstname"/>
 
Second Name : 
<input type="text" name="secondname"/>
 
Email : 
<input type="text" name="email"/>
 
Phone number : 
<input type="text" name="phonenumber"/>
 
<input type="submit" value="Submit"/>
</form>
</body>
</html>

If you save that as insertData.php then open up within your web-server. You will be able to insert data into a database with some javascript / php checks.

Web page inputs and insert into database

Also from the other posts, Join files together and simple calculator , I was also asked to do

“Accept form input for a user registration form, and store results in a MySQL table, using PHP. Applicant should demonstrate a knowledge of input validation, and using server-side code to avoid sql injection exploits. The user data should include first name, last name, email, an phone number.

Usage of Javascript pre-validation would be a plus, as would forward planning, i.e. adding a unique code to the user details that could be used to validate their email address. Suitable MySQL table schema should be demonstrated.”

To start with, I started at the data insert within a web page

<body>
<form name="input" action="insertData.php" method="post" onSubmit="return checkInputs()">
First Name :
<input type="text" name="firstname"/>
Second Name : 
<input type="text" name="secondname"/>
Email : 
<input type="text" name="email"/>
Phone number : 
<input type="text" name="phonenumber"/>
<input type="submit" value="Submit"/>
</form>
</body>

Which takes in the required input’s and also within the form HTML tag, but before sending to the back end PHP part of the exercise, there was a requirement to do some Javascript checking on the inputs.

So here is the javascript that will check the inputs, within the form onsubmit action I call this function checkInputs and the return value (true/false) is returned which gives the form either a action to post back to the server (true return) or to wait for the user correct there inputs (false return).

    var firstname = document.getElementsByName("firstname").item(0);

I get the data from the webpage, getElementsByName (which since it is a name there could be x amount of elements with that name, so I want the first one (.item(0))

  function checkInputs()
  {
    // obtain inputs
    var firstname = document.getElementsByName("firstname").item(0);
    var secondname = document.getElementsByName("secondname").item(0);
    var email= document.getElementsByName("email").item(0);
    var phonenum = document.getElementsByName("phonenumber").item(0);
    /* check the inputs */
    if (lengthCheck(firstname, "first name")) 
      if (lengthCheck(secondname, "second name"))
	if (emailChecker(email))
	  if (checkPhone(phonenum))
	    return true;
    return false;
  }

and then after getting the elements, I then call different additional functions that I did write within javascript to check the inputs gained. Here I check the length of a element passed within the one of the parameters within the function parameter list, with using objects you can access the objects value.length (javascript object of a element) and also use the focus function with the element which will focus the element on the webpage for the user to know where to check there input (also with a alert window to say why, e.g. “please insert some data”.)

  function lengthCheck(elem, thename)
  {
    if (elem.value.length> 0) 
      return true;
    else
    {
      alert("Please insert the " + thename);
      elem.focus();
    }
  }

Here is a way of using regular expression to check email inputs, basically it first checks to make sure there is a name before the “@” and also a at between 2 and 4 names with a “.” better them.

  /* check a email address, using regular expression */
  function emailChecker(elem)
  {
    var reg = /^[\w\-\.\+]+\@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4}$/;
    if (elem.value.match(reg))
      return true;
    else
    {
      alert ("Please insert a valid email address");
      elem.focus();
      return false;
    }
  }

Here is a similar way as above for checking a phone number input between 11-15 numbers

  /* check against a phone number. a number being between 11-15 numbers*/
  function checkPhone(elem)
  {
    var reg = /^[0-9]{11,15}$/;
    if (elem.value.match(reg))
      return true;
    else
    {
      alert ("Please insert a valid phone number");
      elem.focus();
      return false;
    }
  }

This is the web page part, and here is the second part where I insert the data into database with php.

But here is the full web page part of the first part.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<script language="javascript">
  /* check the length of the element, focus is none present */
  function lengthCheck(elem, thename)
  {
    if (elem.value.length> 0) 
      return true;
    else
    {
      alert("Please insert the " + thename);
      elem.focus();
    }
  }
 
  /* check a email address, using regular expression */
  function emailChecker(elem)
  {
    var reg = /^[\w\-\.\+]+\@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4}$/;
    if (elem.value.match(reg))
      return true;
    else
    {
      alert ("Please insert a valid email address");
      elem.focus();
      return false;
    }
  }
 
  /* check against a phone number. a number being between 11-15 numbers*/
  function checkPhone(elem)
  {
    var reg = /^[0-9]{11,15}$/;
    if (elem.value.match(reg))
      return true;
    else
    {
      alert ("Please insert a valid phone number");
      elem.focus();
      return false;
    }
  }
 
  function checkInputs()
  {
    // obtain inputs
    var firstname = document.getElementsByName("firstname").item(0);
    var secondname = document.getElementsByName("secondname").item(0);
    var email= document.getElementsByName("email").item(0);
    var phonenum = document.getElementsByName("phonenumber").item(0);
    /* check the inputs */
    if (lengthCheck(firstname, "first name")) 
      if (lengthCheck(secondname, "second name"))
	if (emailChecker(email))
	  if (checkPhone(phonenum))
	    return true;
    return false;
  }
</script>
</head>
<body>
<form name="input" action="insertData.php" method="post" onSubmit="return checkInputs()">
First Name :
<input type="text" name="firstname"/>
 
Second Name : 
<input type="text" name="secondname"/>
 
Email : 
<input type="text" name="email"/>
 
Phone number : 
<input type="text" name="phonenumber"/>
 
<input type="submit" value="Submit"/>
</form>
</body>
</html>