The pgx Files 07: Smart Querying; SQL Injection Safety

14 Aug 2016

Welcome back to The pgx Files! Today is a two-fer. We will look at reducing the amount of data we transfer over the wire, and we will look at how pgx protects us from SQL injection attacks.

As SQL fans know, it's trivial to only query for the data you need. Our previous episode of The pgx Files showed us fetching an entire user, but today, we will use the power of SQL to give us a boolean answer to a boolean question: Are these login credentials correct?

Let's go into psql and look at our users table as we left it from our previous episode:

# select * from users;
┌──────────────────────────────────────┬──────────┬──────────┬────────────┬───────────┐
│                  id                  │ username │ password │ first_name │ last_name │
├──────────────────────────────────────┼──────────┼──────────┼────────────┼───────────┤
│ 3a6d8008-fec2-412d-acc6-2767a950c5e7 │ manni    │ foo      │ Manni      │ Wood      │
└──────────────────────────────────────┴──────────┴──────────┴────────────┴───────────┘
(1 row)

OK, great. If we wanted to check a user login, we could fetch the whole row and compare the username and password columns of the retrieved row. But we also send over the wire all the other columns that we don't even end up using, and that's wasteful. So, we could of course just select the username and password columns to reduce the amount of data we move over the wire.

But, this being SQL, we can do even better.

# select exists(select count(*)
    from users
   where username = 'mwood' and password = 'foo') as succeeded;
┌───────────┐
│ succeeded │
├───────────┤
│ t         │
└───────────┘
(1 row)

# select exists(select count(*)
    from users
   where username = 'mwood' and password = 'blarg') as succeeded;
┌───────────┐
│ succeeded │
├───────────┤
│ f         │
└───────────┘
(1 row)

There. Boolean answers to a boolean question. Very little data moves over the wire when we call this SQL query from a client.

Here's how we do the same thing using pgx:

package main

import (
	"fmt"
	"log"
	"os"

	"github.com/manniwood/playground/pgxfiles"
)

func main() {
	if len(os.Args) != 3 {
		log.Fatal("Please enter a username and password")
	}
	username := os.Args[1]
	password := os.Args[2]

	conn := util.Connect("user login")
	defer conn.Close()

	var loginOK bool
	err := conn.QueryRow(`
	select exists(
	select count(*)
	  from users
	 where username = $1
	   and password = $2 as succeeded`, username, password).Scan(&loginOK)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Unexpected error trying to login user: %v\n", err)
		os.Exit(1)
	}
	if !loginOK {
		fmt.Fprintf(os.Stderr, "Login of %s failed.\n", username)
		os.Exit(1)
	}

	fmt.Printf("Logged in user %s\n", username)
}

And here's us running it:

$ ./loginuser manni foo
Logged in user manni

$ ./loginuser mwood blarg
Login of mwood failed.

Also, seeing as we are now accepting user input from the command line, this is a good time to test whether or not pgx correctly escapes input to prevent SQL injection attacks:

$ ./loginuser manni "'); drop table users; --"
Login of manni failed.

Nice!

Next on the pgx files: Handling nulls!