Overview of Web database applications with PHP

1. Overview of Web database applications with PHP CS308-6083 Polytechnic University

2. 3 tier architecture • Client runs browser, which sends HTTP requests, receives HTTP responses, and renders the HTML document from the response • Web server (e.g. Apache) calls PHP script that requested url points to and incorporates output into the response • Script is html mixed with executable code fragments • Optionally, script connects to DBMS and uses query results to produce its output • Example: • Code • Execution Execution CS308-6083 Polytechnic University

3. Basic Language Features • Variables • Denoted by $identifier • No static type rules – error prone! • The usual control flow constructs • Functions • Call by value default • Call by reference denoted with & • Lots of string and regular expression functions to facilitate string matching and manipulation • PHP 5: Object oriented; PEAR library CS308-6083 Polytechnic University

4. Associative Arrays • Map key to value • Array slots can also be accessed by position • $price = array (“milk”=>3.99, “bread”=>4.85, “coffee”=>6.99); • Print $price[“milk”]; print $price[0]; • $price[“beer”] = 7.99 // updates or adds element • Heterogeneous • Can be single dimensional or multi-dimensional CS308-6083 Polytechnic University

5. Other useful array features • Explode, implode functions for converting between arrays and strings • Sorting, searching functions • Array_key_exists • Example code • Example execution: Execution CS308-6083 Polytechnic University

6. Executing SQL from PHP • Connect to server • mysql_connect • Select the database • mysql_select_db • Run query • Retrieve row of results • mysql_fetch_array • Retrieve attributes • foreach CS308-6083 Polytechnic University

7. Query Execution Example • Code • Execution: Execution CS308-6083 Polytechnic University

8. Dynamic Query construction • Query details may depend on user inputs from • Parameters to http get or post • Cookies • Session variables • Example code • Example url: Execution CS308-6083 Polytechnic University

9. Passing data from client to server • HTML Form environment • Textual input (beware of injection attacks) • Radio buttons • Menus • Buttons • Specifies • Action: script to be executed with the data as input • method: http GET or POST to pass data to server • Example code • Example execution: Execution CS308-6083 Polytechnic University

10. Selecting Multiple Items • HTML <select multiple> tag allows user to select multiple items from a list • They have the same name in the URL • In order to pass all of them, rather than clobbering all but the last, make the name an array, e.g <select multiple name=“choice[]”> • Example code and execution for pull-down menu. • Example code and execution for target page. CS308-6083 Polytechnic University

11. Passing data from client to server • Other techniques: • Embedded links that can be clicked • Typing urls (inconvenient and less common) CS308-6083 Polytechnic University

12. Multi-file applications • Can require or include other files • Included files can have .inc extension, but beware of putting sensitive information in .inc files unless they’re on inaccessible paths or web-server is configured to not allow them to be downloaded. • Safer to put sensitive info in .php files which will be executed, rather than returned as text. CS308-6083 Polytechnic University

13. Sessions • Manage interaction between browser and server, to give stateful structure to the application, in spite of HTTP statelessness. • Session variables: • State info created and accessed by application • Session ID • Identifier passed between server and browser (usually as cookie) • Used to identify a file on the server, in which session variables and their values are stored (or to find them in a DB) • Eventually session times out and file is removed CS308-6083 Polytechnic University

14. session_start() function • First call generates session ID and creates empty associative array $_SESSION • Application may create and store session variables in $_SESSION • Example: $_SESSION[userName]=$_GET[name]; • Session ID is passed to browser with HTTP response and stored there, and session variables are stored in file • Subsequent calls to session_start() (usually by other scripts in the application) cause $_SESSION to be reinitialized with the values stored on the server CS308-6083 Polytechnic University

15. Typical Application • Login page: • Collect credentials and pass them to setup page via POST • Setup page: • Check credentials • Initialize session and session variables • Redirect to welcome page • Application pages • Call session_start(), authenticate the session, and use/update session variables, as needed • Logout page • Calls session_destroy() • Redirects to “goodbye” page CS308-6083 Polytechnic University

16. Checking User Credentials • Username and cryptographic hash (message digest) of password stored in DB • Retrieve data from HTTP $_POST • Sanitize username, and password digest, and query DB to check that password matches • If OK set session variables with username (and IP address for more safety) and other relevant stuff about user CS308-6083 Polytechnic University

17. Example • Scripts (from Williams, Lane book & website). They use templates, but you should be able to understand the main points: http://www.webdatabasebook.com/2nd-edition/examples/index.html, Chapter 11. • Login page • Logincheck • Authenticate User, Authenticate Session • Logout CS308-6083 Polytechnic University

18. Some Security Issues • Detailed treatment is beyond the scope of this class, but you should be aware that issues exist. • HTTP sends data in the clear. For real applications that handle sensitive data, should use HTTPS • authenticate server • encrypt data sent over network via SSL • Session hijacking • Adversary who discovers session ID can take over a session • Checking IP address of each request helps mitigate this threat, but doesn’t eliminate it CS308-6083 Polytechnic University

19. Security Issues, continued • SQL injection • Malicious user enters input that results in execution of an SQL statement other than the intended one, e.g. • Select * from T where name=‘joe’ or ‘1’=‘1’; Instead of • Select * from T where name=‘joe’; • Cross-site scripting • Malicious user gives input that hides script in content that others will download • Application code should check that input is of the expected form and or “clean” the data, e.g. with mysql_clean CS308-6083 Polytechnic University

20. References • Williams and Lane, Web Database Applications with PHP and MySQL, 2nd Ed, O’Reilly http://www.oreilly.com/catalog/webdbapps2/ • W-L book’s code: http://www.webdatabasebook.com/ • On-line tutorial: http://www.w3schools.com/php/default.asp • Article on security: http://www.sitepoint.com/article/php-security-blunders CS308-6083 Polytechnic University