NSWI142: Web Applications

MySQL on Webik

There is a MySQL server running on Webik, but it is accessible only from Webik itself (due to firewall rules).

• Host: localhost
• Username: your username
• Password: your original password sent to you at the beginning of the course
• Your database: stud_<username>

You can try logging in via Adminer on webik.ms.mff.cuni.cz/adminer.

Feel free to use your own SQL database for development, but you will be required to use Webik's MySQL database for your semestral project.

You can also use:

• More complex phpMyAdmin.
• Command line tool mysql -p from Webik server.

Accessing Webik MySQL from your own machine

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

Then, you can use 127.0.0.1 as host for MySQL connection on your PC.

Note: According to the documentation, we cannot use localhost as it is bound to Unix sockets, which we do not have. Therefore, you need to use 127.0.0.1 to explicitly indicate that we want a TCP/IP connection.

Do not forget to have mysql php extension installed. (On Linux, install php-mysql.)

Connecting to the Database

Using the object-oriented API:

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

// work with the connection for the whole duration of the script ...

$mysqli->close();
    

Connection is closed automatically after the script execution. As a result, closing the connection is in general optional. For you, it is mandatory for NSWI142.

Do NOT use the old mysql_* functions!

Utilize a single connection per user request (i.e., one PHP script invocation). Do NOT create a new connection for every SQL query.

If a query fails, an error message can be obtained using $mysqli->error. See the documentation for the object-oriented and procedural versions.

Fetching results from the database

Using the object-oriented API:

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

Use only for static queries with NO user input.

See mysqli_result class documentation for more details.

SQL Injection

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.

It 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($query);
      

Preventing SQL Injection

There is a section about SQL Injection in PHP manual.

There are functions that can escape strings similar to htmlspecialchars for HTML. Using them is not recommended.

Use 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);

$stmt->execute();

$query_result = $stmt->get_result();