ctec2712_wad_week-5_lecture-5_accessing-databases_slides - Copy

## Page 1: Introduction to Web Application Development - **CTEC2712**: Course focused on Apache HTTP server, PHP, and MariaDB. - Overview of accessing databases with PHP in web applications. ## Page 2: Lecture Content Overview - **Topics Covered**: - What is MariaDB? - Working with PDO and MariaDB. - Linking to MariaDB and extracting information. - Understanding prepared statements. - Using a database wrapper class. - Awareness of SQL injection attacks. - Backing up and copying MariaDB databases. - Introduction to PCI-DSS. ## Page 3: Post Lecture Discussion Topics - Explain the benefits of prepared statements: - **Efficiency**: Reduces overhead of query preparation. - **Security**: Protects against SQL injection attacks. - Identify and explain 5 clauses in the PCI-DSS related to databases. ## Page 4: References & Reading Materials - Links to further reading on PDO and database security: - [PHP Delusions on PDO](https://phpdelusions.net/pdo) - [Security Focus](http://www.securityfocus.com/columnists/432) - [PHP Manual on PDO](http://php.net/manual/en/book.pdo.php) - [PCI Security Standards](https://www.pcisecuritystandards.org/security_standards/pci_dss_download.html) ## Page 5: MariaDB Overview - **MariaDB/MySQL**: Introduced as vital database technology. ## Page 6: What is MariaDB? - **Definition**: Open-source, multi-platform, ACID-compliant relational database. - **License**: Released under the GNU Public License (GPL); free even for commercial use (to an extent). - **Usage**: Commonly used as a backend for PHP-driven websites (WAMP stack) and in various applications. ## Page 7: Other Databases - **Compatible Databases**: PHP can connect with various databases: - Commercial: Oracle, SQLite. - Open Source: MySQL, PostgreSQL. - No-SQL Alternatives: MongoDB, CouchDB, Neo4j. ## Page 8: MariaDB Components - Key databases created on installation: - **information_schema**: Metadata on databases. - **performance_schema**: Server execution statistics. - **mysql**: User info, installed databases; user passwords encrypted. - **test**: For testing purposes; should be deleted for security. ## Page 9: PHP Data Objects (PDO) - **Definition**: A lightweight interface providing standardized access to various databases. - **Driver Requirement**: Each database application requires a specific driver (e.g., php_pdo_mysql.dll on Windows). ## Page 10: Benefits of PDO - Standardizes database access using a common API. - Reduces risk of SQL Injection attacks. - Previous libraries (`mysql & mysqli`) had security issues; `mysql` library removed in PHP7. ## Page 11: PDO Connection Example - Sample connection code: ```php $pdo_dsn = 'mysql:host=localhost;dbname=pdo_test;port=3306;charset=utf8mb4'; $pdo_user_name = 'pdo_tester'; $pdo_user_password = 'password'; $db_link = new PDO($pdo_dsn, $pdo_user_name, $pdo_user_password); ``` ## Page 12: Accessing a Database - Sample SQL query: ```sql SELECT username, password FROM users WHERE username = 'freddy'; ``` - Example with PHP variable: ```php $username = 'freddy'; SELECT username, password FROM users WHERE username = $username; ``` ## Page 13: Prepared Statements in PDO - **Stages of Prepared Statements**: 1. **Prepare**: Sends SQL string to the server for compilation. 2. **Bind**: Binds values to placeholders. 3. **Execute**: Completes the query action. ## Page 14: Example of Prepared Statement - Implementation: Using PHP place holder to prevent SQL injection. ```php $username = 'freddy'; $stmt = $db_link->prepare('SELECT username, password FROM users WHERE username = :keyusername'); $stmt->bindParam(':keyusername', $username); $stmt->execute(); ``` ## Page 15: Result Processing - How to retrieve results: ```php $row_count = $stmt->rowCount(); $all_data = $stmt->fetchAll(); $username = $all_data[0][0]; $password = $all_data[0][1]; ``` ## Page 16: Result Displaying - Examples of displaying results: ```php var_dump($pdo_result); var_dump($row_count); var_dump($all_data); var_dump($username); var_dump($password); ``` ## Page 17: Example Application Output - Example of database output with results being displayed. ## Page 18: Example Applications - **pdo-mariadb-test.php**: Example script showing database interactions. ## Page 19: Listing Databases & Tables - **list_dbs.php**: Lists existing databases and tables; securely stores connection info in an external file. ## Page 20: Connection Security in list_dbs.php - Connection details stored securely and accessed correctly: ```php $pdo_dsn = $rdbms . ':host=' . $host . ';dbname=' . $db_name . ';charset=' . $charset; ``` ## Page 21: Connecting Using Try-Catch - Example secure connection method: ```php try { $db_link = new PDO ($host_name, $user_name, $user_password); } catch (PDOException $error) { echo 'Error!: ' . $error->getMessage() . '
'; exit(); } ``` ## Page 22: HTML Output in list_dbs.php - Creates HTML output for the list of databases and tables: ```php $html_output = '

List of Databases & Tables

'; ``` ## Page 23: Executing Database Queries - Example of showing available databases: ```php $query1 = 'SHOW DATABASES;'; ``` ## Page 24: Selecting Databases - Each database is selected to show tables within: ```php while ($db_name = $db_names->fetch(PDO::FETCH_NUM)) { $db_name_to_use = $db_name[0]; } ``` ## Page 25: Querying Tables - Querying for table names within selected databases: ```php $query3 = 'SHOW TABLES;'; ``` ## Page 26: Counting Tables - Using `rowCount()` to track the number of tables within a database: ```php $row_count = $db_table_names->rowCount(); ``` ## Page 27: Looping Through Database Names - Loop constructs to display all databases and their table counts: ## Page 28: Finalizing the Output - Creating a page result object for display and closing the database connection. ## Page 29: Example Output Display - Sample output display of databases and statistics from MariaDB. ## Page 30: Database Wrapper Class - Class provides error handling when interacting with the database and allows storing database connection details. ## Page 31: Class Functions Overview - List of functions that manage database connectivity and queries in the class: - `setConnectionSettings($connection_settings)` - `connectToDatabase()` - `safeQuery($query_string, $query_parameters)` - `countRows()` - More user-defined functions. ## Page 32: Other Database Techniques - Techniques including joins, unions, views, and stored procedures. ## Page 33: Injection Vulnerabilities Overview - Understanding SQL injection and how input can compromise database security. ## Page 34: SQL Injection Attacks - Explanation of how SQL injection can return sensitive information through crafted SQL strings: - Understanding the problem of unsafe SQL queries. ## Page 35: Practical SQL Injection Example - Example demonstrating SQL injection through unsafe query construction. ## Page 36: Comic Reference on SQL Injection - Humorous take on SQL injection awareness. ## Page 37: SQL Injection Example - Another example showcasing the risks associated with SQL injection vulnerabilities. ## Page 38: Unsafe Database Connection Example - Example of poor coding practices leading to vulnerabilities. ## Page 39: Exporting Data from MariaDB - `mysqldump` command for exporting data to an SQL script. ## Page 40: PCI-DSS Overview - Understanding the Payment Card Industry Data Security Standard (PCI-DSS) and its relevance for developers: - Approval for credit card storage, encryption requirements, etc. ## Page 41: Lecture Summary - Key summary of topics discussed: - Overview of MariaDB, PDO usage, handling SQL injections, and PCI standards.