Question? Leave a message!

Web and Internet Technology

Web and Internet Technology
COM1004 Web and Internet Technology  When a user submits a web form, how do we save the information to a database  How do we retrieve that data later ID NAME EMAIL MESSAGE TIMESTAMP 1 Mike mikedcs Hi there 20120303 10:31:04 2 Steve stevedcs Gotcha 20120303 10:33:07 3 Mike mikedcs Great it works 20120304 08:17:35  Design the form  Identify the information you want the user to provide  Create input tags accordingly  Here we want the user to provide Name, Email and Message data  Design a MySQL table to hold the data  Draw a picture of the required database table(s)  There should be a column for each input tag in the form that contain the relevant data  You may want to store extra data that isn't in the form (e.g. a timestamp and message ID) ID NAME EMAIL MESSAGE TIMESTAMP 1 Mike mikedcs Hi there 20120303 10:31:04 2 Steve stevedcs Gotcha 20120303 10:33:07 3 Mike mikedcs Great it works 20120304 08:17:35  Does the table exist  Don't assume it exists just because you created it earlier it may have been deleted in the mean time…  If necessary, create the table CREATE TABLE IF NOT EXISTS mytable (col1, col2, …); Retrieve data Submit data • If the user provided  If the user provided data, check it makes data, check it makes sense sense • Create a SELECT  Create an INSERT query to get data query to add data to from the table the table • Submit the query to  Submit the query to MySQL MySQL • Check for success  Check for success The data you  Your request has requested is not been processed; you available. Please try will hear from us again later. within 7 days Here is the data you  Sorry, we couldn't requested. handle your request. Please try again later. We're told what the form should look like • Data • Name • Email • Message • Required activities • Submit data • Retrieve data Singleline text inputs  Data input name='name' …  Name input name='email' …  Email Multiline text input  Message textarea name='msg' /textarea  Activities Two buttons  Submit data input type='submit'  Retrieve data name='submit' input type='submit' name='retrieve' Please enter your name, email address, and message. form action ="mysql01.php" method="get" table cellpadding="4" border="1" trtd align="right"Name/td tdinput name="name" type="text" maxlength="31" //td/tr trtd align="right"Email/td tdinput name="email" type="text" maxlength="63" //td/tr trtd align="right"Message/td tdtextarea name="msg" cols="30" rows="10"/textarea/td/tr trtd align="center" colspan="2" input type="submit" value=" Submit " name="submit" / input type="submit" value=" Retrieve " name="retrieve" / /td/tr /table/form  We need to store  Name, Email, Message  We will add extra information:  Message ID (primary key), timestamp id name email msg tstamp 1 Mike mikedcs Hi there 20120303 10:31:04 2 Steve stevedcs Gotcha 20120303 10:33:07 3 Mike mikedcs Great it works 20120304 08:17:35 mytable (id, name, email, msg, tstamp) id name email msg tstamp 1 Mike mikedcs Hi there 20120303 10:31:04 2 Steve stevedcs Gotcha 20120303 10:33:07 3 Mike mikedcs Great it works 20120304 08:17:35 Specify the column types. I'll use the following: • id INT UNSIGNED NOT NULL AUTOINCREMENT PRIMARY KEY • name VARCHAR(16) • email VARCHAR(32) • msg VARCHAR(100) • tstamp DATETIME  CHAR(n)  Exactly n characters, requires n bytes storage  VARCHAR(n)  Up to n characters, (length+(1 or 2)) bytes  TEXT  Up to 65535 characters, storage varies  MEDIUMTEXT  Up to 16,777,215 characters  LONGTEXT  Up to 4,294,967,295 characters  ENUM('square', 'circle', 'triangle')  A fixed list of allowed strings  DATETIME (8 bytes)  10000101 00:00:00 to 99991231 23:59:59  DATE (3 bytes)  10000101 to 99991231  TIME (3 bytes)  838:59:59 to 838:59:59  YEAR(2), YEAR(4) (1 byte)  4digit: 1901 2155  2digit: 1970 2069 (specified as 0099)  TIMESTAMP (4 bytes)  19700101 00:00:01 to 20380119 03:14:07  Before we can create the table, we need to connect to the MySQL server on the DCS network  To do so, we need to provide sensitive information (our passwords)  How can we make sure noone can download this information  Technical solutions: htaccess, htpasswd  Another solution: input the data from another file php / "info.php" defines the values of myDbase my database myPass my password myName my username myHost my hostname / info = "/mysecretpath/info.php"; include info; info.php is NOT under publichtml, so can't be accessed online. It is still visible to the script, since it's on the server. php myHost = ""; myDbase = "mydatabase"; myPass = "mypassword"; myUser = "myusername"; Some people use a ".inc" name for these files, but this isn't always safe for sensitive data. Using ".php" should ensure the server executes the code depends on server configuration. // Connect to the SQL server mysqlpconnect(myHost,myUser,myPass) or die("Unable to connect to SQL serverbr /\n"); • If the connect command works, it returns TRUE • The or part is executed if the first bit is FALSE • The die command tells PHP to abandon the script and print the error message • The message will be displayed on a web page, so we need to remember the br / tag // Select the database mysqlselectdb(myDbase) or die("Unable to select databasebr /\n"); • The server handles lots of databases. We have to say which one we want. • As before, abandon processing if things go wrong. • We ought to provide a more useful error message (this one is just for illustration). • We'll use the // Define the table name table name table = "sql01"; repeatedly, so put it in a variable (or a constant) • The column types will correspond to those in our table design // Create the table if it doesn't yet exist query = "CREATE TABLE IF NOT EXISTS table (" . "id INT UNSIGNED NOT NULL AUTOINCREMENT PRIMARY KEY, " • mysqlerror tells us the . "name VARCHAR(16), " last error encountered . "email VARCHAR(32), " • MySQL queries need . "msg VARCHAR(100), " semicolons, just like . "tstamp DATETIME" PHP. . ");"; mysqlquery(query) or die ("Failed to create table: " . mysqlerror() . "br /\n") ; We have two basic activities (submit retrieve), one for each button in the form if (user clicked submit) submit the data to the table; else if (user clicked retrieve) retrieve the relevant data; else do some default action; Clicking: input name="myname" … Calls: target.phpmyname … if (isset(REQUEST'submit')) submit the data to the table; else if (isset(REQUEST'retrieve')) retrieve the relevant data; else do some default action; name = REQUEST'name'; email = REQUEST'email'; msg = REQUEST'msg'; query = "INSERT INTO " . "table(name,email,msg,tstamp) " . "VALUES('name','email','msg',NOW());"; mysqlquery(query) or die("Unable to insert new data: " . mysqlerror() . "br /\n"); To add data, use an INSERT query. To submit a query, use the mysqlquery function. It returns FALSE if it fails. INSERT INTO sql01(name,email,msg,tstamp) VALUES('mike', 'mikedcs', 'hello', NOW()); "Insert the following values into the columns called name, email, msg and tstamp, in the table called sql01. The values to insert are: name = 'mike' email = 'mikedcs' msg = 'hello' tstamp = the current date/time" Why don't we insert an ID value  Later, we'll be searching for all messages associated with a given email address, so make sure the user specified  An email address  A message  Can do this using basic JavaScript (and then follow this up using PHP regular expressions)  Each form element can access the values of other elements in the form.  We can use an event handler (onClick) to invoke JavaScript code when the button is clicked.  Returning false abandons form submission input type="submit" value="Submit" name="submit" onClick="if ('') alert('Please provide an email address.'); return false; else if (form.msg.value=='') alert('Please provide a message.'); return false; else return true;" / If onClick returns false the form won't be submitted. Other event handlers are similar. form onSubmit="return validate(this);" action="target.php" input type="text" name="email" / input type="submit" value="Click me" name="submit" / /form If validate returns false, the form won't be submitted. Basic validation can be done using a JavaScript function, e.g. script type="text/javascript" function validate(f) if ( == 0) alert("Please give email address"); return false; else return true; /script reqEmail = REQUEST'email'; echo "Retrieving data for: " . "ireqEmail/ibr /\n"; query = "SELECT FROM table " . "WHERE email='reqEmail';"; result = mysqlquery(query) or die(" /\n");  If SELECT fails, the query returns FALSE  If it works, it returns a PHP resource  The resource may contains several records  We can extract them one at a time using mysqlfetcharray query = "SELECT FROM table;"; result = mysqlquery(query); row = mysqlfetcharray(result); if (row) initialise stuff; while(row) do something; row = mysqlfetcharray(result); tidy up; else no rows were returned; id = row'id' row'id' : 'nbsp;'; One of the columns is called 'id', so this is the key we use to extract its value. Because I'll be placing values in an HTML table, I convert empty results into nonbreaking spaces. Otherwise the cell borders may not be displayed properly in some browsers. if (row) open HTML table; while (row) add row data to table; get next row; close table; else tell user there's nothing to display; mysqlclose(); • Be sure to close your connection to the database when you no longer need it, especially during development • Servers can only handle a limited number of connections at once • If 100 students each have 10 old connections open, that's 1000 connections wasted query = "SELECT FROM sql01 " . "WHERE email='reqEmail';"; If reqEmail is the string maths'R'us, this becomes query = "SELECT FROM sql01 " . "WHERE email='maths'R'us ';"; This is valid PHP, but the query contains a syntax error so MySQL will complain.  Malicious code injection. Consider this: “SELECT FROM users WHERE name = '" + userName + "';”  If the user says their name is me’; DROP TABLE users; SELECT FROM private WHERE ‘0' = ‘0  This becomes SELECT FROM users WHERE name = ‘me’; DROP TABLE users; SELECT FROM private WHERE ‘0' = ‘0’; This deletes the users table and lists the entire contents of the private table • Next week we'll be  This week we've looking at something looked at how a bit different PHP/MySQL can be • Data encryption used to • Decryption  Respond to a form • Cracking codes  Submit data to a database • Later we'll look at e  Retrieve data from a commerce database
Document Information
User Name:
User Type:
United States
Uploaded Date: