1 / 34

ECA 236

ECA 236. Open Source Server Side Scripting PHP & MySQL. show source. show_source( ) or highlight_file( ) takes one parameter, the path to a .php filename prints a colored, highlighted version of the code in the browser CAUTION: do not reveal sensitive information. <?php

Download Presentation

ECA 236

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. ECA 236 Open Source Server Side Scripting PHP & MySQL Open Source Server Side Scripting

  2. show source • show_source( ) or highlight_file( ) • takes one parameter, the path to a .php filename • prints a colored, highlighted version of the code in the browser • CAUTION: do not reveal sensitive information <?php highlight_file( name_of_file ); ?> Open Source Server Side Scripting

  3. web database architecture • steps when a user accesses webpage/database • browser sends HTTP request to server • web server passes PHP code to PHP engine • PHP engine parses the script • PHP engine finds command to open database connection • PHP opens connection to MySQL server ( local ) • MySQL receives query, checks users and privileges, processes query, returns results • PHP engine finishes parsing script • web server passes HTML to browser Open Source Server Side Scripting

  4. PHP & MySQL • basic steps to query a database from the web • check and filter data entered by user • connect to appropriate database • query the database • retrieve the results • present the results back to the user • we will use the sitename database Open Source Server Side Scripting

  5. connect to MySQL server • mysql_connect( ) • connects to server • prototype • optional arguments • host • username • password $reference = mysql_connect( ‘host’, ‘user’, ‘password’ ); Open Source Server Side Scripting

  6. connect to MySQL server cont … • mysql_connect( ) • host is usually “localhost” • connection to MySQL from local server • user will have only privileges granted in mysql database • if a connection is made, a link identifier is returned, with which we can reference the open connection $dbc = mysql_connect( ‘localhost’, ‘Web_User’, ‘my1230’ ); Open Source Server Side Scripting

  7. specify database • once a connection has been established, you must identify a particular database to use • similar to using the use keyword in the mysql monitor • mysql_select_db( ) • Syntax mysql_select_db( ‘database_name’, link_identifier ); mysql_select_db( ‘sitename’, $dbc ); Open Source Server Side Scripting

  8. security • set the host, username, password, and database name to variables or CONSTANTS, save in a separate file, include this file in the script • save with a .php extension • to include or require file define( ‘DB_USER’, ‘Web_User’ ); define( ‘DB_PW’, ‘my1230’ ); define( ‘DB_HOST’, ‘localhost’ ); define( ‘DB_NAME’, ‘sitename’ ); require( ‘db_params.php’ ); Open Source Server Side Scripting

  9. security cont … • connect to MySQL with the following • test the connection from the server ( Xitami ) • if it works a blank page will load • otherwise errors will display • the same values we used in the mysql monitor should work in the PHP scripts $dbc = mysql_connect( DB_HOST, DB_USER, DB_PW ); mysql_select_db( DB_NAME ); Open Source Server Side Scripting

  10. error handling • even more important when connecting to a database • probability for errors increases • Common errors • failure to connect to the database server • failure to select a database • inability to run a query • no results returned Open Source Server Side Scripting

  11. error handling cont … • PHP functions to handle MySQL errors • mysql_errno( ) • returns the error number • mysql_error( ) • returns the textual version of the error • to handle errors gracefully • @ sign to suppress error messages • die( ) function $dbc = @mysql_connect( DB_HOST, DB_USER, DB_PW ) or die( 'Could not connect to MySQL: Error number ' . mysql_errno( ) . ': ' . mysql_error( ) ); Open Source Server Side Scripting

  12. simple query • after connection to the server, and selection of a database, we can now execute queries • Web_User has the following privileges • SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX, FILE • mysql_query( ) • function for executing queries • one parameter: the query Open Source Server Side Scripting

  13. simple query cont … • DO NOT place a semicolon inside your query • INSERT, UPDATE, DELETE • $result will be either TRUE or FALSE • SELECT • $result will contain the results of the query if successful • $result will be FALSE if query was unsuccessful $q = 'SELECT first_name, last_name AS n FROM users ORDER BY n'; $result = mysql_query( $q ); Open Source Server Side Scripting

  14. close connection • mysql_close( ) • one parameter, the link identifier • this function is not required, but it is good programming to do so mysql_close( $dbc ); Open Source Server Side Scripting

  15. retrieving results • mysql_fetch_array( ) • primary function for handling the rows returned from a SELECT query • returns each row as an indexed or associative array • two parameters • result of the query, $result in this example • CONSTANT identifying what kind of array to return Open Source Server Side Scripting

  16. retrieving results cont … • mysql_fetch_array( ) • CONSTANTs Open Source Server Side Scripting

  17. retrieving results cont … • mysql_fetch_array( ) • returns one row of data at a time as an array • use within a loop that will run as long as rows are returned while( $row = mysql_fetch_array( $result, MYSQL_ASSOC) ) // do something } Open Source Server Side Scripting

  18. retrieving results cont … • mysql_fetch_array( ) • return the first name, last name, and email addresses of all users in sitename • alphabetize by last name $q = 'SELECT * FROM users ORDER BY last_name'; $result = mysql_query( $q ); while( $row = mysql_fetch_array( $result, MYSQL_ASSOC ) ){echo $row['first_name'] . " " . $row['last_name'] . ": " . $row['email'] . "<br />";} Open Source Server Side Scripting

  19. retrieving results cont … • mysql_fetch_row( ) • equivalent to mysql_fetch_array( $result, MYSQL_NUM ). • mysql_fetch_assoc( ) • equivalent to mysql_fetch_array( $result, MYSQL_ASSOC) • when using associative arrays, the keys are case sensitive Open Source Server Side Scripting

  20. validate user input • if we have a form asking the user to input the following • first name • last name • email address • username • password • confirm password Open Source Server Side Scripting

  21. validate user input cont … • earlier we had used the isset( ) function to check that a form element was not empty • we can do something similar with empty( ) • empty( ) • returns true if the variable • is zero • is empty • is NULL Open Source Server Side Scripting

  22. validate user input cont … • empty( ) • we can use similar code to check that other variables are not empty if (empty($_POST['last_name'])) { $ln = FALSE;} else { $ln = $_POST['last_name'];} Open Source Server Side Scripting

  23. validate user input cont … • if all values test TRUE we can use an if statement • then add the user to the database if( $fn && $ln && $e && $u && $pw ){ $query = "INSERT INTO users (username, first_name, last_name, email, password, registration_date) VALUES ('$u', '$fn', '$ln', '$e', PASSWORD('$p'), NOW( ) )"; $result = @mysql_query ($query); Open Source Server Side Scripting

  24. validate user input cont … • let the user know that the data has been added if ($result) { echo '<p><b>You have been registered!</b></p>';} else { $message = '<p>You could not be registered due to a system error. We apologize for any inconvenience.</p><p>' . mysql_error( ) . '</p>'; } Open Source Server Side Scripting

  25. security • review of ways to validate user input • user superglobals to retrieve user input • use regular expressions to validate user input • trim( ) user input • use the function strip_tags( ) to remove HTML and PHP tags • use the function mysql_real_escape_string( ) to escape potentially troublesome characters Open Source Server Side Scripting

  26. mysql_real_escape_string( ) • mysql_real_escape_string( ) • automatically escapes special character, such as single and double quotes, for use in a SQL statement • for example • a user enters data with an apostrophe, such as the last name O’Malley • without escaping the apostrophe, using O’Malley in a SQL statement will throw an error Open Source Server Side Scripting

  27. mysql_real_escape_string( ) cont … • rather than pulling the value from a for usingescape any potentially troublesome characters • returns the value as $ln = $_POST['last_name']; $ln = mysql_real_escape_string( $_POST['last_name'] ); O\’Malley Open Source Server Side Scripting

  28. stripslashes( ) • if necessary, remove the escaping backslashes with another function, stripslashes( )echoes • Magic Quotes • when enabled, automatically escapes single and double quotes $str = “Is your name Shaun O\’Malley?”;echo stripslashes( $str ); Is your name Shaun O’Malley? Open Source Server Side Scripting

  29. mysql_num_rows( ) • mysql_num_rows( ) • returns the number of rows retrieved by a SELECT query • takes one parameter, the result set of the SELECT query $q = 'SELECT last_name FROM users ORDER BY last_name';$result = mysql_query( $q );echo $n = mysql_num_rows( $result ); Open Source Server Side Scripting

  30. mysql_num_rows( ) cont … • a simple test to see if a username already exists $q = “SELECT user_id FROM users WHERE username = ‘$u’ ”;$result = mysql_query( $q ); if( mysql_num_rows( $result ) = = 0 ) {// insert the data} else {echo “That username is already taken.”;} Open Source Server Side Scripting

  31. mysql_affected_rows( ) • mysql_affected_rows( ) • returns the number of rows affected by INSERT, UPDATE, or DELETE query • takes one OPTIONAL parameter, the result set of the query • if no parameter is specified, uses previous query $query = "INSERT INTO users (username, first_name, last_name, email, password, registration_date) VALUES ('$u', '$fn', '$ln', '$e', PASSWORD('$p'), NOW( ) )"; $result = @mysql_query ($query); echo “Records inserted: “ . mysql_affected_rows( ); Open Source Server Side Scripting

  32. UPDATE • to allow a user to change her password • the first query returns the user_id if the username and password match data stored in the users table • to compare the user’s submitted password, re-encrypt it, then compare with the stored value • if the username and password match, exactly one record is returned • assign this record to the $row variable Open Source Server Side Scripting

  33. UPDATE cont … • check for username / password match, return record $query = "SELECT user_id FROM users WHERE (username='$u' AND password=PASSWORD('$p') )"; $result = @mysql_query ($query); $num = mysql_num_rows ($result); if ($num == 1) { $row = mysql_fetch_array($result, MYSQL_NUM); Open Source Server Side Scripting

  34. UPDATE cont … • if the username and password match, update the database with a new query • verify the results of the query $query = "UPDATE users SET password=PASSWORD('$np') WHERE user_id=$row[0]"; $result = @mysql_query ($query); // Run the query. if (mysql_affected_rows( ) == 1) { echo '<p><b>Your password has been changed.</b></p>'; } } // end outer if Open Source Server Side Scripting

More Related