The Database Hacker's Handbook: Defending Database Servers

SQL Injection with PostgreSQL

SQL injection vulnerabilities have plagued poorly written web applications. Applications that dynamically create and execute queries on PostgreSQL are potentially vulnerable unless care is taken to create certain escape characters such as ' and /. The following snippet of PHP demonstrates a typical SQL injection flaw:

<?php
// moviedatabase.php

// Connect to the Database
$conn = pg_connect("host=10.0.0.1 port=5432 dbname=movies user=postgres password=password!!");

// Retrieve title parameter from submitted URL
$title = $_GET[title];

// Build query; note lack of input validation on $title
$query  = "SELECT title, description FROM movietable WHERE title LIKE '%$title%';";

// Execute query and retrieve recordset
$myresult = pg_exec($conn, $query);

// Enumerate rows in recordset
  for ($lt = 0; $lt < pg_numrows($myresult); $lt++)
  {
    $title = pg_result($myresult, $lt, 0);
    $description = pg_result($myresult, $lt, 1);
    $year = pg_result($myresult, $lt, 0);

    // Print results
    print("<br><br>\n");
    print("Title: $title <br/>\n");
    print("Description: $description <br/>\n");
    print("Year: $year <br/>\n");
  }

// If no records were matched, display a message
if (pg_numrows($myresult) == 0) print("Sorry no results found. <br>\n");
?>

In normal operation, this script would be executed by accessing the URL of the form:

http://webserver/moviedatabase.php?title=Hackers

and would return matching movie titles, as follows:

Title: Hackers Description: A movie about breaking ...