Web Technology Solutions

1. Web Technology Solutions • Class: Documentation and Web Data Integration & Application Security Date : 1/28/2013

2. Tonight • Introductory SQL in MySQLDatabase Connectivity in PHP PHP MySQL: CRUD (p1)Security in PHP and MySQL Lab

3. Lab Preview • Build a DB Driven Shakespeare Insult Kit • Use Functions • Use Array’s (with randomization) • Use your database connection • Build a basic Presidents MGMT system • Uses CRUD

4. Review SIK with Randomization

5. Arrays: Sorting

6. Array: Functions

7. MySQL Database

8. Entities \Tables

9. Databases • Entities • Relationships • Attributes

10. Entities An entity may be defined as a thing which is recognized as being capable of an independent existence and which can be uniquely identified. Text

11. Relationships A relationship captures how two or more entities are related to one another. Text

12. Attributes Entities and relationships can both have attributes. Examples: an employee entity might have a Social Security Number (SSN) attribute; the proved relationship may have a date attribute. Text

13. Tables in this course • This course will contain basic tables to build an app. • More advanced database structures and design will be created by you in the next course.

14. MySQL in this Course • We’re focused on producing queries via PHP. • Database design, ER and complex queries will be handled in later course. • What are queries? Data Manipulation Procedures that allow you alter data from a Relational Database. • KEY SQL Queries for CRUD • INSERT (Create) • SELECT (Read) • UPDATE (Update) • DELETE (Delete)

15. Intro to SQL • SQL = Structured Query Language • Originally developed by IBM in the 70’s (a few iterations of spec since) • Designed for usages in RDBMS (relational database management systems) e.g. MySQL • Different DB’s have different implementations of SQL. e.g. MSSQL has T\SQL (transact SQL)

16. MySQL Table • A Table in SQL is a container that stores specific information about an entity.

17. What is an entity? • Customers, Addresses, Posts, Orders, Questions, etc. Detailed information and meta-data that describes these

18. Creating Tables • A table contains: Columns, Fields, and attributes. • PHP Developers don’t need to know *all* the details of a table (abstraction in action). What is important right now? Understand field names and keys.

19. Simple Table Example Fields Columns Attributes

20. MySQL Tables (Entity Relationship Diagram)

