Web Applications

Code

$connection = mysqli_connect($host, $user, $password, $database);
if (!$connection) { /* handle error */ }
// ... work with connection
mysqli_close($connection); // optional; only after you are done with database access
      

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

$dsn = "mysql:host=$host;dbname=$database";
try {
  $pdo = new PDO($dsn, $username, $password);
} catch (PDOException $e) {
  /* handle error */
});
	  

Documentation ...

• Typically a single connection per user request
  (i.e. one PHP script invocation)
  do not create a new connection for every SQL query
• Do not use the old mysql_* functions
• Documentation: mysqli procedural, mysqli object oriented, pdo
• If a query fails, error message can be obtained using mysqli_error()

Code

$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"], ...
  }
}
	  

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

Documentation ...

• Documentation: mysqli, pdo

What ?

• Technique abusing SQL statements constructed from user inputs without proper sanitization.
• Can be leveraged to extract database data, tamper with existing data, spoof identity, escalate privileges, …
• Misconception: Hackers are not interested in my little website

Wrong :

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

Preventing SQL Injection

• Always validate and/or sanitize inputs on server side
• SQL Injection in PHP manual.
• Use prepared statements with bound variables.
• or … quote each non numeric user supplied value … with the database-specific string escape function
• Check if the given input has the expected data type.
  PHP has a wide range of input validating functions, …(e.g. is_numeric(), intval(), ctype_digit(), …).

Preventing SQL Injection with escaping

$searchStr = $_GET['search'];
// Notice quotes around title
$query = 'SELECT * FROM products WHERE title = "'
  . mysqli_real_escape_string($conn, $searchStr)
  . '"';
$query_result = mysqli_query($conn, $query);
	  

Preventing SQL Injection with binding parameters

// ! Notice no quotes around ?
$stmt = $mysqli->prepare("INSERT INTO myTable VALUES (?, ?)");
// ! do not use literals, use variables for $aStringVar, $aDoubleVar
$stmt->bind_param('sd', $aStringVar, $aDoubleVar);
$aStringVar = '...'
$aDoubleVar = 0;
$stmt->execute();
$query_result = $stmt->get_result();
	  

Exercise 1

List items in the database

• Create PHP file on webik
• Connect to the database and list stored items in given table
• Database
  • server: localhost
  • login: public_readonly
  • password:
  • database: skoda_public
• Table: articles
  • name
  • preview
  • published

Exercise 2

Add filters

• Add form for filtering
• Filters:
  • published is not null
  • preview like %..%

Exercise 3 - bonus

ADD-A-NAME BOARD