ECA 236

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