class: center, middle # MySQL & PHP SDU, Internet Technology, 2019-06-21 *Jack Henschel* --- ## Databases / SQL * Database is an organized collection of data * Database Management System (DBMS) is the software that interacts with the database, applications and end users * DBMS manages the data and the organization of the data in Schemas * Sorted by indexes * Tables are ordered by column names and row ids * Each Schema consists of one or multiple tables * Structured Query Language (SQL) is the language used to communicate with a database * Most RDBMS use SQL, but the syntax varies Examples: ```mysql > SHOW DATABASES; information_schema mysql app > USE DATABASE app; > SHOW TABLES; users images > SELECT name, birthdate FROM users WHERE id = 2; Anton, 1999-09-09 ``` --- ### Data Types MySQL / MariaDB support different datatypes (cell types) for storing different kinds of information: * Boolean: `TinyInt`, `Boolean` * Integer: `SmallInt`, `MediumInt`, `Int` (`Integer`), `BigInt` * Decimal: `Dec`, `Float`, `Double` * Characters: `Char`, `VarChar`, ... * Binary Data: `TinyBlob`, `MediumBlob`, `Blob`, `LongBlob` * Text: `Text`, `LongText`, ... * Time and Date: `TimeStamp`, `Date`, `DateTime`, `Time`, `Year` Choose the appropriate data type for the use-case! Also supports math operators and comparators to work with the above datatypes: `+, -, *, /, <, >, >=, <=, =, !=, ABS(), CEIL(), FLOOR(), EXP(), LIKE, BETWEEN, STRCMP(a,b)...` ??? e.g. instead of using a string to store a date, choose timestamp --- ### SQL Commands * `CREATE`: creates a new database, table, view, ... * `DROP`: deletes a database, table, view, ... * `ALTER`: used to modify the structure of an existing table (`ADD`, `RENAME`, `DROP` ...) * `INSERT`: add a new row to a table * `UPDATE`: modify an existing entry (row) in the table * `SELECT`: query information from a table * `JOIN`: query information present in multiple tables .center[] ??? SQL is a very powerful language Queries can become very long and convoluted **JOIN types** **(INNER) JOIN**: Returns records that have matching values in both tables **LEFT (OUTER) JOIN**: Returns all records from the left table, and the matched records from the right table **RIGHT (OUTER) JOIN**: Returns all records from the right table, and the matched records from the left table **FULL (OUTER) JOIN**: Return all records when there is a match in either left or right table --- ### Migrations Set of files that contain the SQL that makes up the database Ordered (e.g. version or date) and have to be applied in this order Initialize or upgrade the database to a new version Database has a migrations table that remembers which migrations were already applied Don't contain data, just structure ??? Migrations can contain minimal data to get the application up and running --- ### SQL Injections User Input is dangerous! When concatenating SQL statements and user-input together, the user can break out of the SQL clause! ```php $sql = "SELECT * FROM users WHERE username = '" . $_POST["username"] "' "; $stmt = $conn->prepare($sql); $stmt->execute(); ``` Malicious username: `abc'; DROP TABLE users;--` Database input needs to be sanitized or use of "Prepared Statements" (separates SQL statements from data): ```php $sql = "SELECT * FROM users WHERE username = ':username'"; $stmt = $conn->prepare($sql); $stmt->bindParam(':username', $_POST["username"]); $stmt->execute(); ``` ??? Attacker can end the current string with a string delimiter, end the current SQL clause with a semicolon and runs his own query -> delete or extract data! comment at the end of the statement to make sure the entire SQL statement is valid --- ### Sources * Lectures Slides "Internet Technology", Summer 2019, Henrik Lange, SDU * W3Schools SQL & PHP References, https://www.w3schools.com/ * PHP Manual, The PHP Group, https://www.php.net/manual/en/