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; |
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 |
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");
} |
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)));
} |
/* 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");
} |
/* 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";
} |
// 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> |
<?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.
