Spring 2016, CS-161 Lecture: Programming with the Postgres C Application Programmer Interface (API)

Programming with the Postgres C Application Programmer Interface (API)
CS-161: Software Project
Instructor: Rob Bruce
Spring 2016

SLIDE 1: Connecting to Postgres

Postgres database connection (a database handle):

This is a classic client-server model.
Postgres runs as a daemon listening on TCP port 5432 (default).
The Postgres API basically sends each database query via a TCP socket connection to the server. Queries can be sent in plain text or binary.

SLIDE 2: Postgres C API: Connecting to database

db_connection = PQconnectdb("dbname = 'NAME_OF_DATABASE' user = 'USERNAME' password = 'PASSWORD'");
if (PQstatus(db_connection) != CONNECTION_OK)
{
  printf ("Connection to database failed: %s", PQerrorMessage(db_connection));
  PQfinish (db_connection);
  exit (FAILURE);
}

SLIDE 3: Postgres C API: executing a SELECT query

strncpy (&db_statement[0], "SELECT students.student_id, students.first_name, students.last_name FROM students ORDER BY students.student_id ASC", MAX_DB_STATEMENT_BUFFER_LENGTH);
db_result = PQexec (db_connection, &db_statement[0]);
if (PQresultStatus(db_result) == PGRES_TUPLES_OK)
{
  num_rows = PQntuples(db_result);
  if (num_rows == 0)
  {
    printf ("database is empty");
  }
  else
  {
    for (row = 0; row < num_rows; row++)
    {
      printf ("%s %s %s\n", PQgetvalue (db_result, row, 0), PQgetvalue (db_result, row, 1), PQgetvalue (db_result, row, 2));
    }
  }
  PQclear (db_result);
}

SLIDE 4: Postgres C API: executing a SELECT statement

The PQexec() command executes an SQL statement.

Watch out for SQL-injection attacks!

SLIDE 5: SQL injection attacks

Imagine a query such as this:
snprintf (&db_statement[0], MAX_DB_STATEMENT_SIZE-1, "INSERT INTO students (first_name, last_name) VALUES ('Rob', 'Bruce')");

Now imagine we insert the first_name and last_name as character string arrays dynamically to build our SQL query:
snprintf (&db_statement[0], MAX_DB_STATEMENT_SIZE-1, "INSERT INTO students (first_name, last_name) VALUES ('%s', '%s')", &first_name[0], &last_name[0]);

Let first_name be the following literal string (in blue):
Ha', 'Ha'); DELETE FROM students; INSERT INTO students (first_name, last_name) VALUES ('Your data

Let last_name is the following literal string (in green):
is gone

When these variables are substituted into our snprintf() statement, the resulting SQL query is as follows:
INSERT INTO students (first_name, last_name) VALUES ('Ha', 'Ha'); DELETE FROM students; INSERT INTO students (first_name, last_name) VALUES ('Your data', 'is gone');

SLIDE 6: SQL injection attacks (continued)

From previous slide, the resulting SQL injection query is as follows:
INSERT INTO students (first_name, last_name) VALUES ('Ha', 'Ha'); DELETE FROM students; INSERT INTO students (first_name, last_name) VALUES ('Your data', 'is gone');

When executed by Postgres, the statement above will actually execute three commands:
INSERT INTO students (first_name, last_name) VALUES ('Ha', 'Ha');
DELETE FROM students;
INSERT INTO students (first_name, last_name) VALUES ('Your data', 'is gone');

This is an SQL injection attack with really nasty consequences!

SLIDE 7: Preventing SQL injection attacks

Be cautious when using PQexec() to execute the resultant SQL query!

To avoid SQL injection attacks, use PQexecParams() instead.

SLIDE 8: PQexecParams() INSERT statement

parameter_values[0] = &student_first_name[0]; parameter_values[1] = &student_last_name[0]; strncpy (&db_statement[0], "INSERT INTO students (first_name, last_name) VALUES ($1, $2) RETURNING student_id", MAX_DB_STATEMENT_BUFFER_LENGTH); db_result = PQexecParams (db_connection, db_statement, 2, NULL, parameter_values, NULL, NULL, 0); if (PQresultStatus(db_result) != PGRES_TUPLES_OK) { printf ("Postgres INSERT error: %s\n", PQerrorMessage(db_connection)); } else { if (PQntuples(db_result) == 1) { if (strlen(PQgetvalue(db_result, 0, 0)) < MAX_STUDENT_ID_LENGTH-1) { strncpy (&student_id[0], PQgetvalue(db_result, 0, 0), MAX_STUDENT_ID_LENGTH-1); printf ("Student ID %s: assigned to %s %s\n\n", &student_id[0], &student_first_name[0], &student_last_name[0]); } } PQclear(db_result); }