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, yet highly recommended (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 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 query 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.

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.

Always validate and/or sanitize inputs on server side! Use specialized function for SQL ( mysqli_real_escape_string ) or you can utilized a 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);
// ...