O'Reilly logo

The Database Hacker's Handbook: Defending Database Servers by Bill Grindlay, John Heasman, Chris Anley, David Litchfield

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required