21. Table Definition • CREATE TABLE `presidents` ( • `id` int(11) UNSIGNED AUTO_INCREMENT NOT NULL default '0', • `first_name` varchar(256) default NULL, • `last_name` varchar(256) default NULL, • `start_date` varchar(4) default NULL, • `end_date` varchar(4) default NULL, • `party` varchar(90) default NULL, • PRIMARY KEY (`id`) • ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

22. Primary Key’s • A unique identifier for the table. • All tables should have a PK • All PK’s should auto_increment (increases the value when performing an insert) • A PK allows you to identify row to perform updates, deletes, or selects. • What is a PK: an integer that is greater than 0. • What isn’t a PK: an email address, a name, a social security number.

23. Table Definition • CREATE TABLE `presidents` ( • `id` int(11) UNSIGNED AUTO_INCREMENT NOT NULL default '0', • `first_name` varchar(256) default NULL, • `last_name` varchar(256) default NULL, • `start_date` varchar(4) default NULL, • `end_date` varchar(4) default NULL, • `party` varchar(90) default NULL, • PRIMARY KEY (`id`) • ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

24. Insert Statements • Insert places data into a new row of the table. • Syntax: • INSERT INTO table (columns) VALUES (values); • An Example Please?

25. Insert Statements Examples • Syntax: • INSERT INTO presidents VALUES (1,”Ronald”,”Reagan”,”1980”,”1988”); • INSERT INTO presidents (id, first_name,start_date) VALUES (1,”Ronald”,”1980”); • Usual error: supplying too little, too many values for the fields available.

26. Delete Statements • Use with care! Tip: hide or don’t display instead of delete. • Syntax: • DELETE FROM presidents; • DELETE FROM presidents WHERE id = 1; • WHERE id’s the row in the table you’d like to remove.

27. Where Clause • WHERE clause allows you to specify you’re query. • For example: =, IN, NOT IN, AND • WHERE id=1 • WHERE first_name = “Ronald” • WHERE id IN (1,2,3); • WHERE first_name = “Ronald” AND last_name=”Regan”;

28. Update Statement • UPDATE will update the specific fields you’ve identified to be updated. • UPDATE syntax: • UPDATE presidents SET first_name = “James” WHERE id = 1; • Ensure you have a where clause or it will operate on the entire table.

29. Select Statement • SELECT will return complex data back to your application. • Basic Syntax: • SELECT last_name FROM presidents; • SELECT first_name,last_name,start_date,end_date FROM presidents;

30. Select Statement • SELECT * FROM presidents; • returns all the data from a table. • Wise NOT to return all data from a table. • SQL will slow if attempting to return all, doesn’t scale. • Tip: always declare your return fields.

31. Select Statement • SELECT with WHERE clause to refine selection. • Basic Syntax: • SELECT first_name,last_name FROM presidents WHERE id = 3; • SELECT first_name,last_name FROM presidents WHERE id < 3; • SELECT first_name,last_name FROM presidents WHERE id IN (3,4,5);

32. Select Statement • SELECT with ORDER BY clause to sort selection. • Basic Syntax: • SELECT first_name,last_name FROM presidents WHERE last_name = “Smith” ORDER BY id DESC; • SELECT first_name,last_name FROM presidents WHERE last_name = “Smith” ORDER BY last_name,first_name,id ASC;

33. Select Statement • SELECT with LIKE clause to get nearby selection. • Basic Syntax: • SELECT first_name,last_name FROM presidents WHERE last_name LIKE “%Smit%”; • will return Smith, Smithers, Smithsonian, AND ALSO Aerosmith. • Not highly optimized. Might return more than expected. Used in basic searches. • SELECT first_name FROM presidents WHERE last_name LIKE “Smit%”;

34. Connecting to DB with PHP • Basic Syntax: • mysql_connect() • basic everyday small app • mysql_pconnect() • persistent connection. good for highly active apps. • mysqli_connect() • improved for MySQL 4.1 and up - also good for OOP PHP5.

35. MySQL Connection Errors • You can suppress errors in your connection to the DB if you add a “@” to your call. Example: • @mysql_connect()

36. DB Connecting • Define connection variable outside of www folder using constants. • Import your connection script in application_top.php (or akin) • Ensure that your DB connects (or die’s)

37. DB Connecting • Show Sample DB Connection Script.

38. Common PHP MySQL Functions • mysql_affected_rows() — Get number of affected rows in previous MySQL operation • mysql_fetch_array() — Fetch a result row as an associative array, a numeric array, or both • mysql_insert_id() — Get the ID generated from the previous INSERT operation • mysql_num_rows() — Get number of rows in result

39. PHP Security • Good programmers think of security last. Great ones think of it all the time. PHP is very popular and therefor is a primary target for hackers.

40. “A completely secure system is a virtual impossibility”

41. PHP Top 10 • safe_mode() • addslashes() / magic_quotes • Unvalidated Parameters • Broke Access Control • XSS • Error Handling • Poor Cryptography • buffer overflows • sql injection • includes with vars

42. PHP Safe Mode • forces PHP to test for UID permission before opening files. You can relax this to GID by enabling safe_mode_gid, but this usually implies access as if safe mode was disabled) • prevents system() and other calls from working (unless safe_mode_exec_dir is set) • restrictions on setting most environment variables (but not reading them) • open_basedir allows hosters to force file access to stay within a virtual directory, but otherwise it is not set

43. Add Slashes • Circa 2002 recommended the use of addslashes(). In general, this is poor advice today, particularly when PHP is coupled with MySQL. • To prevent SQL injections, it is essential that: • magic_quotes_gpc is disabled in all PHP installations • addslashes() should be deprecated - it does not protect against SQL injections • Use only mysql_real_escape_string()

44. Invalidated Params • Most importantly, turn off register_globals. This configuration setting defaults to off in PHP 4.2.0 and later. Access values from URLs, forms, and cookies through the superglobal arrays $_GET, $_POST, and $_COOKIE. • $result= mysql_query('SELECT * FROM presidents WHERE first_name="'.$_GET['first_name'].'"');

45. Broken Access Control • Don’t Roll Your Own Auth. • Instead of rolling your own access control solution use proven open source Auth Systems. • PEAR Auth does cookie-based authentication for you and Auth_HTTP does browser-based authentication.

46. Cross Site Scripting (XSS) • Never display any information coming from outside your program without filtering it first. Filter variables before including them in hidden form fields, in query strings, or just plain page output. • PHP gives you plenty of tools to filter untrusted data: • htmlspecialchars() turns & > " < into their HTML-entity equivalents and can also convert single quotes by passing ENT_QUOTES as a second argument. • strtr() filters any characters you'd like. Pass strtr() an array of characters and their replacements. To change ( and ) into their entity equivalents, which is recommended to prevent XSS attacks, do: $safer = strtr($untrusted, array('(' => '&#40;', ')' => '&#41;')); • strip_tags() removes HTML and PHP tags from a string.

47. Error Handling • If users (and attackers) can see the raw error messages returned from PHP, your database, or external programs, they can make educated guesses about how your system is organized and what software you use. These educated guesses make it easier for attackers to break into your system. Error messages shouldn't contain any descriptive system information. Tell PHP to put error messages in your server's error log instead of displaying them to a user with these configuration directives: • log_errors = On • display_errors = Off

48. Poor Cryptography • Never Roll your own cryptography. • use mcrypt for file or variable encryption. • use SHA1 with a salt for database values (1 way).

49. Buffer Overflows • PHP has been exploited because of its C background. • serialize/unserialize • pack/unpack • jpg algorithm • exif header field overflows

50. SQL Injection • Hackers use a piece of valid SQL to bypass your insecure code and get access to the database. • SELECT * FROM presidents WHERE first_name = " OR 1=1--" • http://shiflett.org/articles/sql-injection