Prepared Statements with PostgreSQL in Ruby

In our apps we need to create dynamic queries to our databases to allow our users to interact with the data they see. Whether this is to sign up as a new user, update their existing details or maybe order something new.

As we can’t know ahead of time what the users need to change in the data, we need to allow the execution of SQL with values that the user provides. Of course this can allow for all kinds of naughty things to happen:

Source: xkcd

This is a classic! We need to guard against users injecting malicious SQL into our queries and messing with our data structures. So we need some way to differentiate between the SQL statement that we execute and the values that a user can give that statement.

Prepared statements allow us to do just that. We can prepare a SQL Statement to be executed against our database and store it like so:

require 'pg'

con = PG.connect({ dbname: 'dnd',
      host: 'localhost' })

sql = 'INSERT INTO characters
       (name, age, class)
       VALUES ($1, $2, $3)'

con.prepare('save_character', sql)

Under the hood this is creating a SQL query that is stored in our database (as ‘save_character’ here) like a stored procedure, however it will only last until we end our connection to the database.

The ‘$#’ symbols demonstrate that we can give these SQL statements parameters at run time (the inputs from our users). However these are now completely isolated from the SQL statement that will run them. So if our inputs for this query are as follows:

values = ['Gandalf', 345,
          '1); DELETE FROM characters;--']

con.exec_prepared('save_character',
                  values)

Our query will handle that nasty last input as a harmless string and just store it like so:

Our table remains completely intact.