\chapter{MySQL5}
According to \url{http://www.mysql.com/products}, MySQL is the world's most popular open source database. We will use it to store our web app's data.

If we add a database to the picture we discussed in section \ref{Apache, PHP and MySQL Installation and configuration}, we get the following: \\

\noindent\includegraphics[width=\linewidth]{internetDB}

MySQL is a program that runs on the server and specializes in storing, retrieving and manipulating large amounts of data in a structured and highly efficient way.

SQL stands for Structured Query Language. It is the standard language that has been used for decades to manage databases.

A good online learning platform for SQL can be found at \url{http://sqlzoo.net}.

\begin{mdframed}[linecolor=red,linewidth=3pt, leftmargin=1cm,rightmargin=1cm]
\begin{itemize}
\item Remember to use some of the excellent cheat sheets on the web, for instance \url{http://www.cheatography.com/guslong/cheat-sheets/essential-mysql} and \url{http://www.cheatography.com/davechild/cheat-sheets/mysql}.
\item You can play around with MySQL at \url{http://www.tutorialspoint.com/mysql_terminal_online.php}.
\end{itemize}
\end{mdframed}

Data is organized in databases. Each database consists of one or more tables. A table contains data for a specific purpose, for instance addresses. A table is organized in rows and columns. Each column represents a specific data field, e.g. last name. Rows contain data. If we have 10 addresses stored in our address table, there'll be 10 rows, each row containing the address of one person. For each row we can access a specific field, e.g. street.

Here is a sample table for the top 10 clubs in the German Bundesliga (not up to date!):

\begin{tabu}{|X|X|X|}
\hline
\everyrow{\tabucline[0.5pt]{-}}
\textbf{Rank} & \textbf{Club} & \textbf{Points} \\
1 & Bayern München & 23 \\
2 & Dortmund & 22 \\
3 & Leverkusen & 22 \\
4 & Berlin & 15 \\
5 & Schalke 04 & 14 \\
6 & M'Gladbach & 13 \\
7 & Hannover 96 & 13 \\
8 & Bremen & 12 \\
9 & Stuttgart & 11 \\
10 & Hoffenheim & 10 \\
\end{tabu}

\section{Data types}
For each column, we need to specify the type of data we want to store. For instance, in the sample table above, the first and third columns need to be numbers, whereas the second is used for text. A detailed overview of the data types available in MySQL can be found at  \url{http://dev.mysql.com/doc/refman/5.7/en/data-types.html}.

The main ones that we'll use are listed in the following table:
\begin{tabu}{|l|X|X|}
\hline
\everyrow{\tabucline[0.5pt]{-}}
\textbf{Data type} & \textbf{Meaning} & \textbf{Example} \\
\href{http://dev.mysql.com/doc/refman/5.7/en/char.html}{\texttt{VARCHAR}} & string up to specified length (max. 65535) & \texttt{VARCHAR(50)}\\
\href{http://dev.mysql.com/doc/refman/5.7/en/integer-types.html}{\texttt{INT}} & integer & \texttt{INT}\\
\href{http://dev.mysql.com/doc/refman/5.7/en/fixed-point-types.html}{\texttt{DECIMAL}} & decimal with given precision & \texttt{DECIMAL(5, 2)}\\
\href{http://dev.mysql.com/doc/refman/5.7/en/datetime.html}{\texttt{TIMESTAMP}} & date and time & \texttt{TIMESTAMP}\\
\href{http://dev.mysql.com/doc/refman/5.7/en/datetime.html}{\texttt{DATETIME}} & date and time & \texttt{DATETIME}\\
\href{http://dev.mysql.com/doc/refman/5.7/en/datetime.html}{\texttt{DATE}} & date & \texttt{DATE}\\
\href{http://dev.mysql.com/doc/refman/5.7/en/blob.html}{\texttt{TEXT}} & string up to different sizes & \texttt{TEXT}\\
\href{http://dev.mysql.com/doc/refman/5.7/en/blob.html}{\texttt{BLOB}} & binary large object & \texttt{BLOB} \\
\href{http://dev.mysql.com/doc/refman/5.7/en/enum.html}{\texttt{ENUM}} & string from a given list & \texttt{ENUM('Donald Duck', 'Asterix', 'Mickey Mouse')}\\
\href{http://dev.mysql.com/doc/refman/5.7/en/set.html}{\texttt{SET}} & string object with zero or more values from a list of permitted values & \texttt{SET('BMW', 'Nissan')}\\
\href{http://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html}{\texttt{BOOL}} & boolean & \texttt{BOOL}\\
\end{tabu}
\vspace*{\baselineskip}

From \url{http://dev.mysql.com/doc/refman/5.7/en/string-literals.html}:
\begin{quote}
A string is a sequence of bytes or characters, enclosed within either single quote (\verb|'|) or double quote (\verb|"|) characters. Examples:

\verb|'a string'|

\verb|"another string"|

Quoted strings placed next to each other are concatenated to a single string. The following lines are equivalent:

\verb|'a string'|

\verb|'a' ' ' 'string'|
\end{quote}

\verb|TIMESTAMP| and \verb|DATETIME| values are in the format \verb|'YYYY-MM-DD HH:MM:SS'|, for instance \verb|'2014-08-11 15:04:00'|. The supported range is '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999' for \verb|TIMESTAMP| and '1000-01-01 00:00:00' to '9999-12-31 23:59:59' for \verb|DATETIME|.
For a \verb|TIMESTAMP| and (starting from MySQL 5.6.5) \verb|DATETIME| column we can specify \verb|DEFAULT CURRENT_TIMESTAMP|, in which case the current timestamp is automatically set for inserted rows that specify no value for the column. We can also set \verb|ON UPDATE CURRENT_TIMESTAMP| to have the date and time automatically updated when the value of any other column in the row is changed (cf. \url{http://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html}).

When inserting \verb|SET| values into a table, there should be no spaces between values, i.e. use \verb|'val1,val2,val3'| instead of \verb|'val1, val2, val3'|.

\section{Case sensitivity}
SQL keywords, function names, column and index names are not case sensitive. Database, table and view names however are represented using directories and files in the underlying filesystem. Whether they are case sensitive or not depends therefore on the operating system.

\section{Comments}
MySQL supports three comment styles which are very well illustrated at \url{http://dev.mysql.com/doc/refman/5.7/en/comments.html}.

\noindent\url{\ftsql comments1.sql}
{\scriptsize\inputminted[tabsize=2, linenos=true]{mysql}{MySQL5/comments1.sql}}

\section{Naming conventions}
\begin{longtabu}{|l|X|}
\hline
\textbf{Item} & \textbf{Example(s)} \\
\hline
Database & \texttt{dbShop} \\
\hline
Table & \texttt{tblUser}, \texttt{tblLoggedInUser} \\
\hline
Primary key & \texttt{idUser} \\
\hline
Data field & \texttt{dtName} \\
\hline
Foreign key & \texttt{fiUser} referencing \texttt{tblUser.idUser} \\
\hline
\end{longtabu}
\vspace*{\baselineskip}

Table names start with the prefix \verb|tbl| and are always singular, i.e. \verb|tblUsers| should be avoided.

\section{Creating and dropping databases and tables}
The detailed MySQL syntax to create and drop a database and a table can be found here:
\begin{itemize}
\item\url{http://dev.mysql.com/doc/refman/5.7/en/create-database.html} \item\url{http://dev.mysql.com/doc/refman/5.7/en/drop-database.html} \item\url{http://dev.mysql.com/doc/refman/5.7/en/create-table.html} \item\url{http://dev.mysql.com/doc/refman/5.7/en/drop-table.html}
\end{itemize}
Here is a simple application example:

\noindent\url{\ftsql createDB1.sql}
{\scriptsize\inputminted[tabsize=2, linenos=true]{mysql}{MySQL5/createDB1.sql}}

We use the INNODB storage engine. From \url{http://dev.mysql.com/doc/refman/5.7/en/storage-engines.html}:
\begin{quotation}
Storage engines are MySQL components that handle the SQL operations for different table types. InnoDB is the most general-purpose storage engine, and Oracle recommends using it for tables except for specialized use cases.
\end{quotation}
We set the default character set to be UTF8 in order to avoid any problems with storing \texttt{é}, \texttt{ö} etc. We also set collation to \verb|utf8_bin| in order to have case sensitivity. If we skip this, the DB will treat the strings \verb|home|, \verb|Home| and \verb|HOME| as identical.

\begin{mdframed}[linecolor=red,linewidth=3pt, leftmargin=1cm,rightmargin=1cm]
In a production environment, you should create one or several specific DB users for your app that have only the minimum privileges required. Given that you cannot do this on Foxi, we won't go into detail here, but take a look at \url{http://dev.mysql.com/doc/refman/5.7/en/create-user.html} and \url{http://dev.mysql.com/doc/refman/5.7/en/grant.html}.
\end{mdframed}

There are several ways to create and work with a database.

\subsection{\label{PHPSTORMDBScript}Via PhpStorm}
The recommended approach is to write a SQL file that creates the DB using SQL statements. This allows us to easily review the database structure that we have created, make modifications and recreate the database.

If we use PhpStorm, we can configure a data source when we create a new file with extension \texttt{.sql}: \\

\noindent\includegraphics[width=\linewidth]{createDB1}
\vspace*{\baselineskip}

To configure the PhpStorm DB connection, see \ref{PhpStorm Db Setup}.

If you cannot see the database window, you can open it like this: \\

\noindent\includegraphics[width=\linewidth]{createDB5}
\vspace*{\baselineskip}

We can run our SQL script to create the DB and tables: \\

\noindent\includegraphics[width=\linewidth]{createDB6}
\vspace*{\baselineskip}

Don't forget to click \includegraphics{DBSynchronize} to update the DB display.

We can also add a table using the PhpStorm wizard: \\
\noindent\includegraphics{createDBTable1} \\
\noindent\includegraphics[width=\linewidth]{createDBTable2}

\subsection{Via the MySQL command line}
If you have the option to log into your web server (as you do with Foxi) you can create and manage your database via the MySQL command line.
Before you do so, it is important to set the system variables with respect to the character set and collation correctly, otherwise you are asking for trouble.
Here's why: \\

\noindent\includegraphics[width=\linewidth]{createDB7a}
\vspace*{\baselineskip}

As system administrator you could set these settings in the MySQL configuration file, but often you won't have admin rights on the host machine, so you'll need to run the following script after logging in to MySQL and before creating or manipulating any structures or data (cf. \url{https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html} and \url{https://dev.mysql.com/doc/refman/5.7/en/using-system-variables.html}): \\

\noindent\url{\ftsql mysql_conf.sql}
{\scriptsize\inputminted[tabsize=2, linenos=true]{mysql}{MySQL5/mysql_conf.sql}}

\noindent\includegraphics[width=\linewidth]{createDB7b}
\vspace*{\baselineskip}

Now you can work on your structures and data. Here is an example: \\

\noindent\includegraphics[width=\linewidth]{createDB7}
\vspace*{\baselineskip}

If you have created a SQL file you can then simply execute it: \\

\noindent\includegraphics[width=\linewidth]{mysql1}

Note that before you can work with a database, you need to select it as the active database using the \texttt{use} command.

\subsection{Via phpMyAdmin}
\noindent\includegraphics[width=\linewidth]{createDB8}
\noindent\includegraphics[width=\linewidth]{mysql2}
\noindent\includegraphics[width=\linewidth]{mysql3}

\section{Work flow in PHP5}
\noindent\includegraphics[scale=0.7]{DBflow}

\section{Connecting to and disconnecting from a database via PHP}
In chapter \ref{Apache, PHP and MySQL Installation and configuration} we saw how to install and configure MySQL. Now we'll see how we can connect to an existing database (DB) via PHP (cf. \url{http://php.net/manual/en/mysqli.construct.php}). We use the MySQL Improved Extension (mysqli) to access a MySQL DB. A great summary of the MySQLi extension can be found at \url{http://php.net/manual/en/mysqli.summary.php}.

First we define the constants that we need to connect to the database and put them into a file that we can include:

\noindent\url{\ftsql db_credentials_dummy.php}

{\scriptsize\inputminted[tabsize=2, linenos=true]{php}{MySQL5/db_credentials_dummy.php}}

Note that this is safe, given that PHP code gets executed on the server and in this case only an empty document is sent to the browser as you can easily verify by clicking the link.

Now we will connect to our DB using PHP.

\subsection{Procedural approach}
\noindent\url{\ftsql connect_procedural.php}
{\scriptsize\inputminted[tabsize=2, linenos=true]{php}{MySQL5/connect_procedural.php}}

\subsection{Object-oriented approach}
\noindent\url{\ftsql connect_oo.php}
{\scriptsize\inputminted[tabsize=2, linenos=true]{php}{MySQL5/connect_oo.php}}

\verb|$dbc| is a mysqli object, containing all the data fields and methods we need to work with our DB (cf. \url{http://www.php.net/manual/en/class.mysqli.php}). It is essential to become familiar with a number of these fields and methods, which we'll use in many of our DB operations. In particular:
\begin{itemize}
\item \verb|connect_errno| (cf. \url{http://www.php.net/manual/en/mysqli.connect-errno.php}) contains the error code for the last connect call.
\item \verb|connect_error| (cf. \url{http://www.php.net/manual/en/mysqli.connect-error.php}) contains a string description of the last connect error.
\item \verb|errno| (cf. \url{http://www.php.net/manual/en/mysqli.errno.php}) contains the error code for the last function call.
\item \verb|error| (cf. \url{http://www.php.net/manual/en/mysqli.error.php}) contains a string description of the last error.
\item \verb|close| (cf. \url{http://www.php.net/manual/en/mysqli.close.php}) closes a previously opened DB connection.
\item \verb|prepare| (cf. \url{http://www.php.net/manual/en/mysqli.prepare.php}) prepares an SQL statement for execution. The parameter markers must be bound to application variables using the \verb|bind_param| (cf. \url{http://php.net/manual/en/mysqli-stmt.bind-param.php}) method of the \verb|stmt| class before executing the statement.

There are four possible data types:

\begin{tabu}{|l|X|}
\hline
\everyrow{\tabucline[0.5pt]{-}}
\textbf{Character} & \textbf{Data type}\\
i & integer\\
d & double\\
s & string\\
b & blob (cf. \url{http://php.net/manual/en/mysqli-stmt.send-long-data.php}) \\
\end{tabu}
\vspace*{\baselineskip}

The resulting data can be bound to specific variables and then retrieved using  \verb|bind_result| (cf. \url{http://php.net/manual/en/mysqli-stmt.bind-result.php}) and  \verb|fetch| (cf. \url{http://php.net/manual/en/mysqli-stmt.fetch.php}) or retrieved using the result object with \verb|get_result| (\url {http://php.net/manual/en/mysqli-stmt.get-result.php}), which, unfortunately, is not available on Foxi and then using one of the fetch functions described below.

We'll look at practical examples in the following sections.
\item \verb|query| (cf. \url{http://www.php.net/manual/en/mysqli.query.php}) performs a query on the DB. Returns FALSE on failure. For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries it returns a \verb|mysqli_result| object (\url{http://www.php.net/manual/en/class.mysqli-result.php}), for other successful queries TRUE. 
\item \verb|real_escape_string| (cf. \url{http://www.php.net/manual/en/mysqli.real-escape-string.php}) escapes special characters in a string for use in an SQL statement, taking into account the current charset of the connection. However, \verb|_| and \verb|%| are not escaped, although they have special meaning in LIKE clauses. It is therefore safer to use prepared statements, which will be our preferred approach in the following sections.
\item \verb|set_charset| (cf. \url{http://www.php.net/manual/en/mysqli.set-charset.php}) sets the default client character set. We use \verb|utf8|.
\end{itemize}

\section{Retrieving data}
\subsection{MySQL5}
We retrieve data from our DB using the \texttt{SELECT} statement. A straightforward explanation can be found at \url{http://www.w3schools.com/sql/sql_select.asp}. The gory details are at \url{http://dev.mysql.com/doc/refman/5.7/en/select.html}.
We can specify columns for which we want to retrieve data or use \texttt{*} to indicate that we want data for all columns.

We can retrieve data for all rows or only for those rows that fulfill conditions. The conditions are specified using the \texttt{WHERE} clause. We can even use regular expressions, which are explained in detail at \url{http://dev.mysql.com/doc/refman/5.7/en/regexp.html}.

Let's look at a few SQL query examples:

\noindent\url{\ftsql select1.sql}
{\scriptsize\inputminted[tabsize=2, linenos=true]{mysql}{MySQL5/select1.sql}}

\subsection{PHP5}
How do we execute SQL queries in PHP? We can use the object-oriented or the procedural approach. In the former we work with objects, such as a DB connection or result set and use a property or call a method (function) of this object using \verb|->|, for instance \verb|$result->num_rows| or \verb|$dbc->query($query)|. The procedural approach requires that we call the corresponding mysqli function and provide the variable to be used. Thus we need to write \verb|mysqli_num_rows($result)| or \verb|mysqli_query($dbc, $query)|.

\subsubsection{SQL injection}
We need to distinguish between queries that use data provided by the user and those that use internal data. In the first case we need to sanitize user input or use prepared statements to avoid hijacking of our database, which is a major threat to our web app (cf. \url{https://en.wikipedia.org/wiki/SQL_injection}).

Why? Let's look at an example provided by Robin Nixon in his book "PHP, MySQL, JavaScript, \& CSS" on page 249:
\begin{quotation}
Suppose you have a simple piece of code to verify a user, and it looks like this:

\begin{scriptsize}
\begin{verbatim}
$user  = $_POST['user'];
$password  = $_POST['password'];
$query = "SELECT * FROM tblUser WHERE dtUser='$user' AND dtPassword='$password'";
\end{verbatim}
\end{scriptsize}
At first glance, you might think this code is perfectly fine. But what if someone enters the following for \verb|$user| (and doesn't enter anything for \verb|$password|)?

\begin{scriptsize}
\begin{verbatim}
admin' #
\end{verbatim}
\end{scriptsize}

Let's look at the string that would be sent to MySQL:

\begin{scriptsize}
\begin{alltt}
SELECT * FROM tblUser WHERE dtUser='admin' {\bf #' AND dtPassword=''}
\end{alltt}
\end{scriptsize}

Do you see the problem there (highlighted in bold)? In MySQL, the \# symbol represents the start of a comment. Therefore, the user will be logged in as \emph{admin} (assuming there is a user \emph{admin}), without having to enter a password.

What about the case in which your application code removes a user from the database? The code might look something like this:

\begin{scriptsize}
\begin{verbatim}
$user  = $_POST['user'];
$password  = $_POST['password'];
$query = "DELETE FROM tblUser WHERE dtUser='$user' AND dtPassword='$password'";
\end{verbatim}
\end{scriptsize}
Again, this looks quite normal at first glance, but what if someone entered the following for \verb|$user|?

\begin{scriptsize}
\begin{verbatim}
anything' OR 1=1 #
\end{verbatim}
\end{scriptsize}

MySQL would interpret this as the following (again highlighted in bold):
\begin{scriptsize}
\begin{alltt}
{\bf DELETE FROM tblUser WHERE dtUser='anything' OR 1=1} #' AND dtPassword=''
\end{alltt}
\end{scriptsize}
Ouch - that SQL query will always be true, and therefore you've lost all your users table data!
\end{quotation}

\begin{mdframed}[linecolor=red,linewidth=3pt, leftmargin=1cm,rightmargin=1cm]
To sanitize user input, we can call the \verb|real_escape_string| function mentioned above, like this:

\begin{scriptsize}
\begin{verbatim}
$user  = mysqli_real_escape_string($dbc, $_POST['user']); // Procedural approach.
\end{verbatim}
\end{scriptsize}

or

\begin{scriptsize}
\begin{verbatim}
$user  = $dbc->real_escape_string($_POST['user']); // Object-oriented approach.
\end{verbatim}
\end{scriptsize}
\end{mdframed}

This approach suffers from several major security problems:
\begin{enumerate}
\item This does not escape \verb|_| and \verb|%|.
\item Numeric user provided data is not enclosed in quotation marks, in which case escaping won't prevent SQL injection.
\item Storing escaped data in the DB, reading it back and then storing it again will remove the escaping
\end{enumerate}

If we also want to remove HTML and PHP tags and slashes, we can write a function that we call for every input:

\noindent\url{\ftsql sanitize_string.php}
{\scriptsize\inputminted[tabsize=2, linenos=true]{php}{MySQL5/sanitize_string.php}}

Another way is to use prepared statements (cf. \url{https://en.wikipedia.org/wiki/Prepared_statement}): \begin{quote}
Prepared statements are resilient against SQL injection, because parameter values, which are transmitted later using a different protocol, need not be correctly escaped.
\end{quote}

Prepared statements separate query structure from data. Given that user data has no influence on query structure, this is the safest approach.

\subsubsection{With internal data}
With internal data we do not need to take special precautions to avoid DB hijacking although the safest approach would be to always use prepared statements, given that it is not guaranteed that the data was processed in some unexpected way.

\paragraph{Object-oriented approach} ~\\
\noindent\url{\ftsql select1_oo.php}
{\scriptsize\inputminted[tabsize=2, linenos=true]{php}{MySQL5/select1_oo.php}}

First we establish a DB connection and set the character set. Then we define our query and execute it using the \texttt{query} function (cf. \url{http://www.php.net/manual/en/mysqli.query.php}). This function returns \verb|FALSE| if something went wrong, otherwise \verb|TRUE| or the result object of the query, depending on the type of query. The result object has several interesting properties and functions, please study \url{http://www.php.net/manual/en/class.mysqli-result.php}.

For our purposes, the following functions are particularly useful:
\begin{tabu}{|l|X|}
\hline
\textbf{Function} & \textbf{Description} \\
\hline
\texttt{num\_rows} & Number of rows returned by query. \\
\hline
\texttt{fetch\_row} & Fetch current result row as a numbered array. \\
\hline
\texttt{fetch\_array} & Fetch current result row as both a numbered and an associative array. \\
\hline
\texttt{fetch\_assoc} & Fetch current result row as an associative array. \\
\hline
\texttt{fetch\_object} & Fetch current result row as a PHP object. \\
\hline
\end{tabu}
\vspace*{\baselineskip}
It is important to understand, that the result set has an internal pointer representing the current row. Every call to a fetch function increases this internal pointer. Thus, every fetch will return the next row until the end of the result set has been reached. If we want to retrieve a different row, we can use the \verb|data_seek| function to move the pointer. To finish the job we free the memory of the result object using its \verb|free| method and we close the DB connection.

\paragraph{Procedural approach} ~\\
\noindent\url{\ftsql select1_procedural.php}
{\scriptsize\inputminted[tabsize=2, linenos=true]{php}{MySQL5/select1_procedural.php}}

\subsubsection{With user provided data}
If some of the data used to query the DB is user provided, we either need to sanitize it ourselves or use prepared statements. In the former case, all we have to do is run the user provided inputs through \verb|real_escape_string|, like so:

\noindent\url{\ftsql select2_escape_oo.php}
{\scriptsize\inputminted[tabsize=2, linenos=true]{php}{MySQL5/select2_escape_oo.php}}

\noindent\url{\ftsql select2_escape_procedural.php}
{\scriptsize\inputminted[tabsize=2, linenos=true]{php}{MySQL5/select2_escape_procedural.php}}

Keep in mind that escaping is not fully secure.

\paragraph{Using prepared statements}
\subparagraph{Object-oriented approach} ~\\

\noindent\url{\ftsql select2_oo.php}
{\scriptsize\inputminted[tabsize=2, linenos=true]{php}{MySQL5/select2_oo.php}}

\verb|prepare| returns a prepared statement object or \verb|FALSE| if something went wrong. See \url{http://www.php.net/manual/en/class.mysqli-stmt.php}.

\texttt{store\_result} returns a buffered result object or FALSE if something went wrong. To see what this object looks like, go to \url{http://php.net/manual/en/class.mysqli-result.php}.
Once we have a result object, we can for instance figure out how many rows have been retrieved.

With \texttt{bind\_result} we can bind columns in the result set to variables. See \url{http://php.net/manual/en/mysqli-stmt.bind-result.php}.
To free the resources used for our query, we close the prepared statement and the DB connection. \\

\texttt{get\_result} returns a result set from a prepared statement. See \url{http://php.net/manual/en/mysqli-stmt.get-result.php}.

Unfortunately, the mysqlnd driver can currently not be installed on Foxi, therefore this method is not available and the following example fails:

\noindent\url{\ftsql select3_oo.php}
{\scriptsize\inputminted[tabsize=2, linenos=true]{php}{MySQL5/select3_oo.php}}

\texttt{fetch\_array} returns an array that corresponds to the fetched row or NULL if there are no more rows for the result set. See \url{http://php.net/manual/en/mysqli-result.fetch-array.php}.

Example:

\noindent\url{\ftsql select4_oo.php}
{\scriptsize\inputminted[tabsize=2, linenos=true]{php}{MySQL5/select4_oo.php}}

\texttt{fetch\_all} returns an array of associative or numeric arrays holding result rows. See \url{http://php.net/manual/en/mysqli-result.fetch-all.php}.

Unfortunately, the mysqlnd driver can currently not be installed on Foxi, therefore this method is not available. ~\\

\noindent\url{\ftsql select5_oo.php}
{\scriptsize\inputminted[tabsize=2, linenos=true]{php}{MySQL5/select5_oo.php}}

\subparagraph{Procedural approach} ~\\
\noindent\url{\ftsql select2_procedural.php}
{\scriptsize\inputminted[tabsize=2, linenos=true]{php}{MySQL5/select2_procedural.php}}

\begin{Exercise}[difficulty=1, name={MySQL5 Exercise}, counter=excmysql]
Add a login functionality to your signup form. If the user credentials exist in the database, a confirmation message is displayed.
\end{Exercise}

\section{Inserting data}
\subsection{MySQL5}
We insert data into our DB using the \texttt{INSERT} statement. A straightforward explanation can be found at \url{http://www.w3schools.com/sql/sql_insert.asp}. The gory details are at \url{http://dev.mysql.com/doc/refman/5.7/en/insert.html}. \\

\subsection{PHP5}
\subsubsection{Preparing strings for database insertion}
First we should in most cases remove any whitespace from the beginning and end of any input provided by the user using the \href{http://php.net/manual/en/function.trim.php}{\texttt{trim}} function. The following characters can cause problems when inserting data into a database, as they may be interpreted as control characters by the database: \texttt{""}, \texttt{''}, \texttt{$\backslash$}, \texttt{NULL}. We need to tell MySQL that we mean these characters literally and not as control characters. To do this, we add a backslash in front of them.

Once our insertion statement has been executed, we can easily get the id of the inserted record (cf. \url{https://php.net/manual/en/mysqli.insert-id.php}):
\begin{quotation}
The \texttt{mysqli\_insert\_id} function returns the ID generated by a query on a table with a column having the AUTO\_INCREMENT attribute. If the last query wasn't an INSERT or UPDATE statement or if the modified table does not have a column with the AUTO\_INCREMENT attribute, this function will return zero. 
\end{quotation}

In the case of prepared statements, we use the \verb|insert_id| field of the statement (cf. examples below).

\subsubsection{With internal data}
\paragraph{Object-oriented approach} ~\\
\noindent\url{\ftsql insert1_oo.php}
{\scriptsize\inputminted[tabsize=2, linenos=true]{php}{MySQL5/insert1_oo.php}}

\paragraph{Procedural approach} ~\\
\noindent\url{\ftsql insert1_procedural.php}
{\scriptsize\inputminted[tabsize=2, linenos=true]{php}{MySQL5/insert1_procedural.php}}

\subsubsection{With user provided data}
The simplest approach is to use \verb|real_escape_string| on all user provided data, as illustrated above. Alternatively, we can use prepared statements.

\paragraph{Object-oriented approach} ~\\
\noindent\url{\ftsql insert2_oo.php}
{\scriptsize\inputminted[tabsize=2, linenos=true]{php}{MySQL5/insert2_oo.php}}

\paragraph{Procedural approach} ~\\
\noindent\url{\ftsql insert2_procedural.php}
{\scriptsize\inputminted[tabsize=2, linenos=true]{php}{MySQL5/insert2_procedural.php}}

\subsubsection{Storing objects, arrays and other complex data types}
If we want to store more complex PHP data types in a MySQL table, we can use the \verb|serialize| (\url{http://php.net/manual/en/function.serialize.php}) and \verb|unserialize| (\url{http://php.net/manual/en/function.unserialize.php}) functions. Serialized data should be stored in a MySQL \verb|BLOB|.

\begin{Exercise}[difficulty=1, name={MySQL5 Exercise}, counter=excmysql]
Create a PHP script that displays a signup form. The user can enter a user name and password, both of which get stored in the login table created in the previous exercise. 
\end{Exercise}

\begin{Exercise}[difficulty=1, name={MySQL5 Exercise}, counter=excmysql]
How secure is your solution to the previous exercise? Implement a better solution so that, even if your DB gets hijacked, nobody can see the passwords of your users. You might want to take a look at \url{http://dev.mysql.com/doc/refman/5.7/en/encryption-functions.html}.
\end{Exercise}

\begin{Exercise}[difficulty=1, name={MySQL5 Exercise}, counter=excmysql]
Improve your solution to the previous exercise by using a prepared statement.
\end{Exercise}

\section{Deleting data}
\subsection{MySQL5}
We delete data from our DB using the \texttt{DELETE} statement. A straightforward explanation can be found at \url{http://www.w3schools.com/sql/sql_delete.asp}. The gory details are at \url{http://dev.mysql.com/doc/refman/5.7/en/delete.html}.

Deleting all the data in a table is very straightforward in MySQL:

\noindent\url{\ftsql delete1.sql}
{\scriptsize\inputminted[tabsize=2, linenos=true]{mysql}{MySQL5/delete1.sql}}

We can filter which data gets deleted using the \texttt{WHERE} clause, just as for \texttt{SELECT} queries:

\noindent\url{\ftsql delete2.sql}
{\scriptsize\inputminted[tabsize=2, linenos=true]{mysql}{MySQL5/delete2.sql}}

\subsection{PHP5}
\subsubsection{With internal data}
\paragraph{Object-oriented approach} ~\\
\noindent\url{\ftsql delete1_oo.php}
{\scriptsize\inputminted[tabsize=2, linenos=true]{php}{MySQL5/delete1_oo.php}}

\paragraph{Procedural approach} ~\\
\noindent\url{\ftsql delete1_procedural.php}
{\scriptsize\inputminted[tabsize=2, linenos=true]{php}{MySQL5/delete1_procedural.php}}

\subsubsection{With user provided data}
The simplest approach is to use \verb|real_escape_string| on all user provided data, as illustrated above. Alternatively, we can use prepared statements.

\paragraph{Object-oriented approach} ~\\
\noindent\url{\ftsql delete2_oo.php}
{\scriptsize\inputminted[tabsize=2, linenos=true]{php}{MySQL5/delete2_oo.php}}

\paragraph{Procedural approach} ~\\
\noindent\url{\ftsql delete2_procedural.php}
{\scriptsize\inputminted[tabsize=2, linenos=true]{php}{MySQL5/delete2_procedural.php}}

\section{Updating data}
\subsection{MySQL5}
We update data in our DB using the \texttt{UPDATE} statement. A straightforward explanation can be found at \url{http://www.w3schools.com/sql/sql_update.asp}. The gory details are at \url{http://dev.mysql.com/doc/refman/5.7/en/update.html}.

A simple example:

\noindent\url{\ftsql update1.sql}
{\scriptsize\inputminted[tabsize=2, linenos=true]{mysql}{MySQL5/update1.sql}}

\subsection{PHP5}
\subsubsection{With internal data}
\paragraph{Object-oriented approach} ~\\
\noindent\url{\ftsql update1_oo.php}
{\scriptsize\inputminted[tabsize=2, linenos=true]{php}{MySQL5/update1_oo.php}}

\paragraph{Procedural approach} ~\\
\noindent\url{\ftsql update1_procedural.php}
{\scriptsize\inputminted[tabsize=2, linenos=true]{php}{MySQL5/update1_procedural.php}}

\subsubsection{With user provided data}
The simplest approach is to use \verb|real_escape_string| on all user provided data, as illustrated above. Alternatively, we can use prepared statements.

\paragraph{Object-oriented approach} ~\\
\noindent\url{\ftsql update2_oo.php}
{\scriptsize\inputminted[tabsize=2, linenos=true]{php}{MySQL5/update2_oo.php}}

\paragraph{Procedural approach} ~\\
\noindent\url{\ftsql update2_procedural.php}
{\scriptsize\inputminted[tabsize=2, linenos=true]{php}{MySQL5/update2_procedural.php}}

\section{Altering tables}
\subsection{MySQL5}
We can change the structure of a table using the \texttt{ALTER TABLE} statement. A straightforward explanation can be found at \url{http://www.w3schools.com/sql/sql_alter.asp}. The gory details are at \url{http://dev.mysql.com/doc/refman/5.7/en/alter-table.html}.

A simple example:

\noindent\url{\ftsql alter_table1.sql}
{\scriptsize\inputminted[tabsize=2, linenos=true]{mysql}{MySQL5/alter_table1.sql}}

\section{DB normalization}
Normalization is the process of organizing our DB efficiently in order to avoid anomalies that will prevent our application from working correctly (cf. \url{http://en.wikipedia.org/wiki/Database_normalization}).

\subsection{First normal form}
From \url{http://en.wikipedia.org/wiki/First_normal_form}:
\begin{quote}First normal form (1NF) is a property of a relation in a relational database. A relation is in first normal form if the domain of each attribute contains only atomic values, and the value of each attribute contains only a single value from that domain.\end{quote}

Let's say we want to develop an online shop. On first thought we need a table that contains all orders of our customers: \\

\begin{tabu}{|l|l|l|l|l|}
\tabucline[0.5pt black]{-}
\everyrow{\tabucline[0.5pt black]{-}}
\textbf{customer\_id} & \textbf{first\_name} & \textbf{last\_name} & \textbf{articles} & \textbf{quantities} \\
1 & Dummy & Dum & TV Car Apple & 1 1 5 \\
2 & Donald & Duck & Safe House & 1 1 \\
\end{tabu}
\vspace*{\baselineskip}

Here the \texttt{articles} and \texttt{quantities} fields contain multiple values, which violates the first normal form.

A better DB design might look like this: \\

\begin{tabu}{|l|l|l|}
\tabucline[0.5pt black]{-}
\everyrow{\tabucline[0.5pt black]{-}}
\textbf{customer\_id} & \textbf{first\_name} & \textbf{last\_name} \\
1 & Dummy & Dum \\
2 & Donald & Duck \\
\end{tabu}
\vspace*{\baselineskip}

\begin{tabu}{|l|l|l|}
\tabucline[0.5pt black]{-}
\everyrow{\tabucline[0.5pt black]{-}}
\textbf{customer\_id} & \textbf{article} & \textbf{quantity} \\ 
1 & TV & 1 \\
1 & Car & 1 \\
1 & Apple & 5 \\
2 & Safe & 1 \\
2 & House & 1 \\
\end{tabu}
\vspace*{\baselineskip}

Repeating groups of telephone numbers do not occur in this design. Instead, each Customer-to-Telephone Number link appears on its own record. With Customer ID as key, a one-to-many relationship exists between the two tables. A record in the "parent" table, Customer Name, can have many telephone number records in the "child" table, Customer Telephone Number, but each telephone number belongs to one, and only one customer. It is worth noting that this design meets the additional requirements for second and third normal form.

\section{Creating a safe registration and login}
\subsection{Rudimentary version}
We need the following:
\begin{enumerate}
\item A DB table to store our users.
\item A PHP class that provides login and registration functionality for our user table.
\item A login and registration script.
\end{enumerate}

\subsection{User table}
As recommended at \url{http://php.net/manual/en/password.constants.php}, we use a \verb|VARCHAR(255)| to store the password hash.

\noindent\url{\ftsql loginDB1.sql}
{\scriptsize\inputminted[tabsize=2, linenos=true]{mysql}{MySQL5/loginDB1.sql}}

\subsection{DB class}
\noindent\url{\ftsql loginDBl.php}
{\scriptsize\inputminted[tabsize=2, linenos=true]{html+php}{MySQL5/loginDB1.php}}

\subsection{Login and registration script}
\noindent\url{\ftsql login1.php}
{\scriptsize\inputminted[tabsize=2, linenos=true]{html+php}{MySQL5/login1.php}}

\noindent\url{\ftsql logged_in1.php}
{\scriptsize\inputminted[tabsize=2, linenos=true]{html+php}{MySQL5/logged_in1.php}}

\subsection{A more sophisticated approach}
\url{https://stackoverflow.com/questions/244882/what-is-the-best-way-to-implement-remember-me-for-a-website}
\url{http://jaspan.com/improved_persistent_login_cookie_best_practice}
\url{https://paragonie.com/blog/2015/04/secure-authentication-php-with-long-term-persistence#title.2}

\section{Tests}
\subsection{WMOTU Shop DB}
\noindent\includegraphics{WMOTUShopDB1}

WMOTU Shop DB provides the following functionality (cf. \url{http://youtu.be/fpzwKXdBnpc}):
\begin{enumerate}
\item The user sees all the articles in the database. For each article, the name, description and price are displayed.
\item The user can add a new and delete or edit an existing article.
\item The database is created using a MySQL script, which is located in a protected place, so that the user cannot access it.
\item The user cannot hijack our DB with malignant input.
\item Special characters such as \texttt{'} and \texttt{"} are handled correctly.
\end{enumerate}

\subsubsection{Solution}
The solution can be found at \url{\ftsql Tests/WMOTUShopDB}.
To protect the \verb|protected| directory, we proceed as described in \ref{Security}.

\paragraph{createDB.sql}
{\scriptsize \inputminted[tabsize=2, linenos=true]{mysql}{MySQL5/Tests/WMOTUShopDB/protected/createDB.sql}}

\paragraph{database.php}
{\scriptsize \inputminted[tabsize=2, linenos=true]{php}{MySQL5/Tests/WMOTUShopDB/protected/database.php}}

\paragraph{index.php}
{\scriptsize \inputminted[tabsize=2, linenos=true]{html+php}{MySQL5/Tests/WMOTUShopDB/index.php}}

\subsection{Perfume Shop}
\noindent\includegraphics{PerfumeShop}

Develop a perfume shop with the following features:
\begin{itemize}
\item A user DB table to store the user name and encrypted password. There are 2 users (dummy1, dummy1) and (dummy2, dummy2).
\item A perfume DB table to store the perfumes available for sale with the following names and prices (€):
\begin{itemize}
\item Eau de SYSEX 99.99
\item Eau de HTSTA 129.99
\item Eau de CLISS 179.99
\item Eau de WSERS 299.99
\item Eau de WEBAP 499.99
\end{itemize}
\item A purchase DB table, storing for each purchase a link to the user, a link to the perfume and the quantity.
\item A login (no sign up).
\item Every user has his own shopping cart, which displays the articles bought so far and gives the total price. For each article there may not be more than one entry in the shopping cart, e.g. if the user buys 1 Eau de WSERS and later on buys another 3, then the shopping cart will show 4 Eau de WSERS at €299.99 each for a total of €1199.96.
\item The user can add and remove individual perfumes from his shopping cart. For instance, using the previous example, he can remove 2 Eau de WSERS, which leaves 2 for a total of €599.98.
\item Each user has a logout button.
\item Obviously, if the user logs out and then logs in again, the shopping cart still shows all purchases made.
\end{itemize}

\subsubsection{Solution}
The solution can be found at \url{\ftsql Tests/PerfumeShop}.

\paragraph{createDB.sql}
{\scriptsize \inputminted[tabsize=2, linenos=true]{mysql}{MySQL5/Tests/PerfumeShop/createDB.sql}}

\paragraph{database.php}
{\scriptsize \inputminted[tabsize=2, linenos=true]{php}{MySQL5/Tests/PerfumeShop/database.php}}

\paragraph{index.php}
{\scriptsize \inputminted[tabsize=2, linenos=true]{html+php}{MySQL5/Tests/PerfumeShop/index.php}}

\paragraph{logout.php}
{\scriptsize \inputminted[tabsize=2, linenos=true]{html+php}{MySQL5/Tests/PerfumeShop/logout.php}}

\paragraph{main.php}
{\scriptsize \inputminted[tabsize=2, linenos=true]{html+php}{MySQL5/Tests/PerfumeShop/main.php}}

\subsection{MicroQuack}
Create a simple Internet message board with the following features:
\begin{itemize}
\item A user needs to register first with a user name and password. Users are stored in a user table, which you create via a SQL script. Your app needs to be protected against SQL injection attempts. You may not store plaintext passwords in your DB.
\item A logged-in user sees a list of all messages together with their timestamp and author. Messages are stored in a message table, which you create via a SQL script.
\item A logged-in user can write a new message or edit or delete one of his previous messages.
\end{itemize}

\subsubsection{Solution}
The solution can be found at \url{\ftsql Tests/MicroQuack}.

\paragraph{createDB.sql}
{\scriptsize \inputminted[tabsize=2, linenos=true]{mysql}{MySQL5/Tests/MicroQuack/createDB.sql}}

\paragraph{main.php}
{\scriptsize \inputminted[tabsize=2, linenos=true]{html+php}{MySQL5/Tests/MicroQuack/index.php}}