NSWI142: Web Applications

Demonstration: Semestral project

A draft of semestral project assignment is out. You have a week to provide feedback. At the ent of the next week, we are going to publish the final version.

Relational database

MySQL on webik

There is a MySQL server running on webik server. The database is accessible only from webik itself, i.e. localhost. You can interact with the database using various tools:

• Command linemysql tool. To connect to database you can use mysql -p, exit by using exit command.
• phpMyAdmin at webik.ms.mff.cuni.cz/phpmyadmin
• Adminer at webik.ms.mff.cuni.cz/adminer.

How to connect?

• Host: localhost
• Port: 3306
• Username: {username}
• Password: your original password sent to you at the beginning of the course
• Database: stud_{username}

Accessing webik MySQL remotely

Use SSH tunneling to forward the MySQL port from Webik to your local machine.

      ssh -L 3306:localhost:3306 webik
    

This expose the database port 3306 on your local machine at 3306.

MySQL and PHP

Do NOT use the old mysql_* functions!

Connecting to the Database

Using procedural API:

$connection = mysqli_connect($host, $user, $password, $database);
if (!$connection) { /* handle error */ }

// ... work with connection

mysqli_close($connection);
    

Using object oriented API, this is the preferred way:

$mysqli = new mysqli($host, $user, $password, $database);
if ($mysqli->connect_error) { /* handle error */ }

// ... work with connection

$mysqli->close();
    

If a query fails, error message can be obtained using mysqli_error. See documentation for object oriented and procedural version.

Working with database connection

It is common to utilize a single connection per user request, i.e. per PHP script invocation. Do NOT create a new connection for every SQL query.

SQL connection is closed automatically after script execution. Yet, it is highly recommended, mandatory for NSWI142, to close is once you do not need it.

Executing static SQL

      $query = "SELECT * FROM MyTable";
      if ($result = $mysqli->query($query)) {
        /* fetch associative array */
        while ($row = $result->fetch_assoc()) {
          // access $row["Column1"], $row["Column2"], ...
        }
      }
    

Use only for static query with NO user input.

See mysqli_result class documentation for more details.

Executing (dynamic) SQL

Preferred solution for query execution is to employ prepared statements with bound variables.

      // Notice no quotes around '?'.
      $stmt = $mysqli->prepare("INSERT INTO myTable VALUES (?, ?)");

      // Bind variables to '?' with hinting the type (string, double).
      // Function takes references, thus you must use variables.
      $stmt->bind_param('sd', $aStringVar, $aDoubleVar);

      // We can change it later.
      $aStringVar = '...';
      $aDoubleVar = 0;

      $stmt->execute();
      $query_result = $stmt->get_result();
    

SQL injection

What is SQL injection?

SQL Injection is technique abusing SQL statements constructed from user inputs without proper sanitization. Similar technique can be employed in other scenarios as well.

Is can be leveraged to extract database data, tamper with existing data, spoof identity, escalate privileges, …

Hackers ARE interested in my little website.

$query = 'SELECT * FROM products WHERE title = "'. $_GET['search'] . '"';
$query_result = mysqli_query($conn, $query);
      

Preventing SQL Injection

There is section about SQL Injection in PHP manual.

You can try and use sanitization functions. For example, mysqli_real_escape_string performs sanitization for SQL. It works like htmlspecialchars, just for SQL instead of HTML.

It is best to not rely on them and instead use solution like templates or prepared statements. Use of prepared statements instead of mysqli_real_escape_string is mandatory for NSWI142.