Web Applications

Connecting to the Database

Using procedural API (documentation of mysqli procedural):

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

Using object oriented API (documentation of mysqli object oriented):

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

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

Do NOT use the old mysql_* functions!

Connecting to the Database

Typically 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, error message can be obtained using mysqli_error. See documentation for object oriented and procedural version.

Fetching results from the database

Using procedural API:

$query = "SELECT * FROM MyTable";
if ($result = mysqli_query($connection, $query)) {
  // $result contains mysqli_result object representing the result set or FALSE
  // fetch associative array
  while ($row = mysqli_fetch_assoc($result)) {
    // access $row["Column1"], $row["Column2"], ...
  }
}
    

Using object oriented API

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

Preventing SQL Injection

There is a section about SQL Injection in PHP manual.

Always validate and/or sanitize inputs on server side! Use specialized function for SQL (mysqli_real_escape_string) or you can utilize the preferred solution bellow.

Preferred solution: 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);

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

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

Preventing SQL Injection with
escaping

Functional, yet not recommended. It does rely on programmer discipline and professionalism, think of your team members.

// Loading user input.
$searchStr = $_GET['search'];

// Notice quotes around title.
$query = 'SELECT * FROM products WHERE title = "'
  . mysqli_real_escape_string($connection, $searchStr)
  . '"';

$query_result = mysqli_query($conn, $query);
// ...